Consolidating duplicate data

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!

Hi, Try this:-
Results start Sheet(2) Range "A1".

Please Login or Register  to view this content.

Regards Mick

I am getting a type mismatch when I try to run this. It may be my fault, because I goofed when I placed the above example - after ID is the Date of Birth, and then the Relation.

So although I stated correctly at first that there were 9 columns, I didn't show it in my example, and the data needs to be moved to the right of Col I, not H. I must have been really tired.

I also don't know if this would make a difference to the coding, but columns A-G contain text (all at least start with a letter), Col H is a date, and col I is text.