Help with converting multiple columns into rows

Punxsu · Aug 24, 2012

Hi Everyone,

I am trying to convert

Excel 2010

	A	B	C	D	E
School	ID	Race	Age	Gender	Group
ADM	Black	Female	Local
ADM	Black	Female	Local
ADM	Black	Female	Local
ADM	Caucasian	Female	International
ADM	Caucasian	Female	International
ADM	Caucasian	Female	International

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]2010[/TD]
[TD="align: right"]2011[/TD]
[TD="align: right"]2012[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]15[/TD]

[TD="align: right"] 161 [/TD]
[TD="align: right"] 174 [/TD]
[TD="align: right"]307[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]16[/TD]

[TD="align: right"] 174 [/TD]
[TD="align: right"] 192 [/TD]
[TD="align: right"]509[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]17[/TD]

[TD="align: right"] 157 [/TD]
[TD="align: right"] 181 [/TD]
[TD="align: right"]121[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]15[/TD]

[TD="align: right"] 176 [/TD]
[TD="align: right"] 165 [/TD]
[TD="align: right"]106[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]16[/TD]

[TD="align: right"] 186 [/TD]
[TD="align: right"] 173 [/TD]
[TD="align: right"]190[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]17[/TD]

[TD="align: right"] 176 [/TD]
[TD="align: right"] 177 [/TD]
[TD="align: right"]404[/TD]

</tbody>

Data

to

Excel 2010

	A	B	C	D	E	F	G
School	ID	Race	Age	Gender	Group	Year	Size
ADM	Black	Female	Local
ADM	Black	Female	Local
ADM	Black	Female	Local
ADM	Black	Female	Local
ADM	Black	Female	Local
ADM	Black	Female	Local
ADM	Black	Female	Local
ADM	Black	Female	Local
ADM	Black	Female	Local
ADM	Caucasian	Female	International
ADM	Caucasian	Female	International
ADM	Caucasian	Female	International
ADM	Caucasian	Female	International
ADM	Caucasian	Female	International
ADM	Caucasian	Female	International
ADM	Caucasian	Female	International
ADM	Caucasian	Female	International
ADM	Caucasian	Female	International

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]15[/TD]

[TD="align: right"]2010[/TD]
[TD="align: right"] 161 [/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]15[/TD]

[TD="align: right"]2011[/TD]
[TD="align: right"] 174 [/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]15[/TD]

[TD="align: right"]2012[/TD]
[TD="align: right"] 307 [/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]16[/TD]

[TD="align: right"]2010[/TD]
[TD="align: right"] 174 [/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]16[/TD]

[TD="align: right"]2011[/TD]
[TD="align: right"] 192 [/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]16[/TD]

[TD="align: right"]2012[/TD]
[TD="align: right"] 509 [/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]17[/TD]

[TD="align: right"]2010[/TD]
[TD="align: right"] 157 [/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]17[/TD]

[TD="align: right"]2011[/TD]
[TD="align: right"] 181 [/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]17[/TD]

[TD="align: right"]2012[/TD]
[TD="align: right"] 121 [/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]15[/TD]

[TD="align: right"]2010[/TD]
[TD="align: right"] 176 [/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]15[/TD]

[TD="align: right"]2011[/TD]
[TD="align: right"] 165 [/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]15[/TD]

[TD="align: right"]2012[/TD]
[TD="align: right"] 106 [/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]16[/TD]

[TD="align: right"]2010[/TD]
[TD="align: right"] 186 [/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]16[/TD]

[TD="align: right"]2011[/TD]
[TD="align: right"] 173 [/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]16[/TD]

[TD="align: right"]2012[/TD]
[TD="align: right"] 190 [/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]17[/TD]

[TD="align: right"]2010[/TD]
[TD="align: right"] 176 [/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]17[/TD]

[TD="align: right"]2011[/TD]
[TD="align: right"] 177 [/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]17[/TD]

[TD="align: right"]2012[/TD]
[TD="align: right"] 404 [/TD]

</tbody>

Results

Any help with a VBA code (possibly with comments) would help. Thanks.

Bert

Punxsu · Aug 27, 2012

Hello Peter_SSs,

Thanks. I was actually referring to the logic behind the x and y variables and that block of code. Anyway, it works and that's great and I am highly indebted to you. I just wanted to understand and perhaps be able to write something like that in future and not have to bother you or anyone else.

I have a slight problem now. Is it possible for it to keep the formats from the original table? I have some columns showing "9-May" instead of "5-9." I have a column as Age Groups and it seems to reformat all those (possible) to dates, which I don't need it to.

Thanks for your help.

Best.

Bertrand

Peter_SSs · Aug 27, 2012

Punxsu said:
I have a slight problem now. Is it possible for it to keep the formats from the original table? I have some columns showing "9-May" instead of "5-9." I have a column as Age Groups and it seems to reformat all those (possible) to dates, which I don't need it to.

Suppose that is column D ..

1. Put a new 'Const' line with the others near the start

Rich (BB code):

Const TextCol As String = "D"

2. Add the blue lines of code where shown

Rich (BB code):

