Consulting

Page 2 of 6 FirstFirst 1 2 3 4 ... LastLast
Results 21 to 40 of 104

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

  1. #21
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by jazz2409 View Post
    Worked like a charm
    We're using GMT. Is there a need to change anything for that?
    I have no idea.




    Quote Originally Posted by jazz2409 View Post
    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
    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.

  2. #22
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Quote Originally Posted by p45cal View Post
    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

  3. #23
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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.

  4. #24
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Quote Originally Posted by p45cal View Post
    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..

  5. #25
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    here's the sample workbook sample.xlsm

  6. #26
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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.
    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.

  7. #27
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    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.

  8. #28
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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.

  9. #29
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Quote Originally Posted by p45cal View Post
    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.

    Quote Originally Posted by p45cal View Post
    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

    Quote Originally Posted by p45cal View Post
    This affects ALL the cells on that sheet. Code can do it (ActiveWindow.DisplayZeros = False). Can we use it?
    Yes.

    Quote Originally Posted by p45cal View Post
    [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 =""

  10. #30
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by jazz2409 View Post
    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?
    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.

  11. #31
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    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.

  12. #32
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by jazz2409 View Post
    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).
    Attached Files Attached Files
    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.

  13. #33
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Quote Originally Posted by p45cal View Post
    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')))

  14. #34
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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.

  15. #35
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Hello, here's your file. I added more data here.
    Attached Files Attached Files

  16. #36
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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.
    Attached Files Attached Files
    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.

  17. #37
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    (I've made several edits to my last message as well as attaching a file.)

  18. #38
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    I'll check it in a bit. Also what error were you getting?

  19. #39
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by jazz2409 View Post
    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.

  20. #40
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •