Link to home
Start Free TrialLog in
Avatar of Martin Rees
Martin ReesFlag for United Kingdom of Great Britain and Northern Ireland

asked on

break up excel columns into rows

i am having a problem with an export from one system I have here and then importing it into another.  I have an excel sheet that currently has a few columns with information which i need to create something to automatically break the columns up into rows.  to explain Column A contains a persons name then column b, c, etc contains values pertaining to the person.  what i need to do is change this to a format where for each value in a column it creates a separate line giving the persons name, then a value, then the figure in the column.  but to complicate things the value wont be the column name it will be something else but will always be the same value

User generated image
above shows what i have which is the top table and what i need which it the bottom table

any help would be greatly appreciated.
Avatar of ☠ MASQ ☠
☠ MASQ ☠

Do you want to do this programmatically?

Just in case you don't, Excel has a feature that switches Rows for Columns already built-in

Copy the table then in a new sheet right click and choose Paste Special...
Check the "Transpose" box then OK.

Here's a C# option
https://stackoverflow.com/questions/12838403/c-sharp-transpose-method-to-transpose-rows-and-columns-in-excel-sheet
Copy and Transpose won't do what is required. That will convert the table from 7 columns and 4 rows to a table with 4 columns and 7 rows.

Name      Person 1      Person 2      Person 3
Value 1      5                                    5
Value 2                        7                  4
Value 3      5                                    5
Value 4                        7                  6
Value 5                                          6
Value 6                                          4

You can achieve it with INDEX/MATCH formulas, I will work on it and upload shortly.
ASKER CERTIFIED SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You need to use Power Query aka Get & Transform.  These products are embedded in Excel if you are using Excel 2013, 2016, etc.  It can do what you are asking for in a couple of clicks.  If interested I can lead you through the steps and/or send you an example.
See example.  This approach is really important if this exercise is repeated again and again.  - Tom
EE8.xlsx
@Tom - your source data in columns A to D is wrong, that is how it would be after doing a Copy and Transpose.

Looks like the result is almost there though.

I would also be interested in learning about Power Query, any suggestions for online learning??
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Martin Rees

ASKER

thanks Tom this is really helpful