Hello and thank you in advance for the assistance!
I have an export from a database that contains about 100k rows and contains multiple rows for the same customer, but each row has different information for that customer, as shown below. [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 980"]
[TD="class: xl66, width: 126"]Name[/TD]
[TD="class: xl66, width: 110"]AddressName[/TD]
[TD="class: xl66, width: 80"]Car[/TD]
[TD="class: xl66, width: 90"]Training[/TD]
[TD="class: xl66, width: 83"]GPS[/TD]
[TD="class: xl66, width: 83"]Accessories[/TD]
[TD="class: xl66, width: 173"]Address[/TD]
[TD="class: xl66, width: 98"]City[/TD]
[TD="class: xl66, width: 40"]State[/TD]
[TD="class: xl66, width: 43"]Zip[/TD]
[TD="class: xl66, width: 109"]Country[/TD]
[TD="class: xl66, width: 113"]Phone[/TD]
[TD="class: xl66, width: 162"]Email[/TD]
[TD="class: xl67"]allen b[/TD]
[TD="class: xl67"]Main address[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]12 Main St[/TD]
[TD="class: xl67"]Cedar Springs[/TD]
[TD="class: xl67"]MI[/TD]
[TD="class: xl67, align: right"]49319[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl67"]312-154-6587[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl67"]allen b[/TD]
[TD="class: xl67"]shipping address[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]32 Poplar Ave.[/TD]
[TD="class: xl67"]Rapid City[/TD]
[TD="class: xl67"]SD[/TD]
[TD="class: xl67, align: right"]57701[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl67"]455-156-5432[/TD]
[TD="class: xl69"][email protected] gmail.com[/TD]
[TD="class: xl67"]allen b[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]x[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl67"]allen b[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]x[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl67"]Elisha W[/TD]
[TD="class: xl67"]Main Address[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67, align: right"]349344[/TD]
[TD="class: xl67"]Grand Junction[/TD]
[TD="class: xl67"]CO[/TD]
[TD="class: xl67, align: right"]81501[/TD]
[TD="class: xl67"]UNITED STATES[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl69"][email protected] msn.com[/TD]
[TD="class: xl67"]Elisha W[/TD]
[TD="class: xl67"]shipping address[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]Moose[/TD]
[TD="class: xl67"]WY[/TD]
[TD="class: xl67, align: right"]83012[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl67"]Elisha W[/TD]
[TD="class: xl67"]FFL[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67, align: right"]20934[/TD]
[TD="class: xl67"]Grand Junction[/TD]
[TD="class: xl67"]CO[/TD]
[TD="class: xl67, align: right"]81501[/TD]
[TD="class: xl67"]UNITED STATES[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl67"]Elisha W[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]x[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]asld333[/TD]
[TD="class: xl67"]Mesa[/TD]
[TD="class: xl67"]AZ[/TD]
[TD="class: xl67, align: right"]85215[/TD]
[TD="class: xl67"]UNITED STATES[/TD]
[TD="class: xl67"]447-555-4444[/TD]
[TD="class: xl69"][email protected] gmail.com[/TD]
[TD="class: xl67"]Erin D[/TD]
[TD="class: xl67"]Main address[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]BOX 135[/TD]
[TD="class: xl67"]Winston[/TD]
[TD="class: xl67"]MT[/TD]
[TD="class: xl67, align: right"]59647[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl67"]Erin D[/TD]
[TD="class: xl67"]Shipping address[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]Winston[/TD]
[TD="class: xl67"]MT[/TD]
[TD="class: xl67, align: right"]59647[/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl68"] [/TD]
[TD="class: xl67"]Erin D[/TD]
[TD="class: xl67"]Main[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67, align: right"]44068943[/TD]
[TD="class: xl67"]SANDY[/TD]
[TD="class: xl67"]UT[/TD]
[TD="class: xl67, align: right"]84070[/TD]
[TD="class: xl67"]UNITED STATES[/TD]
[TD="class: xl67"]445-645-7894[/TD]
[TD="class: xl69"][email protected] hotmail.com[/TD]
[TD="class: xl67"]Erin D[/TD]
[TD="class: xl67"]Main[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl67"]alsdkf1[/TD]
[TD="class: xl67"]Pocatello[/TD]
[TD="class: xl67"]ID[/TD]
[TD="class: xl67, align: right"]83201[/TD]
[TD="class: xl67"]UNITED STATES[/TD]
[TD="class: xl67"] [/TD]
[TD="class: xl68"] [/TD]
[/TABLE]
I need a macro/code to the following:
Overall objective: Find all the rows that have a matching customer name in column A, create one merged row with a complete set of data (as defined below), then delete all of the other rows for that customer.
1) Enter the customer name into column 1 of the new merged row.
2) Address: If there is a complete address (columns 7-10) for the "Main Address" (column 2), return that address (all addresses are US addresses, so column 11 can be ignored) to the merged row and populate column 2 of the merged row with "Main Address". If there is not a complete address for the "Main Address", return any other address that is complete (cols 7-10) to the merged row and populate column 2 with the address type indicated (Shipping, FFL, etc). If there is no complete address at all for the customer in any row, leave the address fields (columns 2, 7-10) blank.
3) If there is a phone number (col 12) and email address (col 13) with the complete address that is chosen, return those to column 12 and 13 of the merged row. If there is no phone or email with the chosen complete address, return any available phone and gmail listed for the customer in any row.
4) Merge any values in columns 3-6 for all rows for the customer into the new merged row.
5) Delete all rows for the customer except the new merged row
6) Repeat steps 1-5 for the each successive customer until the entire spreadsheet has been evaluated and merged.
Hope this makes sense and thank you again for the assistance!