VBA to group similar records

This is somewhat a continuation to my thread number 806739 but a different issue. http://www.excelforum.com/excel-prog...customers.html

I have code that is grouping accounts in 3 stages

Stage 1 – Group by Name and Address
Stage 2 – Group by SSN and Address
Stage 3 – Group by SSN and Name

Once I have all the accounts grouped in the above 3 stages, I then need to create one ‘Master’ group.

As an example, I have 3 records as follows:

RecordNo…………..SSN……………………..ClientName………………..Address…………..
1…………………………222222222……….…Eugene Lemon…………..307 Broad Street
2…………………………444444444………….Renee Lemon……………..P.O Box 345
3…………………………444444444……….…Renee Lemon……………..307 Broad Street

Based on above table, a group is created between record 1 and record 3 (group created in stage 1; same last name and same address. Another group is created between record 2 and record 3 (group created in stage 3; same SSN and same last name).

The issue encountered is there is no group between record 1 and record 2. However, because record 2 is grouped with record 3, and record 3 is grouped with record 1, then we can assume the record 2 should also be grouped with record 1.

I can’t figure out how to create a group between all 3 records. I did try using the ‘Small’ function once all groups in stages 1-3 were completed but this didn’t work.

I have attached a small sample of data with expected results. As example, there are 4 accounts with the last name “LEMON”. Based on the criteria, all 4 accounts should be grouped together. However, the two accounts for “RENEE LEMON” are being placed in two separate groups. All accounts for “LEMOM” should be under the same group number. In this case, it would be group number “4”.

Thanks in advance for any assistance.