VBA to assign a group/household number to similar invoice customers

I have a file of ~40,000 unique invoices, which I need to group by customer name. The file is made of following fields:
Invoice Number
First Name
Last Name
Mailing Address
City
Zip

I need to accomplish two parts to this process:

(1) The first step is to identify similar customers and update Mailing Address, if required. A similar address is all three fields being equal: 1 - Last Name (exact match) 2 - Mailing address (exact match of first 5 characters only) 3 - Zip Code (exact match). If an exact match is found, then update address, if required. As an example, two invoices are as follows:

Invoice 1 Last: Smith
Invoice 1 Address: 123 Smith Avenue
Invoice 1 Zip: 10021

Invoice 2 Last: Smith
Invoice 2 Address: 123 Smith Ave
Invoice 2 Zip: 10021

In this case, change address for Invoice 2 from 123 Smith Ave to 123 Smith Avenue

(2) the second step is to assign a group number. To continue above example, both Invoice 1 and Invoice 2 are assigned to Group Number 1.

Once all similar records have been updated for one group, then move on to next group and so on. Attached is small sample of data with expected/desired results (column I and J). Thanks

Hi

1) How do you determine which mailing address is the one that is used when there are multiple appearances? Looking at the first one that you have highlighted (LEEBERGER), I would have thought that the first in the list (ie the first with group number 2) would be the one used, but it isn't. So how do you determine which is used?

2) It may pay to review your example data. Looking at row 11 in your example file, you have given it a group number of 5, but it doesn't have the same details as all the other group 5 entries. Same with row 20.

Here's a couple of formulas that may help.

Using your example file

M2: =C2&LEFT(D2,5)&F2
N2: =COUNTIF($M$2:M2,M2)
O2: 1
O3: =IF(N3=1,MAX($O$2:O2)+1,"")
P2: =IF(N2=1,O2,INDEX(O:O,MATCH(M2,M:M,0)))
Q2: =IF(N2=1,D2,INDEX(D:D,MATCH(M2,M:M,0)))

Copy these down. Column P will give you the group number, and column Q will give you a consistent address based on the first appearance of an item.

rylo