• 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...

  • 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

cyliyu

Member
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
 

Attachments

  • upload_2017-9-10_21-7-31.png
    upload_2017-9-10_21-7-31.png
    5.7 KB · Views: 2
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
 
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
 
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

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
 
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
 

Attachments

  • Book1.xlsx
    13 KB · Views: 4
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.

In H3, copied down :

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

Regards
Bosco
 

Attachments

  • Book1 (6a).xlsx
    12.7 KB · Views: 5
Thanks Bosco.
Your formula was assuming the s/no is sequence. if I sort by date it will not work.

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
 

Attachments

  • Book1 (6b).xlsx
    13.8 KB · Views: 8
One more request, can column E (In/Out) automate? starting from 1st occurrence is Out and next will be In and Out - In again....
 
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
 

Attachments

  • Checking with Date Code.xlsx
    13.6 KB · Views: 3
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.
 
Back
Top