Data Massage

[rezolutions24]

Im having problems with a strange file output in Excel.

Normally I deal with data that includes headers. (ie. Top row is the header and the rest of the data referencing those header values.)



This particular file has the headers in rows rather than columns.


A1= First Name: B1=John C1=City: D1= Pleasantville
A2= Last Name: B2 =Doe C2= State D2= FL
A3=Address: B3=1122 Nice street C3=ZIP D3=00000
4 (are blank rows, separates for new record)
5 (are blank rows, separates for new record)
A6= First Name: B6=Mike C6=City: D6= Pleasantville
A7= Last Name: B7 =Jones C7= State D7= FL
A8=Address: B8=1122 Nice street C8=ZIP D8=00000
9 (are blank rows, separates for new record)
10 (are blank rows, separates for new record)


As you can see the headers are repeated in several cells and the values in the next cell to its right.

I want to be able to somehow transpose this data into


First Name Last Name Address City State ZIP
John Doe 1122 Nice Street Pleasantville FL 00000
Mike Smith 1010 Nice Street Pleasantville FL 00000
Tommy Guns 21 Jump Street Pleasantville FL 00000

Is there some type of way I can do this?

[stanleydgromjr]

rezolutions24,

Welcome to the Excel Forum.

Detach/open workbook ReorgData w1 wR AArea NR - rezolutions24 - EF761592 - SDG13.xlsm and run macro ReorgData.

[rezolutions24]

rezolutions24,

Welcome to the Excel Forum.

Detach/open workbook ReorgData w1 wR AArea NR - rezolutions24 - EF761592 - SDG13.xlsm and run macro ReorgData.

Thanks a bunch. I guess since Im not an expert in VBA, its going to be difficult trying to decrypt this macro and apply it to my work.

I appreciate the response.

[stanleydgromjr]

rezolutions24,

its going to be difficult trying to decrypt this macro and apply it to my work.

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

[rezolutions24]

rezolutions24,




To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

Thanks Stan for your attention. Attached is a sample with dummy data.

Therefore the headers are identical, but the values are in the correct format.

Let me know if its still unclear.

[stanleydgromjr]

rezolutions24,


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.

Then run the ReorgDataV2 macro.

[rezolutions24]

That was awesome work Stanley!! Thanks for the HUGE help on this one!