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

drspanklebum

New Member
Joined
Jan 14, 2019
Messages
2
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:


LASTNAME,FIRSTNAME(3)Spouse NameStreet AddressCity, StatePhone #Email
LASTNAME,FIRSTNAME(2)Street AddressCity, StatePhone #Email
LASTNAME,FIRSTNAME(12)Spouse NameStreet AddressCity, StatePhone #Email

<tbody>
</tbody>


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 (n) 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.
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,915
Office Version
2010
Platform
Windows
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 (n) 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?
 
Last edited:

drspanklebum

New Member
Joined
Jan 14, 2019
Messages
2
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,082
Messages
5,466,531
Members
406,484
Latest member
kaksolver

This Week's Hot Topics

Top