Yes if there is a logic in the way the address lines are entered. Please send me the file so I can check the fields. To do so go to http://www.wikisend.com/ and upload the file (no need to signup) then copy the "File ID" number (or the download link) that you will be given and paste it here in your reply.
If the file has sensitive information let me know before you upload it
It's ok. Please send me the file and I will try to find a pattern
Ok let's first get file through
Ok try uploading it here, then send me the Share URL link
Got it, thanks
Gai, yes I can create a macro that will transpose each address into 13 columns, but since not all the address have the same number of rows the listing may need be in the same columns.
e.g. if address 1 has 13 rows, the fields will be placed on anther sheet in columns A thru M, starting in A
and if the next address has 10 rows, it will be placed in columns A thru J starting form A
and so on
Would that be ok
Ok I wrote a macro (and some formulas) and was able to process all 36,000 rows so that each address is transposed into a single a row. Here is a screenshot showing the result of the 50 addresses
Have a look and let me know if it is ok so I can send you the complete list in the Excel file
please refresh this page to see the updated screenshot
Can you give me an example. Which row on your original list?
Ok I see what you mean.
I assumed that column A is all titles. But apparently it has some address related data.
Ok will fix that
I will try do that if the field names are XXXXX XXXXX have to create another macro for that
lol.. already wrote that macro and it is now possessing the data...
The two macros will take some 20-30 mins to complete transposing and changing all the data
Will get back to you as soon as I am done
It is taking a lot of time processing all the data. In the meantime here is the initial list after running two macros, 10 sets of formulas, and plenty of manual modifications. It may not be perfect but I think it is as close it can get with the given time and scope
Hope this is ok. Let me know what know what you think
Not sure how else to send you the file (we cannot use email on this site). Will try other methods. In the meantime here is what the final list looks like
Yes I think I can do that, but first let's get this one accepted which is per your original instructions so I can get some credit for my time on this rather extended question
Yes Gai I will be online most of the time today
Thanks Gai. Much appreciated.
I will start working on the name/email address shortly. Will get back to you as soon as I am done
For example in row 24, the formula in FT24 counts the number of cells in the range E24 thru FM24 that are larger that C24
Oops!. You are correct.
Please ignore it, and thanks for letting me know
I should have the list ready in about 15 mins
Ok here is the file with the names and email for all the addresses. Please note that some names have more than one email address associated with them
Please check it and let me know if it needs and adjustments
Gai, I am not sure I fully understand. Are you referring to the original data?. If so the final list is so far removed from that data, some 8 steps have been performed to transpose the data and extract the names and emails (the rows in the original list no more relate to the rows the final transposed and condensed list)
If it helps I can extract the first names and the last names from the last list and put them in separate columns
Let me know
Ok I managed to get all the names that are preceded by the "N:" character. They are placed in column A in this file
Hope this is ok
Ok one moment please...
Had to do most of extraction manually because of the inconsistency of the data
Here if the final list
Hope this helps
You're welcome Gai! Glad you are happy with the result