How to Rotate Rows to Columns with Large Data Set

[Saacko]

Hello everyone,

I have a fairly large amount of data that in columns, but I would like to convert them to rows. I know simply transposing seems like the obvious answer, but it does not work for this data set. Please see below:

Buy On Paper - RevD.xlsx
	A	B	C	D	E	F	G	H	I
20	Packages and Durations	Project Set-Up	Material Contract 5	Material Contract 4	Material Contract 3	Material Contract 2	Material Contract 1	Short Form Material Contract	these go for hundred of columns….
21	Activity N, in hours	0	200	280	380	520	640	720
22	Activity I, in hours	100	340	320	300	280	260	260
23	Activity Q, in hours	100	280	260	240	220	200	200
24	Activity D, in hours	0	2920	1920	1600	1440	1280	960
25
26	How I want it:
27	Project Set-Up -Activity N, in hours	0
28	Project Set-Up -Activity I, in hours	100
29	Project Set-Up -Activity Q, in hours	100
30	Project Set-Up -Activity D, in hours	0
31	Material Contract 5 -Activity N, in hours	200
32	Material Contract 5 -Activity I, in hours	340
33	Material Contract 5 -Activity Q, in hours	280
34	Material Contract 5 -Activity D, in hours	2920
35	Material Contract 4 -Activity N, in hours	280
36	Material Contract 4 -Activity I, in hours	320
37	Material Contract 4 -Activity Q, in hours	260
38	Material Contract 4 -Activity D, in hours	1920
39	Material Contract 3 -Activity N, in hours	380
40	Material Contract 3 -Activity I, in hours	300
41	Material Contract 3 -Activity Q, in hours	240
42	Material Contract 3 -Activity D, in hours	1600
43	Material Contract 2 -Activity N, in hours	520
44	Material Contract 2 -Activity I, in hours	280
45	Material Contract 2 -Activity Q, in hours	220
46	Material Contract 2 -Activity D, in hours	1440
47	etc.
48	etc.
49	etc.
50	etc.
Transposed Test Data

So first, I am trying to have my Package names Concat with my Activity names. Then, I need those names to match up with my durations. It's important that my activities remain in the order of N, then I, then Q, and then D. I am able to get all my names to Concat in a row format and then I transpose them vertically. I then cut and paste the "I" sections under the "N", then "Q" under the "I", and then "D" under the "Q. "That works well, but I can't seem to sort them to get them in the right order after that. I want the packages in alphabetical order just like above and then sorted by N,I,Q,D. Once I get my names in the right order as shown above, the next challenge is getting the durations to match up I have tried lookups and transposing but nothing really works. I need my data in one column as shown in the spreadsheet.

Recap:

1. I need help taking this data and making it vertical into one column.
2. I can get my names to concat in a row format and then transpose to vertical, but I can't sort them in the right order. I want alphabetical order, and then by N,I,Q,D.
3. Once I get my names in the right order, I need to get the durations to match up.

Please let me know if this will be possible to achieve, thank you all.

 

[alansidman]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Packages and Durations"}, "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Attribute", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Packages and Durations"})
in
    #"Removed Columns"

Book5
	D	E
8	Attribute	Value
9	Material Contract 1	1280
10	Material Contract 1	640
11	Material Contract 1	200
12	Material Contract 1	260
13	Material Contract 2	280
14	Material Contract 2	220
15	Material Contract 2	1440
16	Material Contract 2	520
17	Material Contract 3	240
18	Material Contract 3	1600
19	Material Contract 3	300
20	Material Contract 3	380
21	Material Contract 4	280
22	Material Contract 4	260
23	Material Contract 4	320
24	Material Contract 4	1920
25	Material Contract 5	200
26	Material Contract 5	2920
27	Material Contract 5	340
28	Material Contract 5	280
29	Project Set-Up	0
30	Project Set-Up	100
31	Project Set-Up	100
32	Project Set-Up	0
33	Short Form Material Contract	960
34	Short Form Material Contract	720
35	Short Form Material Contract	260
36	Short Form Material Contract	200
Sheet1

 

