Turning 'Horizontal' data into 'vertical' consolidated table

D

ddander54

Board Regular
Joined
Oct 18, 2012
Messages
97
Hello, I often get a workbook with many sheets of data with multiple columns on the left, dates across the top and numbers at the intersection of each row/column. I then need to put it into a consolidated 'vertical' table in order to create my reports. Most of the time I'm using the Copy/Paste, fill down, repeat, method to create the table, but I know there has to be a better way...just don't know how to get there. Once I get the table structure created, I can usually use INDEX, MATCH to insert the data piece (numbers) into the table.
The Column B (Type) usually is 3-5 rows and the Row 5 'YYYY-MM' can be any number of months (1-12).
Sample data:
Book1
ABCDEFGHI
5SheetnameType2021-062021-072021-082021-092021-102021-112021-12
6Dept1Billable1234113512361126123710561003
7Capital9998951001935997856875
8Expense825815899865856888879
9O/T/M201198225235205188185
10P/H250235245256245300305
11
12SheetnameType2021-062021-072021-082021-092021-102021-112021-12
13Dept2Billable1234113512361126123710561003
14Capital9998951001935997856875
15Expense825815899865856888879
16O/T/M201198225235205188185
17P/H250235245256245300305
Sheet1


What I need to end up with is a consolidated table like below...
Book1
KLMNO
3Consolidated Table
4DeptText YYYY-MMDateTypeHours
5Dept12021-066/1/2021Billable1234
6Dept12021-066/1/2021Capital999
7Dept12021-066/1/2021Expense825
8Dept12021-066/1/2021O/T/M201
9Dept12021-066/1/2021P/H250
10Dept12021-077/1/2021Billable1135
11Dept12021-077/1/2021Capital895
12Dept12021-077/1/2021Expense815
13Dept12021-077/1/2021O/T/M198
14Dept12021-077/1/2021P/H235
15etc….etc….etc….etc….etc….
16Dept2
17etc….etc….etc….etc….etc….
Sheet1
Cell Formulas
RangeFormula
O5:O14O5=INDEX($C$6:$I$10,MATCH($N5,$B$6:$B$10,0),MATCH($L5,$C$5:$I$5,0))


What is a more efficient way of creating the consolidated table, other than, Copy/Paste, fill down, repeat?

TIA,
Don
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
Hi, try this..
VBA Code: 
Sub test()
    Dim yazSat&, bas&, i&, ii&
    Range("K5:O" & Rows.Count).ClearContents

    yazSat = 5

    bas = 5
    GoSub islem

    bas = 12
    GoSub islem

    Exit Sub
islem:
    For i = 3 To 9
        bl = Split(Cells(bas, i).Value, "-")
        For ii = bas + 1 To bas + 5
            Cells(yazSat, "K").Value = Cells(bas + 1, 1).Value
            Cells(yazSat, "L").Value = Cells(bas, i).Value
            Cells(yazSat, "M").Value = DateSerial(bl(0), bl(1), 1)
            Cells(yazSat, "N").Value = Cells(ii, "B").Value
            Cells(yazSat, "O").Value = Cells(ii, i).Value
            yazSat = yazSat + 1
        Next ii
    Next i
    Return

End Sub
 
Upvote 0
A way using Power Query.

