Creating Multiple Tables Using Loop in VBA. I still want to add new sheets and add

[p45cal]

Worked like a charm
We're using GMT. Is there a need to change anything for that?

I have no idea.

Do I add this after

Next cll
If Not NewSht Is Nothing Then NewSht.Columns("B:B").EntireColumn.AutoFit
If Not LastTable Is Nothing Then LastTable.Unlist
End Sub

?

Yes

[jazz2409]

On the consolidated sheet there are many rows for the same date/time/Lob/SubLob which you seem to want to put on one line in the new sheets. How are you summarising theose multiple rows?

There is a sheet there called computation. It has the formula on how to compute for each column

Originally I do SUMIFS (on some columns, for example) based on Sub Category and the time, but if I am to pursue using looping statements then I think I can no longer do that because it significantly slowed down your code

[p45cal]

Save me some time by supplying a workbook with a couple of tables filled out with the formulas you're using.

[jazz2409]

Save me some time by supplying a workbook with a couple of tables filled out with the formulas you're using.

Sure.. However it might take a few hours..

[jazz2409]

here's the sample workbook sample.xlsm

[p45cal]

Could you address ALL these please:

1. I see the formulae refer to cells on the same sheet. Would it not be possible to get this data from the Consolidated sheet?
2. Are you expecting plain values in each output table in the end, or are you happy for formulae to remain?
3. When an update of new data comes in, will old values remain the same? I ask this last because a complete refresh/recreation would be much easier and more robust than adding new data below old data.

[jazz2409]

1. I honestly think that it's best to get the data from the consolidated sheet
2. I actually prefer plain values
3. The old values has to remain the same. The new data will only be added to the tables. However I prefer the complete recreation since all data will stay in the consolidated sheet so even if we recreate the sheets the data will still be the same for the previous hours.

[p45cal]

Your formulae don't take into account Lob and SubLob!
They will be considerably longer. In a bid to shorten them, several of them show "" when it's going to be zero, viz:

=IF(SUMIF(Consolidated!$C:$C,$B3,Consolidated!$P:$P)=0,"",SUMIF(Consolidated!$C:$C,$B3,Consolidated!$P:$P))

where the blue is a replica of the red.
In a bid to shorten the formulae, did you know that you can set the whole sheet to show zeroes as blanks in Options? (untick the box at Options|Advanced|Display Options for this worksheet|Show a zero in cells that have zero value).
This affects ALL the cells on that sheet. Code can do it (ActiveWindow.DisplayZeros = False). Can we use it?
[There is another way using cell formatting where we could format all cells in a table to hide zero values - up to you]

ps. you could have included Lob/SubLob considerations in your formulae.

[jazz2409]

Your formulae don't take into account Lob and SubLob!

Sorry I forgot to indicate that in my actual workbook, the first table is always the overall per LOB that's why the workbook I sent with a table that has formula doesn't take the LOB and Sub LOB into consideration.

In a bid to shorten the formulae, did you know that you can set the whole sheet to show zeroes as blanks in Options? (untick the box at Options|Advanced|Display Options for this worksheet|Show a zero in cells that have zero value).

Sorry I am really new to this, I am not aware that can be done.. But thank you I learned something new

This affects ALL the cells on that sheet. Code can do it (ActiveWindow.DisplayZeros = False). Can we use it?

Yes.

[There is another way using cell formatting where we could format all cells in a table to hide zero values - up to you]

Whichever you think is best. I really have no other idea besides the =""

[p45cal]

Sorry I forgot to indicate that in my actual workbook, the first table is always the overall per LOB that's why the workbook I sent with a table that has formula doesn't take the LOB and Sub LOB into consideration.

Your formulae don't take into account even LOB. Do I take it this table resides on its own sheet and does NOT take into account either Lob or SubLob?

[jazz2409]

No it's on the same sheet.


