compare columns for redundant text entries

[lapsang]

Hi,
I just found your website this morning and love it! I am an low-intermediate user and have the following question. I've looked in your tip archive and found some solutions that come close and maybe you've already answered this, if so forgive me. I have 3 large equipment lists (text) with hundreds of pieces of equipment listed in single columns in different worksheets of a single workbook. Somebody else came up with these lists and I suspect that there are duplicate entries between all of them, and each list should be filled with unique entries( for example one column should only be fruits and another should only list vegetables but I'm sure there is a lettuce or two in the fruit list). I tried your data validation but didn't have much luck applying it to already existing lists. Can you help me?

Thanks
Dirk

 

[tk19]

I'm not sure exactly what you're asking, but maybe you could put everything in one long list, sort it, and type this formula in the next column: "=if(a2=a1,1,0)" in cell b2, and fill down. Copy this column and paste over itself as values. Then sort by this column. All the "ones" are repeats.