Filter Excel data on multiple criteria

Question

I have the following Excel sheet that I want to filter:

Id	Status	run
1	COMPLETED	2
1	FAILED	1
3	FAILED	1
3	FAILED	2
5	COMPLETED	3
6	COMPLETED	1
5	FAILED	1
5	FAILED	2
9	COMPLETED	1
7	COMPLETED	1

I want to get the rows where any id doesn't have a COMPLETED status and copy them to a new sheet. So for example, using the above table, the new table should only have the rows with id 3 since that one never got a COMPLETED status.

I'm new to Excel and I'm not sure where to start looking or what to search for to get a guide or documentation on what I can use.

Answer 1

You can add a helper column to your data.

Formula for cell D2, which counts the total number of Completed statuses for that row's ID.

=COUNTIFS(B:B,"COMPLETED",A:A,A2)

Drag down the formula and here's the Output:

id	status	run	HELPER
1	COMPLETED	2	1
1	FAILED	1	1
3	FAILED	1	0
3	FAILED	2	0
5	COMPLETED	3	1
6	COMPLETED	1	1
5	FAILED	1	1
5	FAILED	2	1
9	COMPLETED	1	1
7	COMPLETED	1	1

Then filter where Helper = 0.

Answer 2

1. Put your cursor inside the table
2. Go to insert ribbon
3. Add new Pivot Table

Then in pivot table fields, add id to the rows, status to columns and run to values

You will get the following result

Now you can copy the data to a new sheet and paste it as VALUES Then with a simple filter you can get the null completed ids