Generating a Summary that has varying number of entries based

[chiron]

[TABLE="width: 553"]
<tbody>[TR]
[TD][/TD]
[TD]Section 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item No.[/TD]
[TD]Description[/TD]
[TD]Budget[/TD]
[TD]Total Invoice[/TD]
[TD]Current Invoice[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]AAAA[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]BBBB[/TD]
[TD]10[/TD]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]CCCC[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]DDDD[/TD]
[TD]10[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Subtotal[/TD]
[TD]40[/TD]
[TD]18[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Section 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item No.[/TD]
[TD]Description[/TD]
[TD]Budget[/TD]
[TD]Total Invoice[/TD]
[TD]Current Invoice[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]E[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]F[/TD]
[TD]10[/TD]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]G[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Subtotal[/TD]
[TD]30[/TD]
[TD]15[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]


I want to automatically generate a summary that looks like this in a different sheet;

[TABLE="width: 208"]
<tbody>[TR]
[TD]Item No.[/TD]
[TD] Current Invoice[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD="align: right"]7[/TD]
[/TR]
</tbody>[/TABLE]

I cannot change the structure of the original table and it has titles repeated as text for each section. What is a good way to do this? The way I am doing it now is to filter and then copy visible cells into the next sheet and total but I think there must be a way to do dynamically generate this table based on the data entered.

 

[sandy666]

use PowerQuery (Get&Transform)
transform tables into proper tables
append into one table
define which items you want to see (even/odd) and filter it
then load table to the sheet whereever you want

ps.
section1 & section2 pretend to be one "table" or they are single tables?
are they excel tables or ranges ?
the names Section1 and Section2 are included in these tables or are they only used for example

 

[chiron]

It is a single worksheet with repeating titles and blank rows between sections. Can't change that sheet as its what my company is sticking to. I was thinking along the lines of a macro which will check for non-zero numeric values in the "Current Invoice" column and put the corresponding data into a different sheet, parsing till it gets to the end of the worksheet.

Not familiar with Powerquery. Will look into it.

 

[sandy666]

here is result from your source "as is"

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Item No.[/td][td=bgcolor:#70AD47]Current Invoice[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

2​

[/td][td=bgcolor:#E2EFDA]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

4​

[/td][td]

3​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

6​

[/td][td=bgcolor:#E2EFDA]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Total[/td][td]

7​

[/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.Workbook(File.Contents("[I][COLOR="#0000FF"]path to your excel file[/COLOR][/I]"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Filtered Rows" = Table.SelectRows(Sheet1_Sheet, each ([Column5] <> null)),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Item No.", type any}, {"Description", type text}, {"Budget", type any}, {"Total Invoice", type any}, {"Current Invoice", type any}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([#"Item No."] <> null and [#"Item No."] <> "Item No.") and ([Current Invoice] <> 0)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"Item No.", "Current Invoice"})
in
    #"Removed Other Columns"[/SIZE]

edit:
if you want vba you need to wait for someone else

 

[chiron]

I did not really understand how that code works. I made a new sheet and tried going to Data> From Other Sources > From Microsoft Query and opened my source file to get the message "data source contains no visible tables" (I guess because there are no tables :D). I am not allowed to do anything to the source worksheet formatting including making tables.

 

[sandy666]

Data - Show Queries - on the right side you'll see 1 query - right click - Edit - in new window - Home - Advanced Editor - in first line change path to this file to real full path, eg. C:\... etc.. then ok, close and load

example excel file

edit: before above you should save file on your computer, Desktop or any other place....

 

[chiron]

I downloaded the example file. When I change the values in the main sheet the changes are not reflected in the summary. Such as if I current invoice for Item #2 to zero then the summary should update to show only Items 4 & 6.

 

[chiron]

I'd definitely prefer a solution that does not involve VBA, but I want the summary to update without my doing anything or maybe just one click.

 

[sandy666]

it was done with your example. I didn't see more in the first post and even in description. No structure, no type of data etc etc.
if you want update right click on the green table and select refresh

solution is an option so you can use or not

have a nice day

 

[sandy666]

I added more sections with dummy data then refresh and it works as you can see