-
1 Attachment(s)
consolidate identical reports
-
1 Attachment(s)
try Power Query
Code:
let
Source = Table.Combine({Table1, Table2}),
#"Grouped Rows" = Table.Group(Source, {"Name"}, {{"Name.1", each _, type table [Name=text, Revenue=number, Purchases=number]}, {"Revenue", each List.Sum([Revenue]), type number}, {"Purchases", each List.Sum([Purchases]), type number}}),
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Name.1"})
in
#"Removed Columns"
Attachment 6133
-
3 Attachment(s)
Hello Amy
Welcome to ExcelFox :)
Quote:
Originally Posted by
Amy
..... Is there a way to consolidate them in or report using EXCEL VBA.
Attachment 6132
As far as a Excel VBA solution is concerned, the simple answer to that is Yes, or perhaps yeses – In Excel VBA there are endless possibilities and permutations to do something like that.
It is difficult to give you more specific help without knowing something about you, your Excel VBA knowledge and abilities, and more precise details of the final requirement, typical actual data sizes etc. etc.
If your data has a very tidy simple ordered structure as your sample and explanation suggests might be the case, then it’s worth pointing out perhaps that a non VBA solution may be a much better alternative, for example , as Sir Moderator, sandy666, has shown you . Other non VBA solutions may also be worth considering. (I am personally less familiar with non VBA solutions, but even a simple formula or formulas might be sometimes sufficient)
Please note, it is easier for us to work on a sample workbook, rather than an image, since then we do not have to construct a workbook ourselves in order to show you examples, and it is then also easier for us to investigate solutions.
Some example solutions, formula and VBA
As sandy666 has given a solution with an example file, I will do a short VBA / formula solution example as a comparison using similar data.
But I will put the results in a new output range so as not to disturb or destroy that existing Power Query solution.
This/these is/are an example of a formula and VBA solution.
I think it helps with this demonstration to include another sample data rage which I will include in a separate new workbook, Book2.xls . For the demonstration solution to work, you will need to download both workbooks, Book1.xlsx and Book2.xls to the same place , (but a final solution does not have to have this restriction: Files can be anywhere as long as you know where.
Note that it is only necessary to have Book1.xlsx open for these demonstrations to work. The other workbook, Book2.xls , can be open or closed. This is because I am applying a closed workbook reference to retrieve values from a closed workbook, which is possible in Excel without VBA. (I am creating the closed workbook formula for convenience with VBA, but it is not necessary: you can add those formulas manually).
So, this first solution, Sub FormulaInWithVBA(), puts the same relative reference formula across the output results range, then saves the file.
Code:
Sub FormulaInWithVBA() ' https://www.excelfox.com/forum/showthread.php/2991-consolidate-identical-reports
' Let Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("C21:D23") = "=SUM(C3,G3,'C:\Users\acer\Desktop\[Book2.xls]Tabelle1'!C3)"
Let Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("C21:D23") = "=SUM(C3,G3,'" & ThisWorkbook.Path & "\[Book2.xls]Tabelle1'!C3)"
Workbooks("Book1.xlsx").Save
End Sub
Finally you have a formula solution, as you only are using the coding once to put the formulas in: You could do the same by writing the formulas in yourself manually instead
The second solution, Sub VBA_ClosedWorkbookFormulaSolution() , is almost the same, but it simply converts the formulas to values.
Code:
Sub VBA_ClosedWorkbookFormulaSolution() ' https://www.excelfox.com/forum/showthread.php/2991-consolidate-identical-reports
' Let Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("C21:D23") = "=SUM(C3,G3,'C:\Users\acer\Desktop\[Book2.xls]Tabelle1'!C3)"
Let Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("C21:D23") = "=SUM(C3,G3,'" & ThisWorkbook.Path & "\[Book2.xls]Tabelle1'!C3)"
Let Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("C21:D23") = Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("C21:D23").Value
Workbooks("Book1.xlsx").Save
End Sub
This second solution is just one of an infinite number or possibly VBA solutions. One advantage of this solution is that the data workbooks do not have to be open
Alan