So my initial workbook filters the consolidated sheet per Main LOB first and then the filtered set of data are being copied and pasted to its respective sheet. After it has been copied and pasted to its respective sheet, the Table 1 says Overall that's why the formula only calculates everything without taking the Main LOB and Sub LOB into consideration.


Then Table 2 is where the Sub LOBs actually start. Like:




1a.jpg

So Main LOB, Sub LOB, and time will be taken into consideration starting the second table up to the last one.

[p45cal]

And do you think it will be easier to just pivot the data then put them in the tables rather than use formulas?

Just addressing this for a moment as a possibility.
In the attached is a pivot table on Sheet9, which at first sight appears to give the correct answers.
I need you to check that it's giving the right results, in all the columns, for the various combinations of Lob and SubLob using the slicers or the dropdowns at the top of column B of Sheet9.
There's not much data in that file, but you can change the source of the Pivot table to a bigger data set to check more thoroughly. The headers need to be exactly the same (at least the ones that are used used in the Pivot).

[jazz2409]

Just addressing this for a moment as a possibility.
In the attached is a pivot table on Sheet9, which at first sight appears to give the correct answers.
I need you to check that it's giving the right results, in all the columns, for the various combinations of Lob and SubLob using the slicers or the dropdowns at the top of column B of Sheet9.
There's not much data in that file, but you can change the source of the Pivot table to a bigger data set to check more thoroughly. The headers need to be exactly the same (at least the ones that are used used in the Pivot).

Everything is correct except for Full AHT. Formula has to be =IF('OB Tasks'=0,'IB AHT'/'IB Tasks',('IB AHT'/'IB Tasks')+(('OB AHT'/'OB Tasks')* ('OB Tasks'/'IB Tasks')))

[p45cal]

That formula gives the same results as mine when looking at the dataset you provided on the Consolidated sheet.
Could you provide me with a bigger dataset so that I can compare more thoroughly?

[jazz2409]

Hello, here's your file. I added more data here.

[p45cal]

Yes, all values are the same.
All these formulae give the same result:

Your formula:
=IF('OB Tasks'=0,'IB AHT'/'IB Tasks',('IB AHT'/'IB Tasks')+(('OB AHT'/'OB Tasks')* ('OB Tasks'/'IB Tasks')))
Your formula shortened:
=IF('OB Tasks'=0,'IB AHT'/'IB Tasks',('IB AHT'/'IB Tasks')+('OB AHT'/'IB Tasks'))
Your formula further shortened:
='IB AHT'/'IB Tasks'+IF('OB Tasks'=0,0,'OB AHT'/'IB Tasks')
my formula:
=' IB AHT'+' OB AHT'*'OB Tasks'/'IB Tasks'

Note that some of these references have leading spaces (' IB AHT' & ' OB AHT'); they are different from others ('IB AHT' & 'OB AHT') and refer to other calculated fields in the pivot, so my last formula, although the shortest, is not necessarily more efficient nor the best.

Now another scenario discovered on your larger data set which might need to be catered for:
SubLOB Category 1b, Jan 10th 2020, at 6:00,12:00,16:00 & 20:00 have all zero values for IB Tasks (column K of the Consolidated sheet), while all have non-zero values for OB Tasks (Column P).
They're causing errors at the moment. Do they need dealing with?

See attached, Sheet9, columns L:O for comparison.

[p45cal]

(I've made several edits to my last message as well as attaching a file.)

[jazz2409]

I'll check it in a bit. Also what error were you getting?

[p45cal]

Also what error were you getting?

In the sheet I attached in the pivot choose category 1b:
2020-01-11_001804.jpg
bedtime here.

[Paul_Hossler]

Pardon me for jumping in so late, but

1. I think that a PT is the way to go. As a general rule, I like to separate the Data from the Computations from the Presentation

2. I'd delete the redundant fields such as Date-Time since you have atomic Date and atomic Time

3. In P45cal's PT, the some calculated fields divide by IB Tasks which sometimes sum to 0

Capture.jpg