[Saacko]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Packages and Durations"}, "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Other Columns",{{"Attribute", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Packages and Durations"})
in
    #"Removed Columns"

Book5
	D	E
8	Attribute	Value
9	Material Contract 1	1280
10	Material Contract 1	640
11	Material Contract 1	200
12	Material Contract 1	260
13	Material Contract 2	280
14	Material Contract 2	220
15	Material Contract 2	1440
16	Material Contract 2	520
17	Material Contract 3	240
18	Material Contract 3	1600
19	Material Contract 3	300
20	Material Contract 3	380
21	Material Contract 4	280
22	Material Contract 4	260
23	Material Contract 4	320
24	Material Contract 4	1920
25	Material Contract 5	200
26	Material Contract 5	2920
27	Material Contract 5	340
28	Material Contract 5	280
29	Project Set-Up	0
30	Project Set-Up	100
31	Project Set-Up	100
32	Project Set-Up	0
33	Short Form Material Contract	960
34	Short Form Material Contract	720
35	Short Form Material Contract	260
36	Short Form Material Contract	200
Sheet1

Thank you for setting this code up for me, I will admit I am fairly new to Power Queries. I am trying to enter it, but it keeps giving me syntax errors. Here's what I see when I try and enter the formula:

There is just a ton of columns and I am not sure where I should paste your formula. Please excuse me if this is dumb question, like I said I am new to this.

Do you think you could help me with how to enter it?

I would greatly appreciate and am already thankful for the help you have provided.

Thanks,

 

[alansidman]

this should help

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[Saacko]

this should help

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Thank you for sending this, I was able to watch a couple videos and figure out how to run the code you gave me. However, it's still not lining up my data quite the way I want it to. The code got my name and duration columns to become vertical. But I don't know which activity is which and which duration goes with each activity. It would seem I can either get my names to show up correctly or I can get the durations but not know which activity they are tied to.

Here is how my data actually looks and the highlighted is how I want to have it:

Buy On Paper - RevD - Copy 2.xlsm
	A	B	C	D	E	F	G	H	I
1	Packages and Durations	Project Set-Up	Column3	Column4	Column5	Material Contract 5	Column7	Column8	Column9
2	NTP to IFR, Hours	Project Set-Up - NTP to IFR, Hours	1. PSU - Project	0	FS	Material Contract 5 - NTP to IFR, Hours	1. MC5 - Materia	200	FS
3	IFR to Quote (Proposal Received), Hours	Project Set-Up - IFR to Quote (Proposal Received), Hours	2. PSU - Project	100	FS	Material Contract 5 - IFR to Quote (Proposal Received), Hours	2. MC5 - Materia	340	FS
4	Quote (Proposal Received) to Award, Hours	Project Set-Up - Quote (Proposal Received) to Award, Hours	3. PSU - Project	100	FS	Material Contract 5 - Quote (Proposal Received) to Award, Hours	3. MC5 - Materia	280	FS
5	Award to Delivery or Completion, Hours	Project Set-Up - Award to Delivery or Completion, Hours	4. PSU - Project	0	FS	Material Contract 5 - Award to Delivery or Completion, Hours	4. MC5 - Materia	2920	FS
6
7
8
9	Package name	Duration	Logic	Abbreviation
10	Project Set-Up - NTP to IFR, Hours	0	FS	1. PSU - Project
11	Project Set-Up - IFR to Quote (Proposal Received), Hours	100	FS	2. PSU - Project
12	Project Set-Up - Quote (Proposal Received) to Award, Hours	100	FS	3. PSU - Project
13	Project Set-Up - Award to Delivery or Completion, Hours	0	FS	4. PSU - Project
14	Material Contract 5 - NTP to IFR, Hours	200	FS	1. MC5 - Materia
15	Material Contract 5 - IFR to Quote (Proposal Received), Hours	340	FS	2. MC5 - Materia
16	Material Contract 5 - Quote (Proposal Received) to Award, Hours	280	FS	3. MC5 - Materia
17	Material Contract 5 - Award to Delivery or Completion, Hours	2920	FS	4. MC5 - Materia
Table14

