Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create a separate list for unique records | Excel Discussion (Misc queries) | |||
How to create a macro in excel so that it can generate a list ofunique records using all permutations and combinations of the data in eachrow ad column | Excel Discussion (Misc queries) | |||
List & count unique records | Excel Programming | |||
how to get the number of unique records in a list | Excel Worksheet Functions | |||
Finding unique records from a list. | Excel Discussion (Misc queries) |