Need to reorg table so new table is row1= col1, col2,n1,col3,row2=col1,col2,n2,col4

I have a data set with 5 columns. The first 2 columns are identifiers and the last 3 columns are properties. I need to convert the data so that I have 2 identifiers on each row. Then I want to put the name of each property on one row along with the value of that property. So along with the name of column 3 as column 3 and value of column 4 as column 4. The next row of data will be the first 2 columns of the data set plus the name of column 4 as column 3 and the value of column 4 as column 4.
Example data:

What I have is:

FirstName LastName Height Weight HairColor
Bob Smith 6'0" 200 Brown
Mary Jones 5'6" 120 Blond

What I need is:

FirstName LastName PropertyName PropertyValue
Bob Smith Height 6'0"
Bob Smith Weight 200
Bob Smith HairColor Brown
Mary Jones Height 5'6"
Mary Jones Weight 120
Mary Jones HairColor Blond

Does anyone have any ideas on how to do this? Many thanks for your help. My actual files might have 4 - 6 columns of identifiers, up to 100 rows of properties and hundreds or thousands of rows of data so this is not a trivial conversion.

Brantly,

Welcome to the Excel Forum.


Detach/open workbook ReorgData w1 wR - Brantly - EF853973 - SDG15.xlsm and run the ReorgData macro.



If you want to use the macro on another workbook:


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.

melliesue,

Your workbook data structure is not similar to the original post by Brantly.

Please do not post your questions in threads started by others - - this is known as thread hijacking.

Always start a new thread for your questions and, if you think it helps, provide a link to any other thread as a reference.

Start a new thread for your question and be sure to give it a search friendly title that accurately describes your need.

And, please manually complete another worksheet with the results you are looking for.