Help with identifying duplicates in two worksheets

[kristell]

I need to set up a load of users on a new system. I have one workbook with one sheet called All users and another called Active users.

In All users sheet, Column D (rows 2:3474) - has names of everyone ever registered on the system since the year dot!

In Active users sheet, Column G (rows 2:311) shows those who have been active in the last couple of years.

I want to highlight on All Users sheet those who appear on the Active users sheet.

I have tried various ways around this but all seem to come up with an error so would be grateful for any help (or sample spreadsheets) that I could use to solve this issue. Thanking you in advance.

 

[alansidman]

You can do this by joining the two sheets in Power Query with a left outer join. If you would like to see a demonstration, then provide sample data from both sheets (anonomize the data) and use XL2BB to load the data so we don't have to recreate it.

 

[kristell]

Oh - I thought it would be a simple formula. It is sensitive information so I can't share it obviously but will see what I can do to recreate it. This is a work system and not sure if we have any extra functionality like Power Query unless it is a standard Excel feature.

 

[Fluff]

You can use conditional formatting.
Select D2:D3474 & in conditional formatting use this formula

=MATCH(D2,'Active Users'!G2:G311,0)

 

[Joe4]

Take a look at the solution I proposed here: Automatic Cell Entry from One Column to Another, which checks to see if a record appears in a list (can be on a different page).

Automatic Cell Entry from One Column to Another

Hello! I have a list of 41 different document numbers that get entered into a larger set. I'm trying to see how I can create a formula that will automatically enter an "x" into a specific column if any of the 41 are triggered in a separate column. Any suggestions? For example: I have a list of...

www.mrexcel.com

You should be able to use this method to do what you want, as it is essentially the same thing.

 

[kristell]

Thanks all - I went with this: =COUNTIF('Active users'!$G$2:$G$311,I3417)>0

and it worked brilliantly.

 

[Joe4]

Glad you were able to implement that solution successfully!