Unique combinations of records in a list
 

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

Unique combinations of records in a list
 

In E2:
=if(countif($A$2:A2,A2)1,"X","")
then drag fill down the column.

--
Regards,
Tom Ogilvy



"Leon" wrote:

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

Unique combinations of records in a list
 

Lean,

In row 3 of your helper column, enter the formula

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

and copy down.

HTH,
Bernie
MS Excel MVP


"Leon" wrote in message
...
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

Unique combinations of records in a list
 

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.

--
Regards,
Tom Ogilvy




"Leon" wrote:

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

Unique combinations of records in a list
 

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....
"Tom Ogilvy" wrote:


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.

--
Regards,
Tom Ogilvy




"Leon" wrote:

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

Unique combinations of records in a list
 

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
"Bernie Deitrick" wrote:

Lean,

In row 3 of your helper column, enter the formula

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

and copy down.

HTH,
Bernie
MS Excel MVP


"Leon" wrote in message
...
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

Unique combinations of records in a list
 

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)

--
Regards,
Tom Ogilvy


"Leon" wrote:

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
"Bernie Deitrick" wrote:

Lean,

In row 3 of your helper column, enter the formula

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

and copy down.

HTH,
Bernie
MS Excel MVP


"Leon" wrote in message
...
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

Unique combinations of records in a list
 

tom:

haven't seen posts from you in a while.

good to see you back.
--


Gary


"Tom Ogilvy" wrote in message
...

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.

--
Regards,
Tom Ogilvy




"Leon" wrote:

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