Data from multiple Sheets to be included in a single Main sheet, plus data added later to be updated in Main Sheet

[Sumanmathew]

Sir,
I have 3 sheets Sheet A, Sheet B and Sheet C and a |Main sheet.
I need the data in these 3 sheets to appear in the main sheet one below the other.
Again whenever I add new data in any of the individual sheet, it should be added in the Main sheet as well.
Like if a new item is added at the end of Sheet C, that should also appear in the Main Sheet.
Please help me solve this.

multiple sheet.xlsx
	A	B	C	D
4	Sl.	Item	Amount
5	1	A111	10
6	2	A222	20
7	3	A333	30
8	4	A444	40
9	5	A555	50
10	6	A666	60
11	7	A777	70
12	8	A888	80
13	9	A999	90
14	10	A1110	100
15
16
Sheet A

multiple sheet.xlsx
	A	B	C	D
4	Sl.	Item	Amount
5	101	B111	100
6	102	B222	200
7	103	B333	300
8	104	B444	400
9	105	B555	500
10	106	B666	600
11	107	B777	700
12	108	B888	800
13	109	B999	900
14	110	B1110	1000
15
Sheet B

multiple sheet.xlsx
	A	B	C	D
4	Sl.	Item	Amount
5	1001	C111	1000
6	1002	C222	2000
7	1003	C333	3000
8	1004	C444	4000
9	1005	C555	5000
10	1006	C666	6000
11	1007	C777	7000
12	1008	C888	8000
13	1009	C999	9000
14	1010	C1110	10000
15
Sheet C

multiple sheet.xlsx
	A	B	C	D
3
4	Sl.	Item	Amount
5
6
7
8
9
10
11
12
13
14
15		Subtotal
16	Sl.	Item	Amount
17
18
19
20
21
22
23
24
25
26
27		Subtotal
28	Sl.	Item	Amount
29
30
31
32
33
34
35
36
37
38
39		Subtotal
40	Grand Total
41
Main

 

[awoohaw]

Please see if Power Query is available in your installation of 2013 Excel. It should be there.
You will be able to do what you do quite easily.

 

[awoohaw]

Rethinking my statement. I don't think you can do subtotals inside of tables. So, back to the drawing board.

Can you have another summary sheet with a totals recap, versus a complete list with subtotals?

 

[Sumanmathew]

Rethinking my statement. I don't think you can do subtotals inside of tables. So, back to the drawing board.

Can you have another summary sheet with a totals recap, versus a complete list with subtotals?

multiple sheet.xlsx
	A	B	C	D
3
4	Sl.	Item	Amount
5
6
7
8
9
10
11
12
13
14
15		Subtotal	550
16	Sl.	Item	Amount
17
18
19
20
21
22
23
24
25
26
27		Subtotal	5500
28	Sl.	Item	Amount
29
30
31
32
33
34
35
36
37
38
39		Subtotal	55000
40	Grand Total		61050
41
Main

Cell Formulas
Range	Formula
C15	C15	='Sheet A'!C15
C27	C27	='Sheet B'!C15
C39	C39	='Sheet C'!C15
C40	C40	=C15+C27+C39

Sir I have added the subtotals, is this what you meant?

 

[awoohaw]

not really. I mean, if you use power query you will be using tables.
the combined appended power query result would be one big table that is easily refreshed when new data is keyed into the sub sheets.
but you cannot have subtotals inside of tables.

My question was ... do you need to see the detail above each subtotal?

 

[Sumanmathew]

not really. I mean, if you use power query you will be using tables.
the combined appended power query result would be one big table that is easily refreshed when new data is keyed into the sub sheets.
but you cannot have subtotals inside of tables.

My question was ... do you need to see the detail above each subtotal?

Yes, all the rows in each of the sub sheets should come in the main sheet.
Again, whenever a new item is included in the sub sheets it should be upgraded in the Main sheet and the subtotal should also be upgraded.

 

[jdellasala]

Using a Power Query function from this video, first bring in the three tables like this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddedGroup = Table.AddColumn(Source, "Group", each Text.Start([Item],1), type text),
    ReorderedAmount = Table.ReorderColumns(AddedGroup,{"Sl.", "Item", "Group", "Amount"})
in
    ReorderedAmount