Please keep in mind the only column labels are the names of the packages. If you want me to have columns names for each column to tie them to the package, I would have to do some extra work to make it look like this:

Buy On Paper - RevD - Copy 2.xlsm
	A	B	C	D	E	F	G	H	I
19	Packages and Durations	Project Set-Up	Project Set-Up	Project Set-Up	Project Set-Up	Material Contract 5	Material Contract 5	Material Contract 5	Material Contract 5
20	NTP to IFR, Hours	Project Set-Up - NTP to IFR, Hours	1. PSU - Project	0	FS	Material Contract 5 - NTP to IFR, Hours	1. MC5 - Materia	200	FS
21	IFR to Quote (Proposal Received), Hours	Project Set-Up - IFR to Quote (Proposal Received), Hours	2. PSU - Project	100	FS	Material Contract 5 - IFR to Quote (Proposal Received), Hours	2. MC5 - Materia	340	FS
22	Quote (Proposal Received) to Award, Hours	Project Set-Up - Quote (Proposal Received) to Award, Hours	3. PSU - Project	100	FS	Material Contract 5 - Quote (Proposal Received) to Award, Hours	3. MC5 - Materia	280	FS
23	Award to Delivery or Completion, Hours	Project Set-Up - Award to Delivery or Completion, Hours	4. PSU - Project	0	FS	Material Contract 5 - Award to Delivery or Completion, Hours	4. MC5 - Materia	2920	FS
Table14

However, if I do that, my data doesn't come out right either:

I don't know which activity those durations are tied to.


I hope there is a way you can help me with this, I'm sorry if this isn't making any sense.

I really appreciate all the help you have given me so far.

- Scout

 

[alansidman]

Based upon your latest proposed solution
Three steps

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Packages and Durations", "Project Set-Up", "Column3", "Column4", "Column5"})
in
    #"Removed Other Columns"

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Material Contract 5", "Column7", "Column8", "Column9"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Material Contract 5", "Project Set-Up"}, {"Column7", "Column3"}, {"Column8", "Column4"}, {"Column9", "Column5"}})
in
    #"Renamed Columns"

Append the two queries as new

