Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate Records
I have an Excel spreadsheet that lists duplicate records and I need to
compare one duplicate column to another column that may have duplicates. Here is an example of my spreadsheet... Date Log ID Log # Technician's Last Name 1/20/2010 324048 20100115@107 SMITH 1/20/2010 324048 20100115@107 SMITH 1/20/2010 324048 20100115@107 LEWIS 1/20/2010 324048 20100115@107 ORANGE 1/20/2010 324048 20100115@107 BANANA So, first you will notice that the Log ID is duplicated, then you see the log # duplicated and then you have 4 different technician's working on that log. So, the only records that I'd want after I do my formula or whatever I have to do would be 1 record from smith, 1 from lewis, 1 from orange and 1 from banana. Basically, even though Smith is listed twice, I'd only like to count it once. Can anyone help?? If you'll notice, the log id is the same for both technician's, but the technician's name |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate Records
Jill,
It is unclear what you want to do, but start by entering this formula into cell E2 =IF(SUMPRODUCT(($A$2:A2=A2)*($B$2:B2=B2)*($C$2:C2= C2)*($D$2:D2=D2))1, "Duplicate","") and then copy it down to match your list. Then copy column E and paste special values - you can then sort or filter and remove the lines where "Duplicate" appears, or exclude them from your calculations. HTH, Bernie MS Excel MVP "JillC" wrote in message ... I have an Excel spreadsheet that lists duplicate records and I need to compare one duplicate column to another column that may have duplicates. Here is an example of my spreadsheet... Date Log ID Log # Technician's Last Name 1/20/2010 324048 20100115@107 SMITH 1/20/2010 324048 20100115@107 SMITH 1/20/2010 324048 20100115@107 LEWIS 1/20/2010 324048 20100115@107 ORANGE 1/20/2010 324048 20100115@107 BANANA So, first you will notice that the Log ID is duplicated, then you see the log # duplicated and then you have 4 different technician's working on that log. So, the only records that I'd want after I do my formula or whatever I have to do would be 1 record from smith, 1 from lewis, 1 from orange and 1 from banana. Basically, even though Smith is listed twice, I'd only like to count it once. Can anyone help?? If you'll notice, the log id is the same for both technician's, but the technician's name |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Duplicate Records
Bernie kind of beat me to it. I think it's going to be determined quite a
bit by exactly what you are trying to do at any given point, but I suspect that most of the solutions for specific requests are going to involve the SUMPRODUCT() function (pre-Excel 2007) or the SUMIFS() function if you happen to be working exclusively with Excel 2007 or 2010. "JillC" wrote: I have an Excel spreadsheet that lists duplicate records and I need to compare one duplicate column to another column that may have duplicates. Here is an example of my spreadsheet... Date Log ID Log # Technician's Last Name 1/20/2010 324048 20100115@107 SMITH 1/20/2010 324048 20100115@107 SMITH 1/20/2010 324048 20100115@107 LEWIS 1/20/2010 324048 20100115@107 ORANGE 1/20/2010 324048 20100115@107 BANANA So, first you will notice that the Log ID is duplicated, then you see the log # duplicated and then you have 4 different technician's working on that log. So, the only records that I'd want after I do my formula or whatever I have to do would be 1 record from smith, 1 from lewis, 1 from orange and 1 from banana. Basically, even though Smith is listed twice, I'd only like to count it once. Can anyone help?? If you'll notice, the log id is the same for both technician's, but the technician's name |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
KEEP duplicate records | Excel Discussion (Misc queries) | |||
HELP duplicate records | Excel Discussion (Misc queries) | |||
Delete records when certain records have duplicate column data | New Users to Excel | |||
duplicate records | Excel Discussion (Misc queries) | |||
duplicate records | Excel Discussion (Misc queries) |