Advanced columns to rows with example attached

I have a file (example attached) that has a column with names, followed by a column containing that person's contact information, followed by a column with that person's rank. The names are listed as "Last, First MI" and have from 0 to 4 blank cells beneath them depending on how much contact information is included for each individual. The contact info never has an empty cell, but they are listed in the same order as long as that information is provided (Address, "City, ST ZIP", Email, Home Phone, Cell Phone), and the rank is in cells the same way as the names. Each new name is in the cell on the column to the left of it's first address information and the column to the right shows their rank. Any additional address information has blank cells to its right and left.

There are about thousands rows of this, with about 1500 names. I need this information to be listed in the following manner (if possible):

First Name/Last Name/MI/Address/City/State/ZIP/Email/Home Phone/Cell Phone/Rank

I have been able to do this by copying everything into Word, where it is all listed separated by ^p. I then change each ^p into a ^t as long as it's not followed by a ^#.^#^# (which is how the ranks are formatted). But with this, the address information and ranks go into random columns depending on how much address info was included for that individual.

My example includes how I have the information now and how I would like it to be listed. I appreciate any feedback. I am not very good with macros, but will figure it out if that is the best way to solve this issue.

Thank you in advance for taking the time to read through this.

nandaopira,

Welcome to the Excel Forum.

Thanks for the workbook.

However, we can not accurately work with the data you posted.

Can we see the actual raw data, say, for 10 to 20 names,,,,,,,, with sensitive data changed?

nandaopira,

Thanks for the latest workbook.

Your first workbook contained three columns of raw data - the newest workbook contains four columns of raw data????

Can we have another workbook with the raw data in the first worksheet (left most sheet in he tabs view), and another sheet with the titles in row 1, and with the first example from the first worksheet, manually entered by you for the results you are looking for.

nandaopira,

In your latest workbook, Sheet3 (results) the first and last names are not in the correct title column?????

B1 = First Name, C1 = Last Name
B2 = Moore, C2 = Linda

nandaopira,

I assume that the raw data is in the first worksheet (left most worksheet in the display for the tabs/worksheets).

The below macro works correctly based on the raw data that you supplied.

The macro will create a new worksheet Results to display the results you are looking for.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ReorgData macro.

nandaopira,

Thanks for the feedback.

You are very welcome. Glad I could help.

Come back anytime.

nandaopira,

Please supply another workbook containing several examples of "A new user entered their information without including their zip code".