Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding Duplicates in Data (2003)

    Hello,

    I have a spreadsheet of 15000 rows of claim data. The claims are based on whether they are denied and sometimes a provider sends a claim in again under a different claim number and we deny it again. I would like to have a macro or something else search the data and locate the claims that have the same patient control number,line number,detail service date and proc code and label that line as a duplicate denial. Can this be done?

    Thanks ,
    Ben

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding Duplicates in Data (2003)

    Could you provide a small sample workbook with some dummy data to give us an idea of the structure of the worksheet?

  3. #3
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Duplicates in Data (2003)

    Here is a small sample. When a patient goes for a visit he is given a unique patient control number and later the hospital submits a claim. If a claim is denied, they sometimes resubmit essentially the same claim but give it a different claim number. The key is to not count these twice because I am summing the allowed amt up and I would overstate the amount if I don't emininate the duplicates.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding Duplicates in Data (2003)

    In the attached version, I have added two columns on the right. Column N contains a formula that calculates a code that can be used to compare records. Column O uses COUNTIF to determine if the row has a duplicate.
    I used conditional formatting to highlight the duplicate rows.
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding Duplicates in Data (2003)

    Another easy way of doing this would be to create an Access database and to create a link to the Excel table in the database.
    You can then use the Find Duplicates Query Wizard to create a query that returns all duplicate records (rows).
    Since the Access table is linked, it will be updated automatically if the data in Excel change. The query will always return up-to-date results.

  6. #6
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Duplicates in Data (2003)

    Looks good but I was wanting to add another column that would be to the right of these and it would say duplicate denial. On your example it shows the duplicates and are highlighted but I only want one to be highlighted and out beside it to say duplicate denial. Is there a way to do this?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding Duplicates in Data (2003)

    But how do you determine which one of the records should be denied?

  8. #8
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Duplicates in Data (2003)

    It doesn't which duplicate we take out because I am summing the allowed amt field and the amt field will be the same since it is something that our pricer dictates by the contract. I don't want to overstate the amt.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding Duplicates in Data (2003)

    Try the simple modification in the attached workbook.
    Attached Files Attached Files

  10. #10
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding Duplicates in Data (2003)

    Thanks Hans . On the money as usual.

  11. #11
    Star Lounger
    Join Date
    Jul 2007
    Location
    North Carolina, USA
    Posts
    71
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Finding Duplicates in Data (2003)

    Hans,

    I was able to use this in a recent spreadsheet and appreciate the help. When I used the first formula you listed =COUNTIF(N:N,N2)>1 I thought I had done something wrong. All the answers showed up as False even though there were duplicates. Then I looked at another string and when I looked back at my spreadsheet it had updated with the correct data. Is there a reason that when I first copied the formula to the cells it did not display the correct answers? Thanks again for the formula, huge time saver for me.

  12. #12
    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 Duplicates in Data (2003)

    Do have have calculation as manual? Perhaps it updated after recalculating with a save...

    Steve

  13. #13
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Finding Duplicates in Data (2003)

    Hans,
    In searching for an alternative method of finding duplicates, I was reading your post where you used the formula ="C"&A4&C4&E4&I4 in column N.

    Is this another form of a Concatenate formula? Hope this isn't classified as a dumb question, I like to know how/why things work and your example will work great for my needs.

    Thanks,
    Tom

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding Duplicates in Data (2003)

    Yes, the & operator can be used to concatenate strings. I find it easier to use (less typing) than the CONCATENATE function in most situations: the formula

    =A1&B1&C1

    is equivalent to

    =CONCATENATE(A1,B1,C1)

Posting Permissions

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