Transposing n rows into columns

molinal

New Member
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>
 

oldbrewer

Board Regular
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>
 

sandy666

Well-known Member
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:

molinal

New Member
Oldbrewer I see the variables but not the formula
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>
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top