I have a huge list of names (over 26K of rows) with multiple diagnoses for every Dr. visit. Sometimes they see the same Dr (Provider), sometimes they have the same diagnoses.
The report has the following 9 columns:
Name, Provider, Diag1, Diag2, Diag3, Diag4, Alt ID, Member DOB, Relation.
I need a way to remove the duplicates for each name so that I only see one unique Provider and one unique diagnosis per person.
In this example, Joe Blow has seen 6 unique providers (BHP, and has 10 unique diagnoses. The second person saw 1 unique provider with 1 unique diagnosis. The third person saw 2 unique providers with 1 unique diagnosis.
Name Provider Diag1 Diag2 Diag3 Diag4 ID Rel
Joe Blow BHP SDM IFD BLA ABC 1 S
Joe Blow BHP SMN
Joe Blow IEM GUI UTI
Joe Blow LRM MCM
Joe Blow MMG ASO
Joe Blow PIH FAB ASO
Joe Blow PIH GUI UTI
Joe Blow QD IPS
Dad Doe BHP AU
Bug Bun BHP IBO
Bug Bun QD IBO
So my ending report could possibly end up being 20-30 columns wide but less than 4K of rows long.
The bottom line is that for each person in Col A, there can be multiple unique providers but only one unique diagnosis.
So I would need to take any data to the right of col A on each duplicate name in Col A and move it to the first empty row to the right of col H on the first original name in Col A so that Joe Blow ends up with his original row plus in Cols H on, any unique data in Col B-H attached to Joe Blow duplicate rows.
I hope this makes sense. Any help would be GREATLY Appreciated, oh mighty gurus of Excel and VB!
Bookmarks