how to use Multi Row Formula based on ID and Status

[manjarigoyal1975]

I have a table in which I have multiple locations and each location has a specific Task ID (1 to 12). I need to group by locationID and fill the column Ready to work where if any Status is New or In progress after the Closed. Below is the template. I am looking for how to populate the Column ReadyToWork as shown below.

Task Number	Location ID	Status	ReadyToWork
1	A	Closed
2	A	Closed
3	A	N/A
4	A	Closed
5	A	Closed
6	A	Closed
7	A	N/A
8	A	Closed
9	A	Closed
10	A	Closed
11	A	Closed
12	A	New	ReadyToWork
1	B	Closed
2	B	Closed
3	B	N/A
4	B	Closed
5	B	Closed
6	B	Closed
7	B	N/A
8	B	Closed
9	B	New	ReadyToWork
10	B	New
11	B	New
12	B	New
1	C	Closed
2	C	Closed
3	C	N/A
4	C	Closed
5	C	In Progress	ReadyToWork
6	C	New
7	C	New
8	C	New
9	C	New
10	C	New
11	C	New
12	C	New
1	D	Closed
2	D	Closed
3	D	N/A
4	D	Closed
5	D	In Progress	ReadyToWork
6	D	New
7	D	Closed
8	D	New
9	D	New
10	D	New
11	D	New
12	D	New

[apathetichell]

Hi,

so the way how I read this it was any "new" after the last "closed" but in location D there is an "In Progress" that comes before a "Closed". Also - do all of the entries get "ReadyToWork" or just the first.

[ArtApa]

Hi @manjarigoyal1975 - Here is a sample:

[manjarigoyal1975]

@ArtApa  In Location ID "D" In Progress comes first after closed and New comes later then "Ready To Work" should only display on the row of In Progress, not on the Row  New.

[DawnDuong]

Hi @manjarigoyal1975 

The trick is to use the cumulative count (with the Multirow tool). I attached an example which gives the same desired results.

Good luck,

Dawn.

[apathetichell · Accepted solution]

And here's how to do it without a multi-row formula....

 

[manjarigoyal1975]

Thankyou. It is working exactly the way I wanted it.