Transposing non-uniform set of data from one column into rows

[drspanklebum]

Greetings,
First off I want to thank all the people in this forum for all the great help and knowledge with Excel and VBA over the years. This is the first time I've had an issue I couldn't find the answer to though so I finally made an account. Here's my issue:

I have a large contact list I have to transpose into rows. The list was copied from an OCR'd PDF so I have everything in Column A, about 17,000 rows. Here's an example of what I have (and the NAME is capitalized).

Also a note that each name has a number in parentheses after it for some reason (it's just how it is in the source) which is basically random, so it all looks like this:

[Column A]
LASTNAME, FIRSTNAME (3)
Spouse Name
Street Address
CIty, State, Zip
Phone #
Email
LASTNAME, FIRSTNAME (2)
Street Address
CIty, State, Zip
Phone #
Email
LASTNAME, FIRSTNAME (12)
(etc)


This would be simple if each set contained the same number of items, but some folks don't have spouses, email addresses, etc, making each set a variable number of rows. I need to turn them into this:


[TABLE="width: 500"]
<tbody>[TR]
[TD]LASTNAME,FIRSTNAME(3)[/TD]
[TD]Spouse Name[/TD]
[TD]Street Address[/TD]
[TD]City, State[/TD]
[TD]Phone #[/TD]
[TD]Email[/TD]
[/TR]
[TR]
[TD]LASTNAME,FIRSTNAME(2)[/TD]
[TD][/TD]
[TD]Street Address[/TD]
[TD]City, State[/TD]
[TD]Phone #[/TD]
[TD]Email[/TD]
[/TR]
[TR]
[TD]LASTNAME,FIRSTNAME(12)[/TD]
[TD]Spouse Name[/TD]
[TD]Street Address[/TD]
[TD]City, State[/TD]
[TD]Phone #[/TD]
[TD]Email[/TD]
[/TR]
</tbody>[/TABLE]


So I can't just transpose every 6 rows or they will turn into a mess, since some sets don't have spouses, some sets don't have emails, etc. My first thought is to have my script transpose a set until it recognizes that the next in cell in Column A contains all capital letters (or even just 3 or 4 capital letters), and move onto the next set, since the only constant is that each new set contains a name in all caps, with a number in parentheses after it. So when it sees FIRSTNAME,LASTNAME it will know that it's a new set and know to move onto the next row of data.

I hope this makes sense and I hope someone can help - I am completely stumped!!

Thanks so much again for any time/thought that goes into this and anyone that could help.

 

[Rick Rothstein]

My first thought is to have my script transpose a set until it recognizes that the next in cell in Column A contains all capital letters (or even just 3 or 4 capital letters), and move onto the next set, since the only constant is that each new set contains a name in all caps, with a number in parentheses after it. So when it sees FIRSTNAME,LASTNAME it will know that it's a new set and know to move onto the next row of data.

Will each name always have a street address?

Will each name always have a city, state and zip code (all three)?

Will there always be a comma between the city and state?

Will there always be a comma between the state and zip code?

 

[drspanklebum]

Will each name always have a street address?

Will each name always have a city, state and zip code (all three)?

Will there always be a comma between the city and state?

Will there always be a comma between the state and zip code?

Thanks for the response.

Answer to these would be no, not always. Some of the addresses are Canadian, German, etc so don't conform to USA address conventions. I'm not so keen on identifying or even splitting each address. I moreso just want to get each set into one row, starting with the NAME, since the only thing that is always true is that the name is in all caps. From there I will have to go back and manually shift each piece of data into its proper column, but getting each set into a column would be a huge help.

Is there a way to read the contents of a cell up to a certain count? What I mean is this:

Read the next column and identify that the first three characters contain capital letters. If the following column contains three consecutive capital letters, start transposing a new row of the dataset. So once it recognizes a new NAME, go to the next row and transpose it there.

I can go back from there and make sure each item (address, phone #, email, etc) is in the proper column.

Thanks so much again