How to group filtered data

[Clayton18]

Hi!

I'm looking for some help in grouping sets of data for when a filter is applied.

Example below:

Says below is the original set of data - I would like to be able to filter by tests that have "failed". Is it possible to keep all of Test number 2 & 4 showing when I filter for "fail"

Below is an example what I would like it to look like when the filter is applied.

Any help would be great!

Thanks,

 

[Eric W]

You could add a work column that shows "Fail" if any step in the test fails, then filter on that. For example:

Book1
	A	B	C	D	E
1	Test Number	Step	Instructions	Pass/Fail	Test Result
2	1	1	Open Software	Pass
3		2	Wait 5 minutes	Pass
4		3	Close Software	Pass
5	2	1	Open Software	Pass	Fail
6		2	Click "New"	Pass	Fail
7		3	Wait 5 minutes	Fail	Fail
8		4	Close Software	Pass	Fail
9	3	1	Open Software	Pass
10		2	Close Software	Pass
11	4	1	Open Software	Pass	Fail
12		2	Click "New"	Pass	Fail
13		3	type 5 words	Pass	Fail
14		4	Wait 5 minutes	Fail	Fail
15		5	Close Software	Pass	Fail
Sheet5

Cell Formulas
Range	Formula
E2:E15	E2	=LET(rng,INDEX(D$2:D2,XMATCH("Open Software",C$2:C2,0,-1)):INDEX(D2:D20,MATCH("Close Software",C2:C20,0)),c,COUNTIF(rng,"Fail"),IF(c,"Fail",""))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D:D,E1	Cell Value	="Fail"	text	NO
D:D,E1	Cell Value	="Pass"	text	NO

The hardest part of this was the merged cells with the test number. I had to look for the start and end of the test, and I just resorted to looking for "Open Software" and "Close Software" which bracketed each test. If that's not always going to be the case, I can look for a 1 in the step for the starting cell in the range, and look for a 1 (or empty cell) at the end, and back up 1 row.

If you put the test number in each of the rows, then it's much easier:

Book1
	A	B	C	D	E
1	Test Number	Step	Instructions	Pass/Fail	Test Result
2	1	1	Open Software	Pass
3		2	Wait 5 minutes	Pass
4		3	Close Software	Pass
5	2	1	Open Software	Pass	Fail
6		2	Click "New"	Pass	Fail
7		3	Wait 5 minutes	Fail	Fail
8		4	Close Software	Pass	Fail
9	3	1	Open Software	Pass
10		2	Close Software	Pass
11	4	1	Open Software	Pass	Fail
12		2	Click "New"	Pass	Fail
13		3	type 5 words	Pass	Fail
14		4	Wait 5 minutes	Fail	Fail
15		5	Close Software	Pass	Fail
Sheet6

Cell Formulas
Range	Formula
E2:E15	E2	=IF(COUNTIFS(A:A,A2,D:D,"Fail"),"Fail","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A15	Expression	=A1=A2	text	NO
D1:D15,E1	Cell Value	="Fail"	text	NO
D1:D15,E1	Cell Value	="Pass"	text	NO

I used Conditional Formatting to hide the test number after the first row.

 

[Peter_SSs]

If you want to keep the merged cells in column A then another option would be to use two helper columns (E & F for me and E could be hidden) to keep the formulas simpler & not dependant on any particular values in col C.

24 02 23.xlsm
	A	B	C	D	E	F
1	Test Number	Step	Instructions	Pass/Fail	TN	Test Result
2	1	1	Open Software	Pass	1
3		2	Wait 5 minutes	Pass	1
4		3	Close Software	Pass	1
5	2	1	Open Software	Pass	2	Fail
6		2	Click "New"	Pass	2	Fail
7		3	Wait 5 minutes	Fail	2	Fail
8		4	Close Software	Pass	2	Fail
9	3	1	Open Software	Pass	3
10		2	Close Software	Pass	3
11	4	1	Open Software	Pass	4	Fail
12		2	Click "New"	Pass	4	Fail
13		3	type 5 words	Pass	4	Fail
14		4	Wait 5 minutes	Fail	4	Fail
15		5	Close Software	Pass	4	Fail
Fail

Cell Formulas
Range	Formula
E2:E15	E2	=LOOKUP(9^9,A$2:A2)
F2:F15	F2	=IF(COUNTIFS(E$2:E$15,E2,D$2:D$15,"Fail"),"Fail","")

 

[Clayton18]

You could add a work column that shows "Fail" if any step in the test fails, then filter on that. For example:

Book1
	A	B	C	D	E
1	Test Number	Step	Instructions	Pass/Fail	Test Result
2	1	1	Open Software	Pass
3		2	Wait 5 minutes	Pass
4		3	Close Software	Pass
5	2	1	Open Software	Pass	Fail
6		2	Click "New"	Pass	Fail
7		3	Wait 5 minutes	Fail	Fail
8		4	Close Software	Pass	Fail
9	3	1	Open Software	Pass
10		2	Close Software	Pass
11	4	1	Open Software	Pass	Fail
12		2	Click "New"	Pass	Fail
13		3	type 5 words	Pass	Fail
14		4	Wait 5 minutes	Fail	Fail
15		5	Close Software	Pass	Fail
Sheet5

Cell Formulas
Range	Formula
E2:E15	E2	=LET(rng,INDEX(D$2:D2,XMATCH("Open Software",C$2:C2,0,-1)):INDEX(D2:D20,MATCH("Close Software",C2:C20,0)),c,COUNTIF(rng,"Fail"),IF(c,"Fail",""))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D:D,E1	Cell Value	="Fail"	text	NO
D:D,E1	Cell Value	="Pass"	text	NO

The hardest part of this was the merged cells with the test number. I had to look for the start and end of the test, and I just resorted to looking for "Open Software" and "Close Software" which bracketed each test. If that's not always going to be the case, I can look for a 1 in the step for the starting cell in the range, and look for a 1 (or empty cell) at the end, and back up 1 row.

If you put the test number in each of the rows, then it's much easier:

Book1
	A	B	C	D	E
1	Test Number	Step	Instructions	Pass/Fail	Test Result
2	1	1	Open Software	Pass
3		2	Wait 5 minutes	Pass
4		3	Close Software	Pass
5	2	1	Open Software	Pass	Fail
6		2	Click "New"	Pass	Fail
7		3	Wait 5 minutes	Fail	Fail
8		4	Close Software	Pass	Fail
9	3	1	Open Software	Pass
10		2	Close Software	Pass
11	4	1	Open Software	Pass	Fail
12		2	Click "New"	Pass	Fail
13		3	type 5 words	Pass	Fail
14		4	Wait 5 minutes	Fail	Fail
15		5	Close Software	Pass	Fail
Sheet6

Cell Formulas
Range	Formula
E2:E15	E2	=IF(COUNTIFS(A:A,A2,D:D,"Fail"),"Fail","")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A15	Expression	=A1=A2	text	NO
D1:D15,E1	Cell Value	="Fail"	text	NO
D1:D15,E1	Cell Value	="Pass"	text	NO

I used Conditional Formatting to hide the test number after the first row.

Hi, this looks awesome!

Could it be possible for the formula to look 1 in the step column and end 1 row above the next 1? if so what would the formula look like?
The "instructions" in this scenario were purely simple examples, generally they start and finish with different. wording

Cheers!

cell) at the end, an

 

