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. ### cyliyuMember

Messages:
170
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)

File size:
5.7 KB
Views:
2
2. ### NARAYANK991Excel Ninja

Messages:
16,619
Hi ,

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

Narayan
3. ### bosco_yipExcel Ninja

Messages:
1,908

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

Messages:
1,908
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. ### cyliyuMember

Messages:
170
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.

File size:
13 KB
Views:
4
6. ### bosco_yipExcel Ninja

Messages:
1,908
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:

• ###### Book1 (6a).xlsx
File size:
12.7 KB
Views:
5
Thomas Kuriakose likes this.
7. ### cyliyuMember

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

Messages:
1,908
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:

• ###### Book1 (6b).xlsx
File size:
13.8 KB
Views:
8
Thomas Kuriakose and cyliyu like this.
9. ### cyliyuMember

Messages:
170
It's work.
Thanks and appreciate Bosco for the great help.
10. ### cyliyuMember

Messages:
170
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_yipExcel Ninja

Messages:
1,908
In E3, copied down :

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

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

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

Messages:
170
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.

File size:
13.6 KB
Views:
3
14. ### cyliyuMember

Messages:
170
Took me few days to try and error.