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.

Could you not use formulas?

=C2 & " | " & D2 'for name and address
=A2 & " | " & D2 'for SSN and address
=A2 & " | " & C2 'for SSN and name
=A2 & " | " & C2 &" | " & D2 'for SSN and name and address

Pl Enter Array formula is in Q2 cell and drag downwords. It may suite your requirement.Good Luck.

Please Login or Register  to view this content.

I am not coming up with the expected results. I am using a modfied version of kvsrinivasamurthy suggestion in VBA code

Please Login or Register  to view this content.

As an example, as per attached sample, line item number 4 (Helen Klogman) is being grouped with line items 3, 5, and 6. Line item 4 should not be grouped with these other line items as there is (1) no match on Name & Address (2) no match on SSN & Address and (3) no match on SSN & Name.

The other example is for line items 31-34. Line Item 34 should be grouped with line items 31-33. Line item 34 should be grouped with these other lines items…line item 34 is grouped with line item 31 (Same SSN and Same Name).

PL See the attached file.

Still not coming up with the expected results. I have some records that should be grouped with other records but aren't. And I have other records that are grouped with other records that should not be grouped.

For example (see attached):

EXAMPLE 1: Record number 7 should be grouped with records 5 & 6. I expected the group number for record number 7 to be with group 4. The formula is assigning it to group 5. Record 7 should be grouped with record 5 and 6 because there is a match between records number 6 and 7 on "Name & Address" combination.

EXAMPLE 2: Record number 14 should be grouped with records 13, 15, & 17. I expected the group number for record number 14 to be with group 11. The formula is assigning it to group 12. Record 14 should be grouped with records 13, 15, & 17 because there is a match between records number 14 and 15 on "SSN & Name" combination.

EXAMPLE 3: Record number 28 should be grouped with record 20. I expected the group number for record number 28 to be with group 14. The formula is assigning it to group 16. Record 28 should be grouped with record 20 because there is a match between records number 28 and 20 on "SSN & Address" combination. The other part is record number 28 is also being grouped with records 22, 23, & 25. It should not be a part of group 16 as there is no relation.

Pl see attached file.