Sort rows in a table in excel

Question

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

Spencer Barnes · Accepted Answer · 2021-12-06 11:13:40Z

2

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.

edited Dec 6, 2021 at 11:13

answered Dec 6, 2021 at 10:22

Spencer Barnes

2,8851 gold badge10 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ron Over a year ago

Thanks for commenting - I can see the logic, but the formula has a "too many arguments for format" error.

Spencer Barnes Over a year ago

Apologies, that first COUNTIF should be COUNTIFS, editing answer now

Ron Over a year ago

Thanks! It worked! I appreciate your answer

Collectives™ on Stack Overflow

Sort rows in a table in excel

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related