Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #3
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    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. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #5
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    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. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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
  •