match & delete

**kroz** · 02-24-2009, 04:48 AM

Hey guys,

I need to simplify a worksheet. I have over 2000 rows with 20 columns and i need to find and remove identical rows. I can do a match with MATCH() by concatenating the columns and comparing them as a whole but i need to apply this to the whole table and i need it to remove all the duplicate rows it finds.

**Richard Schollar** · 02-24-2009, 05:00 AM

Hi

Take a look at AdvancedFilter as it has an option to Filter Unique Records only - you would need to filter the entire 2000 rows by 20 columns. If you insert a blank sheet and while A1 of the blank sheet is selected go Data>Filter>Advnaced Filter the Advanced FIlter dialog will open. You should specify "Copy to another location" then click on the little box with the red arrow next to "List Range" and choose the other sheet containing your data and select the range (including the headers), then specify A1 on the new sheet for the "Copy to" and then make sure you check "Copy Unique Records only". Then click OK.

That should do it!

Richard

**Steve R** · 02-24-2009, 05:10 AM

See attached workbook.

I created a new unique list.

**kroz** · 02-24-2009, 05:25 AM

The autofilter is great but it has only a small flaw. I have 20 columns and i want to use as criteria only 5-6 of them (i can use up to 17 of them because the data in the other 3 is irrelevant for i don't need to filter my sheet by it). The problem is that i still need the data in the columns that i don't filter by and if i use a filter that data is lost.
Steve: I'll try applying that list on the table and let you know how it works. It would have been easier with a VBA but i don't know how to code it.

**Richard Schollar** · 02-24-2009, 05:36 AM

You can still use Advanced Filter if you use a criteria:

Assume Sheet1 contains your data in A1:Z2000 and the 3 columns that mark a unique record are A,B,C. Then, in AB1 type: Criteria and in AB2 type the following formula:

=SUMPRODUCT(--($A$2:$A2=$A2),--($B$2:$B2=$B2),--($C$2:$C2=$C2))<2

make sure you copy the $ exactly as they appear above.

Then select your data eg A1:Z2000 and go Data>Filter>Advanced Filter. In the Criteria box, select AB1:AB2 (make sure you include the header cell ie the one with 'Criteria'). Select "copy to another location" and select cell AD1. Leave "Unique Records Only" unchecked this time. Then click OK.

Richard