compare columns for redundant text entries

lapsang · Apr 11th, 2002, 02:43 PM

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 · Apr 11th, 2002, 02:58 PM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 11th, 2002, 02:43 PM	#1
lapsang New Member Join Date: Apr 2002 Posts: 1	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

Apr 11th, 2002, 02:58 PM	#2
tk19 New Member Join Date: Mar 2002 Posts: 33	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.