I have a large table with lots of "ORDERS" that go by numbers, each has multiple numbered "STEPS" (not necessarily equal in all orders). Each step has a "STATUS" which is one of two: "In process" or "completed".
I want to create a column that tracks each orders' step, and if they are all completed it will mark the order in all of it's rows as "FINISHED".
I tried formula Array but I can't think about something that worked.
EXAMPLE of a Desired outcome: (First row and column are belong to Excel's bar)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ORDER number | STEP number | STEP Status | ORDER STATUS |
| 2 | 179 | 001 | completed | FINISHED |
| 3 | 179 | 002 | completed | FINISHED |
| 4 | 179 | 003 | completed | FINISHED |
| 5 | 179 | 004 | completed | FINISHED |
| 6 | 192 | 001 | In process | |
| 7 | 192 | 002 | completed | |
| 8 | 192 | 003 | completed | |
| 9 | 192 | 004 | In process | |
| 10 | 192 | 005 | In process | |
| 11 | 202 | 001 | completed | FINISHED |
| 12 | 202 | 002 | completed | FINISHED |
| 13 | 202 | 003 | completed | FINISHED |
| 14 | 202 | 004 | completed | FINISHED |
| 15 | 202 | 005 | completed | FINISHED |
| 16 | 202 | 006 | completed | FINISHED |
CodePudding user response：
In D2, formula should be =IF(COUNTIFS(A:A, A2, C:C, "completed") = COUNTIF(A:A, A2), "FINISHED", ""). Then copy that formula down column D.