Results 1 to 3 of 3

Thread: Compare data and highlight rows

  1. #1

    Compare data and highlight rows

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Everyone

    I have 2 excel spreadsheets containing mail users. The one contains all users and the other only disabled users.

    Colum B contains the usernames in both sheets. But B236 in sheet1 does not contain the same data as B236 in sheet2 since sheet 2 only contains disabled users.

    I would like to compare the users from sheet2 (disabled user) to sheet1 (all user) and highlight the complete row that contains the same name.

    I managed to highlight duplicates with conditional formatting, but not the whole row, also some users are assigned to more than one mailbox. That user is still active but since it duplicates in the same sheet it marks as well.

    Any help would be greatly appreciated.


  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Nanaimo, BC, Canada
    Blog Entries
    Excel Version
    Excel Office 365 Insider
    Hi JP, and welcome to the forum,

    It's hard without seeing your data, but an easy way to get to the end results might be to do the following:
    • Copy the names of the Disabled Users to a new worksheet
    • Add the value "Disabled" to the next blank column
    • Copy the names of the Enabled Users and append it at the bottom of the new table
    • Add the value "Enabled" for those users
    • Sort the data by username
    • Add a conditional format to highlight the row if the username is the same as the cell above
    It may not be the easiest to maintain, but it would be quick and easy to get you to your goal I think.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: -||- Blog: -||- Forums:
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Excel Version
    To CF a whole row, you just select the whole row before applying the formula, and use an absolute column reference.

    The second part must be an error in your CF if you highlight same sheet duplicates.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts