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: spreadsheetpage.com/index.php/tip/…. You can still access the pivot table wizard in later versions of Excel by pressing Alt-D-P. — lori_m
– lori_m, Commented Jul 30, 2012 at 16:40
Thank you, do you want to post it as an answer so that I can credit you for it? — BadgerBeaz
– BadgerBeaz, Commented Jul 30, 2012 at 17:00

ΩmegaMan · Accepted Answer · 2014-05-08 21:14:09Z

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 8, 2014 at 21:14

ΩmegaMan

32.2k13 gold badges110 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:31:59Z

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, 2017 at 12:31

CommunityBot

11 silver badge

answered Jul 30, 2012 at 17:26

lori_m

5,5751 gold badge21 silver badges30 bronze badges

Comments

sancho.s ReinstateMonicaCellio · Accepted Answer · 2017-09-01 08:55:01Z

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.

Collectives™ on Stack Overflow

Excel Converting rows to columns with groups

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related