'Using the newly added sheet
With ActiveSheet

    'Set the column to text
    .Columns(TextCol).NumberFormat = "@"
    
    'Put the headings in
    .Range("A1").Resize(, fixedcols + 2).Value = Headers

Punxsu · Aug 27, 2012

Hello Peter_SSs,

Thanks a ton. It worked like a magic. I am very appreciative. And thanks for your advice about the pivot table issue.

Regards,
Bertrand

Fazza · Aug 30, 2012

Perhaps alternative approaches are OK? Without code or formulas.

Make a pivot table directly from the source data. The fields become row fields except the Years 2010, 2011 & 2012 become data fields. Take out the sub-totals and you're nearly there. Copy and paste to a new location and fill in the blanks with a formula '=cell above' applied over visible cells.

Another way is to make a query table from the source data. This is similar to a pivot table but less widely known so I won't detail it further.

hth

Punxsu · Aug 30, 2012

Hello Fazza,

Thanks for your response. I will be very grateful if you could elaborate as I couldn't quite do what you had suggested. Before I even sought help to rearrange the data, I created the pivot tables from the source data but there was so many things I couldn't do because the pivot table was treating (as you rightly pointed out) the years as the data fields. I will definitely like to try your suggestion so get back to me.

Thanks.

Bertrand

Fazza · Aug 30, 2012

I'm not sure what elaboration is wanted, Bertrand..

Make a pivot table. Put the fields other than the years as row fields. Put the years in as data fields. Take out the subtotals. Copy & paste to a new location. Select blanks (such as CTRL-G or F5 and then ALT-S, K) and then in one cell =cell above, using up arrow maybe. CTRL-ENTER to enter in all cells. CTRL-H to replace the SUM OF with nothing to get the years back again. I've done this without reference to Excel; it'll be pretty close to correct.

If there is some step that isn't clear, please advise exactly what it is, what you've done, what happens, what doesn't happen, what it looks like, etc

Peter_SSs · Aug 30, 2012

Fazza, I can't see how to produce the required results following your instructions either. Below I've followed the steps (I think) to just before copying to a new location etc. As you can see, I have basically just re-created the original table and still have the year values and the 174, 192, 509 etc horizontally. How do you get them to appear vertically for each original line item, per the original request?

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

1

School

ID

Race

Age

Gender

Group

2010

2011

2012

Data

2

ADM

1

Black

3-9

Female

Local

161

174

307

School

ID

Race

Age

Gender

Group

Sum of 2010

Sum of 2011

Sum of 2012

3

ADM

1

Black

16

Female

Local

174

192

509

ADM

1

Black

16

Female

Local

174

192

509

4

ADM

1

Black

17

Female

Local

157

181

121

17

Female

Local

157

181

121

5

ADM

2

Caucasian

15

Female

International

176

165

106

3-9

Female

Local

161

174

307

6

ADM

2

Caucasian

16

Female

International

186

173

190

2

Caucasian

15

Female

International

176

165

106

7

ADM

2

Caucasian

17

Female

International

176

177

404

16

Female

International

186

173

190

8

17

Female

International

176

177

404

9

Sheet1

Fazza · Aug 31, 2012

hi, Peter

They default to that configuration for me. Excel 2003.

It looks like your screen image except the data fields 'sum of 2010', 'sum of 2011', etc are stacked up under each other adjacent to the Group field.

For me to get to the set up you show, I have to do an extra step - drag the "Data" button to become a column field.

So, to get from the set up you see, I would drag from the column field to become a row field.

This is in-line with a many forum posts over the years where users have it like I see & want it like you posted.

I wonder if post Excel 2003 pivot tables are slightly changed wrt this issue?

Howzat for you?

PS. Here is a link that shows what I'm trying to describe http://www.contextures.com/xlPivot02.html

Fazza · Aug 31, 2012

Well, now that I actually take the time to read the info at the link I posted just above, it describes

"In Excel 2010 and Excel 2007, when you add multiple data fields to the table, by default the data headings are arranged horizontally."

So, post Excel 2003 has changed.

For Excel 2003 it is as I see it; for Excel 2007 & 2010 it is as you see it. And to change it to the Excel 2003 format is simple as shown in the posted link

regards

Peter_SSs · Aug 31, 2012

Thanks Fazza. I was testing in Excel 2010 and yes, it is an easy change to get the vertical layout - now that I know how.

I doubt this will be an easy/possible method for the OP though. Apparently there is about 40 'year columns' and a lot of rows. When changed to the new layout, there are more rows than will fit on a single worksheet so the result needs to broken into multiple sheets.

Help with converting multiple columns into rows

Punxsu

New Member

Punxsu

New Member

Peter_SSs

MrExcel MVP, Moderator

Punxsu

New Member

Fazza

MrExcel MVP

Punxsu

New Member

Fazza

MrExcel MVP

Peter_SSs

MrExcel MVP, Moderator

Fazza

MrExcel MVP

Fazza

MrExcel MVP

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Share this page

Help with converting multiple columns into rows

New Member

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP, Moderator

MrExcel MVP

MrExcel MVP

MrExcel MVP, Moderator

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock