identify and hilite duplicates

[gpeterman]

I want to write a formula or script that would search the same collumn on all the sheets in a workbook and mark the text items that are repeated. I am willing to attempt it but I don't even have a list of the possible excel commands.
I own excel for Dummies but it about 7000 miles away.

 

[xlhelp]

Hightlight the column in the first worksheet starting with Cell A1 (if that's the first cell). Give it a name "Insert, Name" (-- XL2003 and lower). For example Set1.

With the column still highlighted go to conditional formatting and under "Formula is" type =COUNTIF($A$1:$A$40,A1)>1 if A1 to A40 is your range. Apply a formatting and click OK until all pop-ups disappear.

Go to the next sheet, highlight the column starting with cell A1 (again, if theat's the first cell), go to conditional formatting and for "Formula is" type =VLOOKUP(A1,set1,1,FALSE)=A1. Apply formatting etc.

The result will be duplicates in the second sheet that are also on the first sheet. And you can do this for the rest of the worksheets.

(Please note, evrything here refers to cell A1; you may need to adjust)

If you really want all duplicates for all sheets, then there are scripts out there, you need to search.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[gpeterman]

Thanks - I will give it a shot and see how good I am.

 

[gpeterman]

Thanks xlhelp for the heads up. I found "ASAP utility" on line, it is a free download and it's just the ticket.