let
    Source = Table.Combine({Table1, #"Table1 (2)"}),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Packages and Durations"})
in
    #"Removed Columns"

Book2
	A	B	C	D
1	Project Set-Up	Column3	Column4	Column5
2	Project Set-Up - NTP to IFR, Hours	1. PSU - Project	0	FS
3	Project Set-Up - IFR to Quote (Proposal Received), Hours	2. PSU - Project	100	FS
4	Project Set-Up - Quote (Proposal Received) to Award, Hours	3. PSU - Project	100	FS
5	Project Set-Up - Award to Delivery or Completion, Hours	4. PSU - Project	0	FS
6	Material Contract 5 - NTP to IFR, Hours	1. MC5 - Materia	200	FS
7	Material Contract 5 - IFR to Quote (Proposal Received), Hours	2. MC5 - Materia	340	FS
8	Material Contract 5 - Quote (Proposal Received) to Award, Hours	3. MC5 - Materia	280	FS
9	Material Contract 5 - Award to Delivery or Completion, Hours	4. MC5 - Materia	2920	FS
Append1

 

[kevin9999]

If VBA is an option you'd consider, please try the following on a copy of your workbook. Assumes your layout is exactly as you show in post #5, and that the sheet is called Table14, the following code:

Option Explicit
Sub Saacko()
    Dim ws As Worksheet, LCol As Long
    Set ws = Worksheets("Table14")          '<-- *** Change sheet name to suit ***
    LCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
    Dim arrIn, arrOut, i As Long, j As Long, k As Long, m As Long
    arrIn = ws.Range(ws.Cells(2, 2), ws.Cells(5, LCol))
    ReDim arrOut(1 To LCol - 1, 1 To 4)
    
    j = 1: k = 1
    For i = 1 To UBound(arrOut, 1)
        arrOut(i, 1) = arrIn(j, k)
        arrOut(i, 2) = arrIn(j, k + 2)
        arrOut(i, 3) = arrIn(j, k + 3)
        arrOut(i, 4) = arrIn(j, k + 1)
        m = m + 1
        If m = 4 Then
            k = k + 4
            m = 0
        End If
        If j = 4 Then j = 1 Else j = j + 1
    Next i
    ws.Range("A10").Resize(UBound(arrOut, 1), 4).Value = arrOut
End Sub

Turns this:

move data.xlsm
	A	B	C	D	E	F	G	H	I
1	Packages and Durations	Project Set-Up	Column3	Column4	Column5	Material Contract 5	Column7	Column8	Column9
2	NTP to IFR, Hours	Project Set-Up - NTP to IFR, Hours	1. PSU - Project	0	FS	Material Contract 5 - NTP to IFR, Hours	1. MC5 - Materia	200	FS
3	IFR to Quote (Proposal Received), Hours	Project Set-Up - IFR to Quote (Proposal Received), Hours	2. PSU - Project	100	FS	Material Contract 5 - IFR to Quote (Proposal Received), Hours	2. MC5 - Materia	340	FS
4	Quote (Proposal Received) to Award, Hours	Project Set-Up - Quote (Proposal Received) to Award, Hours	3. PSU - Project	100	FS	Material Contract 5 - Quote (Proposal Received) to Award, Hours	3. MC5 - Materia	280	FS
5	Award to Delivery or Completion, Hours	Project Set-Up - Award to Delivery or Completion, Hours	4. PSU - Project	0	FS	Material Contract 5 - Award to Delivery or Completion, Hours	4. MC5 - Materia	2920	FS
6
7
8
9	Package name	Duration	Logic	Abbreviation
10
Table14

Into this:

move data.xlsm
	A	B	C	D	E	F	G	H	I
1	Packages and Durations	Project Set-Up	Column3	Column4	Column5	Material Contract 5	Column7	Column8	Column9
2	NTP to IFR, Hours	Project Set-Up - NTP to IFR, Hours	1. PSU - Project	0	FS	Material Contract 5 - NTP to IFR, Hours	1. MC5 - Materia	200	FS
3	IFR to Quote (Proposal Received), Hours	Project Set-Up - IFR to Quote (Proposal Received), Hours	2. PSU - Project	100	FS	Material Contract 5 - IFR to Quote (Proposal Received), Hours	2. MC5 - Materia	340	FS
4	Quote (Proposal Received) to Award, Hours	Project Set-Up - Quote (Proposal Received) to Award, Hours	3. PSU - Project	100	FS	Material Contract 5 - Quote (Proposal Received) to Award, Hours	3. MC5 - Materia	280	FS
5	Award to Delivery or Completion, Hours	Project Set-Up - Award to Delivery or Completion, Hours	4. PSU - Project	0	FS	Material Contract 5 - Award to Delivery or Completion, Hours	4. MC5 - Materia	2920	FS
6
7
8
9	Package name	Duration	Logic	Abbreviation
10	Project Set-Up - NTP to IFR, Hours	0	FS	1. PSU - Project
11	Project Set-Up - IFR to Quote (Proposal Received), Hours	100	FS	2. PSU - Project
12	Project Set-Up - Quote (Proposal Received) to Award, Hours	100	FS	3. PSU - Project
13	Project Set-Up - Award to Delivery or Completion, Hours	0	FS	4. PSU - Project
14	Material Contract 5 - NTP to IFR, Hours	200	FS	1. MC5 - Materia
15	Material Contract 5 - IFR to Quote (Proposal Received), Hours	340	FS	2. MC5 - Materia
16	Material Contract 5 - Quote (Proposal Received) to Award, Hours	280	FS	3. MC5 - Materia
17	Material Contract 5 - Award to Delivery or Completion, Hours	2920	FS	4. MC5 - Materia
18
Table14

 

[Saacko]

Kevin,
I tried this and it worked on all of my data. It is perfect! I can't thank you enough for helping me with this. I have been trying to find this answer for weeks now and the VBA was much simpler than the power query.

 

[kevin9999]

Happy to help, and thanks for the feedback