Transposing n rows into columns

M

molinal

New Member
Joined
Jun 9, 2019
Messages
2
Hi everyone. I am trying to make a chart with demographic data, and I need to rearrange the data. I've been searching for a thread about this query, but all I found refer to Multiple Rows to Single Columns, and resulting columns on top of each other. My dataset is a table of 103x496 and I cannot do it with pivot tables.
I need help transposing Several Rows into Multiple Columns Adjacent to each other and I would prefer a formula, over VBA but all help is appreciated. Here is a small example of what I have.:confused:
<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}tr {mso-height-source:auto;}col {mso-width-source:auto;}br {mso-data-placement:same-cell;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}--></style>
YEARRACE0years1years2years
2016Whitec2d2e2
2016Blackc3d3e3
2016Nativec4d4e4
2017Whitec5d5e5
2017Blackc6d6e6
2017Nativec7d7e7
2018Whitec8d8e8
2018Blackc9d9e9
2018Nativec10d10e10
2019Whitec11d11e11
2019Blackc12d12e12
2019Nativec13d13e13
2020Whitec14d14e14
2020Blackc15d15e15
2020Nativec16d16e16

<tbody>
</tbody>


This is what I need:p

<style><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}@page {margin:.75in .7in .75in .7in; mso-header-margin:.3in; mso-footer-margin:.3in;}tr {mso-height-source:auto;}col {mso-width-source:auto;}br {mso-data-placement:same-cell;}td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;}.xl65 {text-align:center;}--></style>
0years1years2years
WhiteBlackNativeWhiteBlackNativeWhiteBlackNative
2016c2c3c4d2d3d4e2e3e4
2017c5c6c7d5d6d7e5e6e7
2018c8c9c10d8d9d10e8e9e10
2019c11c12c13d11d12d13e11e12e13
2020c14c15c16d14d15d16e14e15e16

<tbody>
</tbody>
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
YEARRACE0years1years2years20160years1years2years
2016Whitec2d2e2WhiteBlackNativeWhiteBlackNativeWhiteBlackNative
2016Blackc3d3e3c2c3c4d2d3d4e2e3e4
2016Nativec4d4e420170years1years2years
2017Whitec5d5e5WhiteBlackNativeWhiteBlackNativeWhiteBlackNative
2017Blackc6d6e6c5c6c7d5d6d7e5e6e7
2017Nativec7d7e720180years1years2years
2018Whitec8d8e8WhiteBlackNativeWhiteBlackNativeWhiteBlackNative
2018Blackc9d9e9c8c9c10d8d9d10e8e9e10
2018Nativec10d10e1020190years1years2years
2019Whitec11d11e11WhiteBlackNativeWhiteBlackNativeWhiteBlackNative
2019Blackc12d12e12c11c12c13d11d12d13e11e12e13
2019Nativec13d13e1320200years1years2years
2020Whitec14d14e14WhiteBlackNativeWhiteBlackNativeWhiteBlackNative
2020Blackc15d15e15c14c15c16d14d15d16e14e15e16
2020Nativec16d16e16
all done with formulas in rows 1 to 3 which are copied down
F2 (2016)=a2
G2 (0years)=C$1
J2 (1years)=d$1
M2 (2years)=e$1
G2 (white)=B2
H2 (black)=B3repeated for colums J to O
I2 (native)=B4
G3 (c2)=c2
H3 (c3)=c3
I3 (c4)=c4
J3 (d2)=d2and so on
highlight F1:O3
copy
highlight F4
pasteto bottom of table

<colgroup><col span="5"><col><col span="10"></colgroup><tbody>
</tbody>
 
Upvote 0
one way with PowerQuery (Get&Transform)

YEARRACE0years1years2yearsYEARWhite 0yearsBlack 0yearsNative 0yearsWhite 1yearsBlack 1yearsNative 1yearsWhite 2yearsBlack 2yearsNative 2years
2016​
Whitec2d2e2
2016​
c2c3c4d2d3d4e2e3e4
2016​
Blackc3d3e3
2017​
c5c6c7d5d6d7e5e6e7
2016​
Nativec4d4e4
2018​
c8c9c10d8d9d10e8e9e10
2017​
Whitec5d5e5
2019​
c11c12c13d11d12d13e11e12e13
2017​
Blackc6d6e6
2020​
c14c15c16d14d15d16e14e15e16
2017​
Nativec7d7e7
2018​
Whitec8d8e8
2018​
Blackc9d9e9
2018​
Nativec10d10e10
2019​
Whitec11d11e11
2019​
Blackc12d12e12
2019​
Nativec13d13e13
2020​
Whitec14d14e14
2020​
Blackc15d15e15
2020​
Nativec16d16e16

Code: 
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UnPivot = Table.Unpivot(Source, {"0years", "1years", "2years"}, "Attribute", "Value"),
    Sort = Table.Sort(UnPivot,{{"Attribute", Order.Ascending}, {"RACE", Order.Descending}}),
    Merge = Table.CombineColumns(Sort,{"RACE", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    Pivot = Table.Pivot(Merge, List.Distinct(Merge[Merged]), "Merged", "Value")
in
    Pivot[/SIZE]
 
Last edited:
Upvote 0
Oldbrewer I see the variables but not the formula
oldbrewer said:
YEARRACE0years1years2years20160years1years2years
2016Whitec2d2e2WhiteBlackNativeWhiteBlackNativeWhiteBlackNative
2016Blackc3d3e3c2c3c4d2d3d4e2e3e4
2016Nativec4d4e420170years1years2years
2017Whitec5d5e5WhiteBlackNativeWhiteBlackNativeWhiteBlackNative
2017Blackc6d6e6c5c6c7d5d6d7e5e6e7
2017Nativec7d7e720180years1years2years
2018Whitec8d8e8WhiteBlackNativeWhiteBlackNativeWhiteBlackNative
2018Blackc9d9e9c8c9c10d8d9d10e8e9e10
2018Nativec10d10e1020190years1years2years
2019Whitec11d11e11WhiteBlackNativeWhiteBlackNativeWhiteBlackNative
2019Blackc12d12e12c11c12c13d11d12d13e11e12e13
2019Nativec13d13e1320200years1years2years
2020Whitec14d14e14WhiteBlackNativeWhiteBlackNativeWhiteBlackNative
2020Blackc15d15e15c14c15c16d14d15d16e14e15e16
2020Nativec16d16e16
all done with formulas in rows 1 to 3 which are copied down
F2 (2016)=a2
G2 (0years)=C$1
J2 (1years)=d$1
M2 (2years)=e$1
G2 (white)=B2
H2 (black)=B3repeated for colums J to O
I2 (native)=B4
G3 (c2)=c2
H3 (c3)=c3
I3 (c4)=c4
J3 (d2)=d2and so on
highlight F1:O3
copy
highlight F4
pasteto bottom of table

<tbody>
</tbody>
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
IF Condition help (I believe)
Replies
3
Views
1K
Joe4
Joe4
B
  • Question
Appending a Value to Each Element of a Range Array
Replies
7
Views
2K
Peter_SSs
Peter_SSs
M
  • Question
Multiple table array
Replies
5
Views
804
Fluff
Fluff
M
  • Question
"Sort by" Formula
2
Replies
11
Views
2K
Marcelo Branco
M
Y
  • Question
Format Today() in a string
Replies
1
Views
689
Rick Rothstein
Rick Rothstein

Forum statistics

Threads
1,214,899
Messages
6,122,155
Members
449,068
Latest member
shiz11713

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top