Book1
ABCDEFGHIJKLMNO
1SheetnameType2021-062021-072021-082021-092021-102021-112021-12SheetnameTypeText YYYY-MMDateHours
2Dept1Billable1234113512361126123710561003Dept1Billable2021-066/1/20211234
3Capital9998951001935997856875Dept1Billable2021-077/1/20211135
4Expense825815899865856888879Dept1Billable2021-088/1/20211236
5O/T/M201198225235205188185Dept1Billable2021-099/1/20211126
6P/H250235245256245300305Dept1Billable2021-1010/1/20211237
7Dept1Billable2021-1111/1/20211056
8SheetnameType2021-062021-072021-082021-092021-102021-112021-12Dept1Billable2021-1212/1/20211003
9Dept2Billable1234113512361126123710561003Dept1Capital2021-066/1/2021999
10Capital9998951001935997856875Dept1Capital2021-077/1/2021895
11Expense825815899865856888879Dept1Capital2021-088/1/20211001
12O/T/M201198225235205188185Dept1Capital2021-099/1/2021935
13P/H250235245256245300305Dept1Capital2021-1010/1/2021997
14Dept1Capital2021-1111/1/2021856
15Dept1Capital2021-1212/1/2021875
16Dept1Expense2021-066/1/2021825
17Dept1Expense2021-077/1/2021815
18Dept1Expense2021-088/1/2021899
19Dept1Expense2021-099/1/2021865
20Dept1Expense2021-1010/1/2021856
21Dept1Expense2021-1111/1/2021888
22Dept1Expense2021-1212/1/2021879
23Dept1O/T/M2021-066/1/2021201
24Dept1O/T/M2021-077/1/2021198
25Dept1O/T/M2021-088/1/2021225
26Dept1O/T/M2021-099/1/2021235
27Dept1O/T/M2021-1010/1/2021205
28Dept1O/T/M2021-1111/1/2021188
29Dept1O/T/M2021-1212/1/2021185
30Dept1P/H2021-066/1/2021250
31Dept1P/H2021-077/1/2021235
32Dept1P/H2021-088/1/2021245
33Dept1P/H2021-099/1/2021256
34Dept1P/H2021-1010/1/2021245
35Dept1P/H2021-1111/1/2021300
36Dept1P/H2021-1212/1/2021305
37Dept2Billable2021-066/1/20211234
38Dept2Billable2021-077/1/20211135
39Dept2Billable2021-088/1/20211236
40Dept2Billable2021-099/1/20211126
41Dept2Billable2021-1010/1/20211237
42Dept2Billable2021-1111/1/20211056
43Dept2Billable2021-1212/1/20211003
44Dept2Capital2021-066/1/2021999
45Dept2Capital2021-077/1/2021895
46Dept2Capital2021-088/1/20211001
47Dept2Capital2021-099/1/2021935
48Dept2Capital2021-1010/1/2021997
49Dept2Capital2021-1111/1/2021856
50Dept2Capital2021-1212/1/2021875
51Dept2Expense2021-066/1/2021825
52Dept2Expense2021-077/1/2021815
53Dept2Expense2021-088/1/2021899
54Dept2Expense2021-099/1/2021865
55Dept2Expense2021-1010/1/2021856
56Dept2Expense2021-1111/1/2021888
57Dept2Expense2021-1212/1/2021879
58Dept2O/T/M2021-066/1/2021201
59Dept2O/T/M2021-077/1/2021198
60Dept2O/T/M2021-088/1/2021225
61Dept2O/T/M2021-099/1/2021235
62Dept2O/T/M2021-1010/1/2021205
63Dept2O/T/M2021-1111/1/2021188
64Dept2O/T/M2021-1212/1/2021185
65Dept2P/H2021-066/1/2021250
66Dept2P/H2021-077/1/2021235
67Dept2P/H2021-088/1/2021245
68Dept2P/H2021-099/1/2021256
69Dept2P/H2021-1010/1/2021245
70Dept2P/H2021-1111/1/2021300
71Dept2P/H2021-1212/1/2021305
Sheet4


Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Filter = Table.SelectRows(Source, each ([Type] <> null and [Type] <> "Type")),
    FillDown = Table.FillDown(Filter,{"Sheetname"}),
    UOC = Table.UnpivotOtherColumns(FillDown, {"Sheetname", "Type"}, "Attribute", "Value"),
    Rename = Table.RenameColumns(UOC,{{"Attribute", "Text YYYY-MM"}, {"Value", "Hours"}}),
    toDate = Table.AddColumn(Rename, "Date", each 
        let 
            x = List.Transform(Text.Split(_[#"Text YYYY-MM"],"-"),Number.From),
            d = #date(x{0},x{1},1)
        in
            d
        ),
    Reorder = Table.ReorderColumns(toDate,{"Sheetname", "Type", "Text YYYY-MM", "Date", "Hours"}),
    Types = Table.TransformColumnTypes(Reorder,{{"Sheetname", type text}, {"Type", type text}, {"Text YYYY-MM", type text}, {"Date", type date}, {"Hours", Int64.Type}})
in
    Types
 
Upvote 0
Thank you both for your solutions. What if my data is actually on separate sheets, ie, Dept1 is on one sheet and Dept2 is on another sheet within the same workbook?
 
Upvote 0
Hi,
The data on the Dept1 and Dept2 pages will be transferred to the Report Page by editing from the 2nd line.

VBA Code: 
Sub test()
    Dim sheetName$, yazSat&
    Dim i&, ii&
    Dim s1 As Worksheet, sR As Worksheet

    Set sR = Sheets("Report")

    sR.Range("A2:E" & Rows.Count).ClearContents

    yazSat = 2

    sheetName = "Dept1"
    GoSub islem

    sheetName = "Dept2"
    GoSub islem

    Exit Sub
islem:
    Set s1 = Sheets(sheetName)
    For i = 2 To s1.Cells(1, Columns.Count).End(1).Column
        bl = Split(s1.Cells(1, i).Value, "-")
        For ii = 2 To s1.Cells(Rows.Count, 1).End(3).Row
            sR.Cells(yazSat, 1).Value = sheetName
            sR.Cells(yazSat, 2).Value = s1.Cells(1, i).Value
            sR.Cells(yazSat, 3).Value = DateSerial(bl(0), bl(1), 1)
            sR.Cells(yazSat, 4).Value = s1.Cells(ii, "A").Value
            sR.Cells(yazSat, 5).Value = s1.Cells(ii, i).Value
            yazSat = yazSat + 1
        Next ii
    Next i
    Return

End Sub
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

G
  • Question
Macro to consolidate multiple worksheets into Master based on cell data?
Replies
1
Views
309
bobaol
B
R
  • Solved
Scoring table
Replies
9
Views
127
Rock5150
R
Yecart77
  • Solved
Data consolidation within a table
Replies
4
Views
172
Yecart77
Yecart77
M
  • Question
How do I turn a contact list on one sheet into contact "cards" on another.
Replies
6
Views
254
mjtrifillis
M
N
  • Question
consolidate pivot tables and have 1 that reports all
Replies
0
Views
264
noel1
N

Forum statistics

Threads
1,215,634
Messages
6,125,934
Members
449,275
Latest member
jacob_mcbride

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