Do the same for Table2 and Table3.
Now open the Power Query editor and start a new blank query (I have no idea where that is for 2013 - in 365 it's Data -> Get Data -> From Other Sources -> Blank Query) named fxRowAndColTotal and enter this code:

let
    Source = ( InputTable as table, InitialColumnCount as number, optional AddColumnTotal as logical ) =>

let
    Cols = List.Skip(
                Table.ColumnNames(InputTable),
                InitialColumnCount ),

    InitialCols = List.FirstN(
                Table.ColumnNames(InputTable),
                InitialColumnCount ),

    TotalLabelAdded =  List.Accumulate(
                InitialCols,
                [],
                (s,c)=> Record.AddField(s, c, "Total") ),

    TotalRecord = List.Accumulate(
            Cols,
            TotalLabelAdded,
            (s,c)=> Record.AddField(s, c, List.Sum(Table.Column(InputTable, c))) ),

    RowTotal = Table.InsertRows(
                InputTable,
                Table.RowCount(InputTable), {TotalRecord} ),
  
    ColTotal = Table.AddColumn(
                RowTotal, "Col Total",
                each List.Sum(List.Select(Record.ToList(_), each _ is number)) )
in

    if AddColumnTotal = true then ColTotal else RowTotal
in
    Source

Finally, from the Power Query Home tab, select Append -> Append as New, select the three tables added to mash them all together, and then invoke the function above:

let
    Source = Table.Combine({Table1, Table2, Table3}),
    GroupedAllRows = Table.Group(Source, {"Group"}, {{"AllRows", each _, type table [#"Sl."=nullable number, Item=nullable text, Amount=nullable number, Group=text]}}),
    AddedCustom = Table.AddColumn(GroupedAllRows, "AddTotalToTables", each fxRowAndColTotal([AllRows], 3, true )),
    RemovedOtherColumns = Table.SelectColumns(AddedCustom,{"AddTotalToTables"}),
    ExpandedAddTotalToTables = Table.ExpandTableColumn(RemovedOtherColumns, "AddTotalToTables", {"Sl.", "Item", "Group", "Amount", "Col Total"}, {"Sl.", "Item", "Group", "Amount", "Col Total"}),
    RemovedColTotal = Table.RemoveColumns(ExpandedAddTotalToTables,{"Col Total"}),
    ChangedType = Table.TransformColumnTypes(RemovedColTotal,{{"Sl.", type text}, {"Item", type text}, {"Group", type text}, {"Amount", Int64.Type}}),
    ReplacedTotal = Table.ReplaceValue(ChangedType,"Total","",Replacer.ReplaceText,{"Sl.", "Item"}),
    ReplacedSubtotal = Table.ReplaceValue(ReplacedTotal,"Total","Subtotal",Replacer.ReplaceText,{"Group"})
in
    ReplacedSubtotal

to get this:

CombineSheets.xlsx
	A	B	C	D
1	Sl.	Item	Group	Amount
2	1	A111	A	10
3	2	A222	A	20
4	3	A333	A	30
5	4	A444	A	40
6	5	A555	A	50
7	6	A666	A	60
8	7	A777	A	70
9	8	A888	A	80
10	9	A999	A	90
11	10	A1110	A	100
12			Subtotal	550
13	101	B111	B	100
14	102	B222	B	200
15	103	B333	B	300
16	104	B444	B	400
17	105	B555	B	500
18	106	B666	B	600
19	107	B777	B	700
20	108	B888	B	800
21	109	B999	B	900
22	110	B1110	B	1,000
23			Subtotal	5,500
24	1001	C111	C	1,000
25	1002	C222	C	2,000
26	1003	C333	C	3,000
27	1004	C444	C	4,000
28	1005	C555	C	5,000
29	1006	C666	C	6,000
30	1007	C777	C	7,000
31	1008	C888	C	8,000
32	1009	C999	C	9,000
33	1010	C1110	C	10,000
34			Subtotal	55,000
Report

If you don't want the Group column, use this code:

let
    Source = Table.Combine({Table1, Table2, Table3}),
    GroupedAllRows = Table.Group(Source, {"Group"}, {{"AllRows", each _, type table [#"Sl."=nullable number, Item=nullable text, Amount=nullable number, Group=text]}}),
    AddedCustom = Table.AddColumn(GroupedAllRows, "AddTotalToTables", each fxRowAndColTotal([AllRows], 3, true )),
    RemovedOtherColumns = Table.SelectColumns(AddedCustom,{"AddTotalToTables"}),
    ExpandedAddTotalToTables = Table.ExpandTableColumn(RemovedOtherColumns, "AddTotalToTables", {"Sl.", "Item", "Group", "Amount", "Col Total"}, {"Sl.", "Item", "Group", "Amount", "Col Total"}),
    RemovedColumns = Table.RemoveColumns(ExpandedAddTotalToTables,{"Group", "Col Total"}),
    ChangedType = Table.TransformColumnTypes(RemovedColumns,{{"Sl.", type text}, {"Item", type text}, {"Amount", Int64.Type}}),
    ReplacedTotal = Table.ReplaceValue(ChangedType,"Total","",Replacer.ReplaceText,{"Sl."}),
    ReplacedSubtotal = Table.ReplaceValue(ReplacedTotal,"Total","Subtotal",Replacer.ReplaceText,{"Item"})
in
    ReplacedSubtotal

Note that the columns to be totaled need to be at the end of the table, and the second parameter of the function is the number of columns to skip.
Just to be clear, the function is not mine, I got it from Goodly. Point being a search of YouTube or Google will usually point you in the right direction.
Hope that helps.

 

[mumps]

VBA approach:
Assuming the Main sheet has headers in row 1, try:

Sub UpdateMain()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, wsName As Range, lRow As Long, srcRng As Range, rng As Range, lCol As Long, desWS As Worksheet
    Set desWS = Sheets("Main sheet")
    desWS.UsedRange.Offset(1).ClearContents
    For Each ws In Sheets
        If ws.Name <> "Main sheet" Then
            ws.UsedRange.Offset(1).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

When you add new data in any of the individual sheets, just run the macro again to update the data in the main sheet.

 

[Sumanmathew]

Using a Power Query function from this video, first bring in the three tables like this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddedGroup = Table.AddColumn(Source, "Group", each Text.Start([Item],1), type text),
    ReorderedAmount = Table.ReorderColumns(AddedGroup,{"Sl.", "Item", "Group", "Amount"})
in
    ReorderedAmount

Do the same for Table2 and Table3.
Now open the Power Query editor and start a new blank query (I have no idea where that is for 2013 - in 365 it's Data -> Get Data -> From Other Sources -> Blank Query) named fxRowAndColTotal and enter this code:

let
    Source = ( InputTable as table, InitialColumnCount as number, optional AddColumnTotal as logical ) =>

let
    Cols = List.Skip(
                Table.ColumnNames(InputTable),
                InitialColumnCount ),

    InitialCols = List.FirstN(
                Table.ColumnNames(InputTable),
                InitialColumnCount ),

    TotalLabelAdded =  List.Accumulate(
                InitialCols,
                [],
                (s,c)=> Record.AddField(s, c, "Total") ),

    TotalRecord = List.Accumulate(
            Cols,
            TotalLabelAdded,
            (s,c)=> Record.AddField(s, c, List.Sum(Table.Column(InputTable, c))) ),

    RowTotal = Table.InsertRows(
                InputTable,
                Table.RowCount(InputTable), {TotalRecord} ),
 
    ColTotal = Table.AddColumn(
                RowTotal, "Col Total",
                each List.Sum(List.Select(Record.ToList(_), each _ is number)) )
in

    if AddColumnTotal = true then ColTotal else RowTotal
in
    Source

Finally, from the Power Query Home tab, select Append -> Append as New, select the three tables added to mash them all together, and then invoke the function above:

let
    Source = Table.Combine({Table1, Table2, Table3}),
    GroupedAllRows = Table.Group(Source, {"Group"}, {{"AllRows", each _, type table [#"Sl."=nullable number, Item=nullable text, Amount=nullable number, Group=text]}}),
    AddedCustom = Table.AddColumn(GroupedAllRows, "AddTotalToTables", each fxRowAndColTotal([AllRows], 3, true )),
    RemovedOtherColumns = Table.SelectColumns(AddedCustom,{"AddTotalToTables"}),
    ExpandedAddTotalToTables = Table.ExpandTableColumn(RemovedOtherColumns, "AddTotalToTables", {"Sl.", "Item", "Group", "Amount", "Col Total"}, {"Sl.", "Item", "Group", "Amount", "Col Total"}),
    RemovedColTotal = Table.RemoveColumns(ExpandedAddTotalToTables,{"Col Total"}),
    ChangedType = Table.TransformColumnTypes(RemovedColTotal,{{"Sl.", type text}, {"Item", type text}, {"Group", type text}, {"Amount", Int64.Type}}),
    ReplacedTotal = Table.ReplaceValue(ChangedType,"Total","",Replacer.ReplaceText,{"Sl.", "Item"}),
    ReplacedSubtotal = Table.ReplaceValue(ReplacedTotal,"Total","Subtotal",Replacer.ReplaceText,{"Group"})
in
    ReplacedSubtotal

to get this:

CombineSheets.xlsx
	A	B	C	D
1	Sl.	Item	Group	Amount
2	1	A111	A	10
3	2	A222	A	20
4	3	A333	A	30
5	4	A444	A	40
6	5	A555	A	50
7	6	A666	A	60
8	7	A777	A	70
9	8	A888	A	80
10	9	A999	A	90
11	10	A1110	A	100
12			Subtotal	550
13	101	B111	B	100
14	102	B222	B	200
15	103	B333	B	300
16	104	B444	B	400
17	105	B555	B	500
18	106	B666	B	600
19	107	B777	B	700
20	108	B888	B	800
21	109	B999	B	900
22	110	B1110	B	1,000
23			Subtotal	5,500
24	1001	C111	C	1,000
25	1002	C222	C	2,000
26	1003	C333	C	3,000
27	1004	C444	C	4,000
28	1005	C555	C	5,000
29	1006	C666	C	6,000
30	1007	C777	C	7,000
31	1008	C888	C	8,000
32	1009	C999	C	9,000
33	1010	C1110	C	10,000
34			Subtotal	55,000
Report

If you don't want the Group column, use this code:

let
    Source = Table.Combine({Table1, Table2, Table3}),
    GroupedAllRows = Table.Group(Source, {"Group"}, {{"AllRows", each _, type table [#"Sl."=nullable number, Item=nullable text, Amount=nullable number, Group=text]}}),
    AddedCustom = Table.AddColumn(GroupedAllRows, "AddTotalToTables", each fxRowAndColTotal([AllRows], 3, true )),
    RemovedOtherColumns = Table.SelectColumns(AddedCustom,{"AddTotalToTables"}),
    ExpandedAddTotalToTables = Table.ExpandTableColumn(RemovedOtherColumns, "AddTotalToTables", {"Sl.", "Item", "Group", "Amount", "Col Total"}, {"Sl.", "Item", "Group", "Amount", "Col Total"}),
    RemovedColumns = Table.RemoveColumns(ExpandedAddTotalToTables,{"Group", "Col Total"}),
    ChangedType = Table.TransformColumnTypes(RemovedColumns,{{"Sl.", type text}, {"Item", type text}, {"Amount", Int64.Type}}),
    ReplacedTotal = Table.ReplaceValue(ChangedType,"Total","",Replacer.ReplaceText,{"Sl."}),
    ReplacedSubtotal = Table.ReplaceValue(ReplacedTotal,"Total","Subtotal",Replacer.ReplaceText,{"Item"})
in
    ReplacedSubtotal

Note that the columns to be totaled need to be at the end of the table, and the second parameter of the function is the number of columns to skip.
Just to be clear, the function is not mine, I got it from Goodly. Point being a search of YouTube or Google will usually point you in the right direction.
Hope that helps.

Thankyou for your reply, please let me try it.

 

[mumps]

I missed the subtotals and grand total in my previous post. Please try:

Sub UpdateMain()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, lRow As Long, lRow2 As Long, desWS As Worksheet
    Set desWS = Sheets("Main sheet")
    desWS.UsedRange.Offset(1).ClearContents
    For Each ws In Sheets
        If ws.Name <> "Main sheet" Then
            With desWS
                lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                If lRow = 1 Then
                    ws.UsedRange.Offset(1).Copy .Range("A2")
                    lRow2 = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                    .Range("B" & lRow2 + 1) = "Subtotal"
                    .Range("C" & lRow2 + 1) = WorksheetFunction.Sum(.Range("C2:C" & lRow2))
                Else
                    lRow = .Columns(2).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                    ws.UsedRange.Offset(1).Copy .Range("A" & lRow + 1)
                    lRow2 = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                    .Range("B" & lRow2 + 1) = "Subtotal"
                    .Range("C" & lRow2 + 1) = WorksheetFunction.Sum(.Range("C" & lRow + 1 & ":C" & lRow2))
                End If
            End With
        End If
    Next ws
    With desWS
        lRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Range("B" & lRow + 1) = "Grand Total"
        .Range("C" & lRow + 1) = WorksheetFunction.Sum(.Range("C2:C" & lRow))
    End With
    Application.ScreenUpdating = True
End Sub