I have a spreadsheet in Excel that contains a "Member ID" in the first column, with six variables, related to this Member ID, in the next six columns.
I need to somehow convert these columns into rows, but still have the Member ID column at the beginning of each row.
Here's the data as it stands (there are 5000 rows, hence hoping to find an automated solution):
MEMBER 1 | AAA | BBB | CCC | DDD | EEE | FFF
MEMBER 2 | BBB | ZZZ | FFF | AAA | RRR | SSS
MEMBER 3 | YYY | FFF | OOO | MMM | PPP | AAA
And here's the format that I need:
MEMBER 1 AAA
MEMBER 1 BBB
MEMBER 1 CCC
MEMBER 1 DDD
MEMBER 1 EEE
MEMBER 1 FFF
MEMBER 2 BBB
MEMBER 2 ZZZ
MEMBER 2 FFF
MEMBER 2 AAA
MEMBER 2 RRR
MEMBER 2 SSS
MEMBER 3 YYY
MEMBER 3 FFF
MEMBER 3 OOO
MEMBER 3 MMM
MEMBER 3 PPP
MEMBER 3 AAA
I attempted to follow the steps in this question: Split multiple excel columns into rows , however that seems to only work for numeric values and not text.
Any help that anyone can give me would be hugely appreciated, I'm stumped as to how to do this. Thanks so much in advance!
This question is stale but I just needed it, so I'll answer.
I found the solution by Chris Chua on Quora, please upvote him there if this is helpful: https://www.quora.com/How-do-I-convert-multiple-column-data-into-a-column-with-multiple-rows-in-excel
I'll also copy-paste it here in case of a broken link:
Apologies. I can’t really see the writings clearly, but I’m assuming that you want to unpivot the column headers as part of the data set.
Here I am suggesting a solution using Microsoft Excel’s Power Query feature. If you have Excel 2016, the feature is found under Data | Get & Transform ribbon command. Otherwise if you have Excel 2013 or the Professional Plus version of 2010, you can download the Power Query add-in from here.
Step 1: Select all the data, press Ctrl+T to create a table, and under Create Table, make sure that My table has headers is selected.
Sample data create table dialog
Step 2: In Excel 2016, go to Data | Get & Transform | From Table. For Power Query add-in for Excel 2013 and 2010, look for something in the Power Query tab ribbon that says From Table.
Ribbon location for "From Table"
Step 3: The Power Query interface will open up. Click on the header of No. Column, hold Ctrl and click on the header of the Name Column. With both columns now selected, right click on the headers and select Unpivot Other Columns.
Right Click, Unpivot Other Columns screencap
Step 4: Notice that the data is now unpivoted (which is what you wanted). Double click on the Attribute and Value headers to rename them.
Step 5: Click Home | Close & Load.
Close & Load screencap
Step 6: Excel will load the new cleaned up table onto a new sheet. Ta-da you’re done!
Screencap of pivoted table, which is the desired output.
To update any new data in the future:
Supposed you now have new data in your original table.
Updated sample data table screencap with a new column, row and value inserted.
Go to the new cleaned up table, right click and select Refresh.
Screencap of right-clicking inside the pivoted output table, clicking Refresh.
The new data will be updated immediately.
This should be doable, try saving excel file as csv then make a script to format that csv to your liking then import csv into excel.
There are many scripting languages like bash, powershell, python and tutorials on how to different things with them. Python is beginner friendly.
Thanks Spidey for your help. I managed to get it sorted using Sublime Text rather than Excel.
For those interested, I just copied the six columns into Sublime Text, did a find & replace for the gap/space/tab character between each column and turned this gap into a return/new line. That gave me a giant list of variables.
I then copied in the first column (Member ID), selected all the rows and did "Split selection into lines", which gave me a cursor at the end of each line. I then just selected each line and did a copy -> return -> paste, six times. This gave me a giant list of each Member ID showing up six times.
Then it was just a matter of (in Excel) pasting that long Member ID list into Column A, with the long list of variables (from the first step) into Column B. Done!
