Compare data and highlight rows

[kobusstrydom]

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.

JP

 

[Ken Puls]

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.

 

[Bob Phillips]

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.