I cross almost all excel post in the Internet, and I really need your help in this please.
I do not have expertise in scripting, but I am looking forward to give a help on my case.
I have all my contacts in google contact CSV which shows an address in separate rows "I don't know my but it is their format". However, the key point in 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 a multi tasking 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 itself is like:
A
1 Harry,Harry,VIP Group,
2 Work,Central Region,Work,+1xxx,
3 Potter,Potter,Friends,
4 Jack,Jack,Central Region,
5
6 Work Group,
7 Work,Tel,+1xxx,
And the result I dream of like:
Z
1 Harry,Harry,VIP Group,,Work,Central Region,Work,+1xxx,
2 Potter,Potter,Friends,
3 Jack,Jack,Central Region,,,Work Group,,Work,Tel,+1xxx,
What I'm doing now, Extract Duplicates in a reference column (Y) to know that start and end point by using the VBA from this link, that shows the word "work" as duplicate.
Extract Duplicates VBA, Thanks for "Matt Rogers" http://www.mrexcel.com/forum/excel-q...word-cell.html
Then merge the cells manually for each contact in a other referance column (Z) using the VBA form this link.
Cell Merge VBA, Thanks for "Jon Acampora" http://www.excelcampus.com/keyboard-...ange-of-cells/
Then I copy the column (Z) and past it in the same column as values to avoid coping the formula and get the data only.
Then I do a manually cleaning job to remove the blank rows which haven't data.
Then delete the columns from (A) to (Y) to start working on the sheet.
Sorry for my long question, and I do appreciate everyone trying to help.
Bookmarks