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
Save me some time by supplying a workbook with a couple of tables filled out with the formulas you're using.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
here's the sample workbook sample.xlsm
Could you address ALL these please:
- I see the formulae refer to cells on the same sheet. Would it not be possible to get this data from the Consolidated sheet?
- Are you expecting plain values in each output table in the end, or are you happy for formulae to remain?
- 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.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
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.
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.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
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.
Sorry I am really new to this, I am not aware that can be done.. But thank you I learned something new
Yes.
Whichever you think is best. I really have no other idea besides the =""
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
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.
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).
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
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?
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
Hello, here's your file. I added more data here.
Yes, all values are the same.
All these formulae give the same result: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.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'
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.
Last edited by p45cal; 01-10-2020 at 03:07 PM.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
(I've made several edits to my last message as well as attaching a file.)
I'll check it in a bit. Also what error were you getting?
In the sheet I attached in the pivot choose category 1b:
2020-01-11_001804.jpg
bedtime here.
Last edited by p45cal; 01-10-2020 at 07:19 PM.
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
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
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3