Excel Converting rows to columns with groups

Question

Let's say that I have my columns in excel set up like this:

Name     A    B    C    D  
George   10   5    4    6

And I want it to instead be like this:

Name   Category   Amount  
George     A        10  
George     B         5  
George     C         4  
George     D         6

Is there an easy way to do a copy and paste with transpose, or with a pivot table, or does it have to be done with VBA? I've been trying to only do it with VBA but I havent gotten very far.

Try this tip: http://spreadsheetpage.com/index.php/tip/creating_a_database_table_from_a_summary_table/. You can still access the pivot table wizard in later versions of Excel by pressing `Alt-D-P`. — lori_m, Jul 30 '12 at 16:40
Thank you, do you want to post it as an answer so that I can credit you for it? — BadgerBeaz, Jul 30 '12 at 17:00

score 3 · Answer 1 · answered May 08 '14 at 21:14

3

How about a real answer instead of a link?

Highlight the two rows to pivot.
Copy
Find open row
Paste Special -> Transpose

answered May 08 '14 at 21:14

ΩmegaMan

29,542
12
100
122

score 2 · Accepted Answer · edited May 23 '17 at 12:31

2

As per comment, you can follow that link. A related SO answer that describes similar steps for Excel 2010 is How can I transform rows into repeated column based data?.

edited May 23 '17 at 12:31

Community

1
1

answered Jul 30 '12 at 17:26

lori_m

5,487
1
18
29

score 0 · Answer 3 · answered Sep 01 '17 at 08:55

If you want to maintain a "dynamic" link between your source and target data, so whenever your source data changes, your target also does it automatically, you need formulas. In that case, you can use Item 7 from Excel: Formulas for converting data among column / row / matrix :

To get in matrix_data3 the transpose of matrix_data2, one only needs to use matrix_data2_top_left and matrix_data3_top_left, with the formula

=OFFSET(matrix_data2_top_left,COLUMN()-COLUMN(matrix_data3_top_left),ROW()-ROW(matrix_data3_top_left))

and copied to a suitable target range.

Note that matrix_data2_top_left and matrix_data3_top_left are convenient Excel range names for the upper left cells of the source and target ranges, respectively, and they can be replaced in the formula by their absolute references (e.g., $B$1 and $B$6). Also, matrix_data2 and matrix_data3 are Excel range names, but these ranges (either as names or absolute references) are not needed here.

Excel Converting rows to columns with groups

3 Answers3

Linked