1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Cell To Display Status Based on Condition

Discussion in 'Ask an Excel Question' started by cyliyu, Sep 10, 2017.

  1. cyliyu

    cyliyu Member

    Messages:
    93
    I have a worksheet required to check and auto reflect the status based on the number of time the same s/no occurring.
    The condition will be:-
    1) All the s/no happen in the first time, the status = Open
    2) If the s/no repeated the 2nd time, the status = open (1st time) and closed (2nd time).
    3) If the s/no repeated a few times, the status changed to open (1st time), in-progress (2nd time onward) and closed (last time)

    Need help please!
    upload_2017-9-10_21-8-51.png

    Attached Files:

  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,586
    Hi ,

    Please upload your workbook instead of screenshots. A formula has to have worksheet cell references in it.

    Secondly , will the maximum number of occurrences be 3 ? What if there are more than 3 occurrences ?

    Narayan
  3. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,247
    upload_2017-9-11_1-0-37.png

    In B2, copied down :

    =IF(A2="","",IF(COUNTIF(A$2:A2,A2)=1,"Open",IF(COUNTIF(A$2:A2,A2)=COUNTIF(OFFSET(A$2,,,LOOKUP(9^9,A:A)),A2),"Closed","In-Progress")))

    Regards
    Bosco
    Thomas Kuriakose likes this.
  4. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,247
    The above formula, please revised LOOKUP into MATCH as in :

    =IF(A2="","",IF(COUNTIF(A$2:A2,A2)=1,"Open",IF(COUNTIF(A$2:A2,A2)=COUNTIF(OFFSET(A$2,,,MATCH(9^9,A:A)),A2),"Closed","In-Progress")))

    Regards
    Bosco
  5. cyliyu

    cyliyu Member

    Messages:
    93
    Thanks Bosco for your great help.
    It work except I need to change some of the condition.
    Details explanation can found in the spreadsheet remarks.

    upload_2017-9-11_11-28-13.png

    Attached Files:

  6. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,247
    In H3, copied down :

    =IF(D3="","",IF((D3<>D4)*ISODD(G3)+(G3=1),"Open",IF((D3<>D4)*ISEVEN(G3),"Closed","In-Progress")))

    Regards
    Bosco

    Attached Files:

    Thomas Kuriakose likes this.
  7. cyliyu

    cyliyu Member

    Messages:
    93
    Thanks Bosco.
    Your formula was assuming the s/no is sequence. if I sort by date it will not work.
  8. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,247
    Then, changed the formula to >>

    =IF(D3="","",IF((COUNTIF(D$3:D3,D3)=COUNTIF(D$3:D$32,D3))*ISODD(G3)+(G3=1),"Open",IF((COUNTIF(D$3:D3,D3)=COUNTIF(D$3:D$32,D3))*ISEVEN(G3),"Closed","In-Progress")))

    Regards
    Bosco

    Attached Files:

    Thomas Kuriakose and cyliyu like this.
  9. cyliyu

    cyliyu Member

    Messages:
    93
    It's work.
    Thanks and appreciate Bosco for the great help.
  10. cyliyu

    cyliyu Member

    Messages:
    93
    One more request, can column E (In/Out) automate? starting from 1st occurrence is Out and next will be In and Out - In again....
  11. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,247
    In E3, copied down :

    =IF(D3="","",IF(ISODD(COUNTIF(D$3:D3,D3)),"Out","In"))

    Regards
    Bosco
    cyliyu and Thomas Kuriakose like this.
  12. cyliyu

    cyliyu Member

    Messages:
    93
    Thanks Bosco.
    You have make my spreadsheet easy to maintain and prevent data entry error.
    Appreciate.
  13. cyliyu

    cyliyu Member

    Messages:
    93
    I have added a date code and unit count columns in my spreadsheet as per production request.

    The existing spreadsheet only checks on S/No but not Date Code.

    It can happen to have the same serial number but different date code.

    I need help in column F (In/Out) as it should be displayed as OUT since 1st occurrence and also column G (Unit count) should be "4" as this is a new unit.

    upload_2017-10-9_8-57-19.png

    Attached Files:

  14. cyliyu

    cyliyu Member

    Messages:
    93
    Took me few days to try and error.
    Replaced COUNTIF to COUNTIFS and added the additional column.
    Will test out with my large data tomorrow to confirm it is error free.
    Thanks Chandon.org. It helps me a lots.

Share This Page