Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    May 2008
    Location
    Albuquerque, New Mexico, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Name occuring for 2 codes (2003)

    I have a spreadsheet with one column containing member's full name and another column contains 2 Service Codes, either H2014 or H2015. A member is billed by one or the other...however, sometimes they are billed for both and I need to view the list of people who have been billed for both service codes. In other words...looking down the service code column if H2014 shows up for Mr. Smith and then you continue down the column and H2015 shows up for Mr. Smith...then give me that list of names

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name occuring for 2 codes (2003)

    Assuming the names are in the A column and they can only appear on the list once for each code.
    Using a dummy column:
    in C2 (or your dummy column) enter the formula =countif(A:A,A2)
    fill down and then sort descending on the results.

    Members who have both codes will have a 2 in the column.

    For a unique list, highlight the names in the A column that have a 2 as a formula result and go to Data / Filter> Advanced Filter...

    Chose a "Copy to another location" , pick you location (it must be on the current sheet) - Check the Unique records only. Click OK

  3. #3
    New Lounger
    Join Date
    May 2008
    Location
    Albuquerque, New Mexico, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name occuring for 2 codes (2003)

    The names can appear multiple times for the same code...so what you suggested will not work

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name occuring for 2 codes (2003)

    Do the Data / Filter > Advance Filter using the two columns, then proceed using the directions in my first post.

  5. #5
    New Lounger
    Join Date
    May 2008
    Location
    Albuquerque, New Mexico, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name occuring for 2 codes (2003)

    This does not work. The countif in a separate column can give numbers like 10 or 20 because that name appears that many times with the same H2015 code. I need only the names where they have both codes appearing and the advanced filter will not do this for me.

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

    Re: Name occuring for 2 codes (2003)

    See the attached workbook. You can select TRUE from the dropdown list in the last column header to select the names that have both codes.
    Attached Files Attached Files

  7. #7
    New Lounger
    Join Date
    May 2008
    Location
    Albuquerque, New Mexico, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name occuring for 2 codes (2003)

    Thank you. That works good enough

  8. #8
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name occuring for 2 codes (2003)

    =IF(SUMPRODUCT(--(A1:A1000="Smith"),--(B1:B1000="H2014"))*SUMPRODUCT(--(A1:A1000="Smith"),--(B1:B1000="H2015"))<>0,"Duplicate","All same")

Posting Permissions

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