One for all you Excel gurus: I have to dedupe an Excel 97 worksheet, based on the contents of a single column. I have about 10,000 rows of data, of which about 1,000 have two or three duplicate entries in the column. I'm going blind trying to find all the dupes.

Do any of you know a way to hide the single entry data, so I can focus on fixing the dupes? This would make my life much easier...

thanks,

-jl

If you only want to dedupe by one column and not by entire row, select the column which has the duplicate entries, goto Data > Filter > Advanced Filter, check "Unique records only." and hit OK. It will filter in place, then you can copy and paste everything in to a new sheet and you'll have only unique records by the column selected. If you want unique data by the entire row, just select all of the colums in the first step.

The opposite of what you asked for, really, but you should be able to still get the same results with a little work.

I hope that makes sense, I haven't finished my coffee yet.

Thanks for the idea. I tried it, and the filter showed only the unique rows. But when I cut and pasted my "unique" rows to get them out of the way, it took all the rows, unique as well as dupes.

Am I doing something wrong? Or am I back to slogging...

-jk

Odd. I just tried it a few times and I only get the filtered results when I copy/paste. Which version of Excel are you using?

Excel 97

Hrm, well that might be the problem. I'm using 2002 (XP) SP2.

Maybe you can copy/paste using "Paste Special" somehow. <shrug>

I don't have 97 to test it, any one else have any input?

Excel 97 does even weirder things for me. I don't get the duplicated rows, but a copy-n-paste sometimes doesn't copy all the shown rows. I'm guessing there be bugs here.

I'm guessing there be bugs here.

Most likely, and they'll never get fixed since it is a discontinued product.

I'd say his best bet is to get Office XP, or give the file to someone who does and ask them to do him a favor.

I don't know...

Thanks (he says slogging on...)

-jk

If the order is important, Insert a column in front of column A and create a numbered list (1,2, 3,4, 5, etc down the column)
Sort the spreadsheet on the column that has the duplicate
Then insert another column before column a
enter this formula in a2 (if c is the dup column otherwise use the correct column letter), =c2=c1
copy the formula down in column A
select column A and copy
Paste Special Values only back into column a
Sort on column A
Delete all the rows that says true (they should be grouped together after sorting)
Resort on the numbered column or column B
Delete the numbered column

Questions?