[Eric W]

Here's how it would look using the Step to find the start/stop of the ranges:

Book1
	A	B	C	D	E
1	Test Number	Step	Instructions	Pass/Fail	Test Result
2	1	1	Open Software	Pass
3		2	Wait 5 minutes	Pass
4		3	Close Software	Pass
5	2	1	Open Software	Pass	Fail
6		2	Click "New"	Pass	Fail
7		3	Wait 5 minutes	Fail	Fail
8		4	Close Software	Pass	Fail
9	3	1	Open Software	Pass
10		2	Close Software	Pass
11	4	1	Open Software	Pass	Fail
12		2	Click "New"	Pass	Fail
13		3	type 5 words	Pass	Fail
14		4	Wait 5 minutes	Fail	Fail
15		5	Close Software	Pass	Fail
16
Sheet5

Cell Formulas
Range	Formula
E2:E16	E2	=LET(rng,INDEX(D:D,XLOOKUP(1,B$2:B2,ROW(B$2:B2),,0,-1)):INDEX(D:D,XLOOKUP(1,B3:B20,ROW(B3:B20),ROW(B20),0)-1),c,COUNTIF(rng,"Fail"),IF(AND(c,B2<>""),"Fail",""))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D:D,E1	Cell Value	="Fail"	text	NO
D:D,E1	Cell Value	="Pass"	text	NO

This formula assumes that no test will have over 20 steps. If that might happen, just change the 20 in the formula (2 places) to a higher value.

 

[Peter_SSs]

Hi, this looks awesome!

Seems like you were not interested in the 2-column approach. In that case, would this work for you?
Where I have used 30 in the formula (twice) use any number that will definitely be greater than the number of rows of data that you will ever have.

24 02 23.xlsm
	A	B	C	D	E
1	Test Number	Step	Instructions	Pass/Fail	Test Result
2	1	1	Open Software	Pass
3		2	Wait 5 minutes	Pass
4		3	Close Software	Pass
5	2	1	Open Software	Pass	Fail
6		2	Click "New"	Pass	Fail
7		3	Wait 5 minutes	Fail	Fail
8		4	Close Software	Pass	Fail
9	3	1	Open Software	Pass
10		2	Close Software	Pass
11	4	1	Open Software	Pass	Fail
12		2	Click "New"	Pass	Fail
13		3	type 5 words	Pass	Fail
14		4	Wait 5 minutes	Fail	Fail
15		5	Close Software	Pass	Fail
16
17
Fail (2)

Cell Formulas
Range	Formula
E2:E15	E2	=IF(B2=1,IF(IFNA(MATCH("Fail",D2:D$30,0),9^9)<=IFNA(MATCH(1,B3:B$30,0),9^8),"Fail",""),E1)

 

[Clayton18]

Seems like you were not interested in the 2-column approach. In that case, would this work for you?
Where I have used 30 in the formula (twice) use any number that will definitely be greater than the number of rows of data that you will ever have.

24 02 23.xlsm
	A	B	C	D	E
1	Test Number	Step	Instructions	Pass/Fail	Test Result
2	1	1	Open Software	Pass
3		2	Wait 5 minutes	Pass
4		3	Close Software	Pass
5	2	1	Open Software	Pass	Fail
6		2	Click "New"	Pass	Fail
7		3	Wait 5 minutes	Fail	Fail
8		4	Close Software	Pass	Fail
9	3	1	Open Software	Pass
10		2	Close Software	Pass
11	4	1	Open Software	Pass	Fail
12		2	Click "New"	Pass	Fail
13		3	type 5 words	Pass	Fail
14		4	Wait 5 minutes	Fail	Fail
15		5	Close Software	Pass	Fail
16
17
Fail (2)

Cell Formulas
Range	Formula
E2:E15	E2	=IF(B2=1,IF(IFNA(MATCH("Fail",D2:D$30,0),9^9)<=IFNA(MATCH(1,B3:B$30,0),9^8),"Fail",""),E1)

Hi Peter,

This is perfect. Thank you!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.