Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5
Default Search for duplicates across three worksheets

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default Search for duplicates across three worksheets

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.


Sarah_Lund wrote:

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


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Disallow duplicates in different worksheets ClaireView Excel Discussion (Misc queries) 5 February 4th 08 09:05 PM
create a macro to search an excel spread for duplicates jamalin Excel Worksheet Functions 4 February 16th 07 11:58 AM
Finding duplicates in 2 worksheets darkwood Excel Worksheet Functions 2 May 12th 06 08:06 PM
how do I search a colum for duplicates automatically Andrew Excel Discussion (Misc queries) 2 February 8th 06 03:45 PM
excel duplicates on 2 worksheets Luke Rogers Excel Discussion (Misc queries) 3 February 6th 06 05:31 PM


All times are GMT +1. The time now is 12:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"