Consulting

Results 1 to 15 of 15

Thread: Helper columns to solve problem

  1. #1
    VBAX Regular
    Joined
    Jul 2022
    Posts
    13
    Location

    Helper columns to solve problem

    HI Guys
    In the attached spreadsheet I am trying to identify each time the format changes during the day in column J such as to eventually identify the start and finish time of the format (as explained in column K). I have tried in column J to create a formula to detect the format changeover in column C but the zero values in column C are leading to errors in my formula.
    Do I need to create extra helper columns to help sort the data? I would appreciate any help possible
    Thanks
    Chris
    Attached Files Attached Files

  2. #2
    VBAX Mentor
    Joined
    Sep 2019
    Location
    Philippines
    Posts
    416
    Location
    you can try to use this formula in Cell, J2:

    =IF(ROW()=2,1,IF(C2<>C1,1,0))


    copy it down, make sure to set your Calculation to Automatic.

  3. #3
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,187
    Location
    Sadly your function will not work as an If(Or since it cannot hold more than two tests by its very nature. If(Or(Logical test1, Logical Test2, Result if true, Result if false).
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  4. #4
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,187
    Location
    Perhaps try a derivative of arnelgp's formula.... =If(And(Row()=2,C2<>C1,B2,""))
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  5. #5
    VBAX Regular
    Joined
    Jul 2022
    Posts
    13
    Location
    Hi GuysThanks for your help and suggestions. I have not managed to put the proposed formula but I have tried something else which iseems to be OK in column J.

    The problem I have now is that I want to use column J to detect when the format has changed such as to enter the start and finish time of a series of a particular format for each day. IN the table the days of 1st and 2nd June give the correct start and finish time but this is based on the first and last products of that format during the day. However, if there are more than one series of that format during the day (ie seperated by another format) the formula does not work.

    The ideal solution shows what I am trying to achieve with the start and finish times for each series of a format (especially if there is more than one run of that format during the day).Many thanks for you help
    Attached Files Attached Files

  6. #6
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,583
    In K2:
    PHP Code:
    =IF((COUNTIF($K$1:$K1;C2)=0)*(C2<>0);C2;""

  7. #7
    VBAX Regular
    Joined
    Jul 2022
    Posts
    13
    Location
    HI Guys

    Thanks for all your help and proposals.
    In the end what I am trying to do is to identify the start and finish times of a series.

    I though that creating a helper column in column K in the attached file which would check in the above cells in column J whether the format has been produced or not during the day (ie Gn 1/3, Bol or verrines). If not, then a 1 would be allocated in column K and this until the format changes over. If the new format for that day has not yet been previously produced during the day, then again a 1 would be allocated.

    For June 3 data, the format Bol is produced once but then again at 17:46. Ideally the formula would take this into account and place a 2 in the column K, etc as hightlighted in the attached file.

    I would most appreciate any help or alternative ideas for the formula in column K
    Attached Files Attached Files

  8. #8
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,583
    In the end what I am trying to do is to identify the start and finish times of a series.
    C'est exactement ce que ma Code fait.

  9. #9
    VBAX Regular
    Joined
    Jul 2022
    Posts
    13
    Location
    Hi SNB
    Thanks for your reply. Your code highlights the first time the format appears (Gn 1/3, Bol, Verrines) but afterwards when the formula is copied down it does not identify the next time those formats are produced on the following days - it only identifies it once. As per my previous message, I would like to identify each time a format is changed for that day by a 1 and if the format is produced again later in the day by a 2, etc as per the numbers I have inputed manually in column J.
    Ideally I would like to have an automatic formula for this instead in column J
    Thanks for your help and sorry for the confusion

  10. #10
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,618
    In the attached a pivot table at cell K12 derived from a Power Query transformation using only columns A:I.
    Update the data in the table on the left then refresh the pivot to see new results.
    2022-09-21_141834.jpg
    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.

  11. #11
    VBAX Regular
    Joined
    Jul 2022
    Posts
    13
    Location
    Hi p45cal
    Thank you very much for investigating this and your proposed solution. However, I have Excel 2013 and it does not seem to work with Power Query?
    Kind regards
    Chris

  12. #12
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,187
    Location
    I believe that Power Query came with the introduction of Office 365
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  13. #13
    VBAX Mentor
    Joined
    Sep 2019
    Location
    Philippines
    Posts
    416
    Location
    Power Query is a downloadable Add-in for Excel 2013
    Download Microsoft Power Query for Excel from Official Microsoft Download Center

  14. #14
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,618
    Quote Originally Posted by Aussiebear View Post
    I believe that Power Query came with the introduction of Office 365
    Excel 2016 built-in (still after Excel 2013).
    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. #15
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,187
    Location
    Well there you go then. I need to ask the jail authorities to update my reading material by 20 years.
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

Posting Permissions

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