Name occuring for 2 codes (2003)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Thank you. That works good enough

Reply | Quote

=IF(SUMPRODUCT(–(A1:A1000=”Smith”),–(B1:B1000=”H2014″))*SUMPRODUCT(–(A1:A1000=”Smith”),–(B1:B1000=”H2015″))0,”Duplicate”,”All same”)

Reply | Quote