Search for duplicates across three worksheets


S

Sarah_Lund

I need help with this one. I have one excel file with three worksheets. I
want to compare the data in column A in each worksheet and if a value exists
in more than one sheet I'd like to highlight the cell in each sheet it
exists. Or I guess another way to put it is I want to higlight all values in
the A columns that are not unique across all three worksheets.

For example:
Sheet1
Column A
apple
orange (would be highlighted)
banana

Sheet2
Column B
orange (would be highlighted)
melon (would be highlighted)

Sheet 2
Column A
peach
melon (would be highlighted)

Thank you!
Sarah
 
Ad

Advertisements

D

Dave Peterson

I would use two extra columns on each worksheet (say columns B and C).

On sheet1, I'd label the columns "On Sheet2" and "On Sheet3".

Then I'd put this formula in B2 (headers in row 1 of all columns).
=isnumber(match(a2,sheet2!a:a,0))

And this in C2:
=isnumber(match(a2,sheet3!a:a,0))

And then drag those formulas as far as I needed.

Then I could filter the data to show just the ones I want.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top