Unique combinations of records in a list

  • Thread starter Thread starter Leon
  • Start date Start date
L

Leon

I have a Excel list made of 6 columns.
Records are added to that list daily.

I want to be able to create a helper column that will show an "X" next to a
row that has 2 records, let's say on column A and C ONLY, identical to those
in previous rows.

Example:
A B C D E
John 06/18/08 $ 590.00 256 A
Jack 06/19/08 $ 700.00 300 C
Mark 06/18/08 $ 590.00 256 A
Miles 06/19/08 $1,1190.00 300 C
Jack 06/18/08 $ 700.00 256 A...............X
(Identical records on Col A AND C than row 2
Lee 06/19/08 $1,1190.00 300 C

Any help or pointer will be gratefully appreciated
 
Lean,

In row 3 of your helper column, enter the formula

=IF(SUMPRODUCT(($A$2:$A2=A3)*($C$2:$C2=C3))>0,IF(A3<>"","X",""),"")

and copy down.

HTH,
Bernie
MS Excel MVP
 
Sorry, didn't read closely enough

in F2
=IF(SUMPRODUCT(--($A$2:A2&$C$2:C2=A2&C2))>1,"X","")

then drag fill down the column.
 
Thanks Tom your second answer seems better than the first one.
I have already come up with the first one and it would give me relative
duplacates by columns and not rows.
I will try your formula and keep you posted.
Thanks a lot anyways....
 
Both of the last 2 answers work perfectly.
How can find in a very long list, the row (previous) that has the identical
records than the one found by the formula
 
Why not sort your data on those two columns.

If you say it has to remain the same make a copy of the sheet, then in an
adjacent column put in

=row() and drag fill down. then select that column and do
Edit=>copy, then Edit=>Paste special and select values

Now sort the data. The duplicate rows will stay in the same relative order
since Excel does a stable sort. Now you will see the row number in the
original for the matching records. (use the same formulas to identify the
duplicates)
 
Back
Top