Find duplicate entries in data set

[jpenndcl]

I am trying to sort through a list of data and find duplicates in it.

I want to sort through data in column 1 and see if the contents in A1 is the same as any of the other entries in column A (I want it to repeat through the entire list of data in column A)

If the entry in A1 is the same as one or more of the cells in column A then I want to look at the corresponding values in column B to see if they match as well.

If the corresponding values in column B match, then I would like to copy the rows of matching data into a separate worksheet and add a column that tells me the row numbers where the matching data is located in the original list. I would also like it to skip a row between sets of matching data.


Example

Tab 1

A B

1 p
2 d
3 d
2 p
1 p


Tab 2

A B C

1 p 1
1 p 5

 

[Zack Barresse]

Hi,

Have you tried Advanced Filter? What version are you using? You should be able to make a helper column and filter for unique values, then separate with Text to Columns (keeping the helper column), then add a MATCH formula to find the values in the helper column and return the row number.

Post back if you need help with it.

 

[jpenndcl]

Hi,

Have you tried Advanced Filter? What version are you using? You should be able to make a helper column and filter for unique values, then separate with Text to Columns (keeping the helper column), then add a MATCH formula to find the values in the helper column and return the row number.

Post back if you need help with it.

2003, service pack 2

Ahh, didn't know about that filter....

I think I know how to do the first part ("you should be able to make a helper column and filter for unique values") using the advanced filter (although I have not gotten the right results yet!), but I am not sure what you mean about "separate with Text to Columns (keeping the helper column)" and am not 100% sure how to implement the MATCH formula to work with it.

 

[Zack Barresse]

A helper column would be where you concatenate the two values together. So if your data was in A and B, in C you would use the formula...

=A1&B1

.. and copy it down. To make it unique, add an operator of sorts...

=A1&"~"&B1

.. then copy down. When you do Text to Columns, it is from the Data menu (since you're using 2003, Data tab in 2007). It will take a column of data and separate each cell out into multiple columns depending on either location or character, whichever you specify (fixed width or delimited [by a character]).

Once you have your helper column and advanced filter it, you can take those values and run a MATCH on it, say if your data was filtered (copied to another location via advanced filter) to E1 you would use...

=MATCH(E1,C:C,0)

If matched, the row number is returned, if not a formulatic error.

HTH