Duplicate Records

[JillC]

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

 

[Bernie Deitrick]

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$22=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

 

[JLatham]

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.