Conditional Cells Merging Macro

martel_9 - Mar 16, 2016 at 01:42 PM

I have the google contact in a CSV which shows an address in a separate rows. The key point on this file is the date that it is all of it in column (A) only with a comma separated, and the good news is the person name is always repeated in each contact, like (Harry,Harry) but also the word "work" is repeated like (work,Fax,work,Tel).
Now the pain point is the address which comes in multiple rows depend on the address details.

The goal is to run an intelligent macro to consolidate the data in one cell with all contact details.
For Example: The macro starts with A1 and check the first word against the next word after the comma. If we have a match, then this is a contact name like (Harry,Harry,,,,Group,Friends) and put it in Z1.

Then check the next cell A2 for the same, if we didn't find a match (PO Box,000), then this is an address, so macro will merge A2 with A1 in a comma separated as it is a CSV to be like (Harry,Harry,,,,Group,Friends,PO Box,000) and put the result in Z1.

This file it self is like:

1 Harry,Harry,,,,VIP Group,,,,,,,
2 Work,,,,Central Region,,Work,+1xxx
3 Potter,Potter,,,,Friends,
4 Jack,Jack,,,,,Central Region
6 Work Group,,,
7 Work,Tel,+1xxx