# Thread: Finding unique records (xp - 2003)

1. ## Finding unique records (xp - 2003)

I am trying to separate those records that appear more that once. same id # & same date . Is this doable with advance filter or does it require programming?
I am posting a sample of the database

Thanks

2. ## Re: Finding unique records (xp - 2003)

What if they have different dates? Does the seq# go with each separate one as well? If so how do you decide which one gets which seq# and which date?

Steve

3. ## Re: Finding unique records (xp - 2003)

Thank you for the response.

The definition of a duplicate, triplicate etc. record is if it is the same id# and date. Seq # is only a record # not a field which is data relevant.

so if on 4 different dates the id#123 shows up twice and a on a 5th date id#123 shows up four times, i want to record that record 123 was duplicated 4 times on dates 1, 2,3 and 4 and on date 5 id#123 was recorded 4 times.

4. ## Re: Finding unique records (xp - 2003)

This seems a different question than the original one. The equation in E2
=COUNTIF(\$B\$2:\$B\$187,B2)
and copied down the column should give you the total number of occurences regardless of date. If you want to get a list of dates, yuou can use the VLIndex functions from <post:=395,235>post 395,235</post:>

The formula in F2:
=SUMPRODUCT((\$B\$1:\$B\$187=B2)*(\$D\$1:\$D\$187=D2))

and copied down the column will give how many times that particular combination of ID and date appears

The formula in G2:
=SUMPRODUCT((\$B\$1:B1=B2)*(\$D\$11=D2))

and copied down will indicate the "duplicate number". 0 means it is the original, 1 is the first duplicate, 2 the 2nd duplicate, etc

Advanced filter will extract unique combinations of ID and date if desired

A pivot table will also extract the unique combinations and count them as well.

Steve

5. ## Re: Finding unique records (xp - 2003)

Could you give me assistance with the structure of the Pivot table that you mentioned. I could not cull out the unique combinations and the count.

Thank you very much

6. ## Re: Finding unique records (xp - 2003)

select a cell in the range A1187
Data - pivot table and pivot chart report ...
<next><next>
<layout>
Drag <ee #> to ROW
Drag <DATE> to ROW
Drag <ee #> to DATA
<OK>

In the pivot table created you can right click one of the total rows and choose "Hide" to not display them

Now you have a list of the ee# and number for each date

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•