Excel formula to show above and below row in the next column

[mar_luna]

Hi,

I have tried several formulas and I cant find a way around.
I need to show/filter and above and below row from one cell . I need to do this in the next column.
I have a formula that is called gap and if the cell is YES, the in the next column I have to do a formula that shows the row above and below YES. hopefully, my explanation make sense. Any help will be highly appreciated it,

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Is one of these what you are after?

mar_luna.xlsm
	A	B	C	D
1
2
3
4	Step	GAP	Show	Show2
5	6.76
6	8.47		show	show
7	4.96	YES
8	5.17		show	show
9	4.16
10	0.32		show	show
11	7.98	YES	show
12	3.44	YES	show
13	8.62		show	show
14	0.89
15	7.18
16	2.03		show	show
17	4.16	YES
18	0.32		show	show
19	7.98	YES
20	3.44		show	show
21	8.62
22
23
Sheet1

Cell Formulas
Range	Formula
C5:C21	C5	=IF(OR(B4="YES",B6="YES"),"show","")
D5:D21	D5	=IF(AND(OR(B4="YES",B6="YES"),B5<>"YES"),"show","")

 

[mar_luna]

Welcome to the MrExcel board!

For the future, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Is one of these what you are after?

mar_luna.xlsm
	A	B	C	D
1
2
3
4	Step	GAP	Show	Show2
5	6.76
6	8.47		show	show
7	4.96	YES
8	5.17		show	show
9	4.16
10	0.32		show	show
11	7.98	YES	show
12	3.44	YES	show
13	8.62		show	show
14	0.89
15	7.18
16	2.03		show	show
17	4.16	YES
18	0.32		show	show
19	7.98	YES
20	3.44		show	show
21	8.62
22
23
Sheet1

Cell Formulas
Range	Formula
C5:C21	C5	=IF(OR(B4="YES",B6="YES"),"show","")
D5:D21	D5	=IF(AND(OR(B4="YES",B6="YES"),B5<>"YES"),"show","")

Thank you so much Peter. I'm going to explain myself better.
1. I can't install XL2BB (work computer)
2. I have a table with more than 500,000 rows.
3. I need to show the rows above and below YES. Please note that this is NOT the order, I have the "YES" in several other random cells.

Something like this:

 

[Peter_SSs]

1. I can't install XL2BB (work computer)

Fair enough. In any future threads you start, tell us that in the first post so we don'y keep asking.

Something like this:

You did not give an example of what you want if 2 or more cells in a row have "YES". Is that possible with your data?

Something like this:

In what way do my formulas in my previous post fail for you? Both of them provide those exact results for the sample that you have now given.

mar_luna.xlsm
	A	B	C
1	GAP	Show	Show2
2		Show	Show
3	YES
4		Show	Show
5
6		Show	Show
7	YES
8		Show	Show
9
10
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=IF(OR(A1="YES",A3="YES"),"Show","")
C2:C8	C2	=IF(AND(OR(A1="YES",A3="YES"),A2<>"YES"),"Show","")

 

[mar_luna]

Fair enough. In any future threads you start, tell us that in the first post so we don'y keep asking.


You did not give an example of what you want if 2 or more cells in a row have "YES". Is that possible with your data?


In what way do my formulas in my previous post fail for you? Both of them provide those exact results for the sample that you have now given.

mar_luna.xlsm
	A	B	C
1	GAP	Show	Show2
2		Show	Show
3	YES
4		Show	Show
5
6		Show	Show
7	YES
8		Show	Show
9
10
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=IF(OR(A1="YES",A3="YES"),"Show","")
C2:C8	C2	=IF(AND(OR(A1="YES",A3="YES"),A2<>"YES"),"Show","")

Hi Peter,

It doesn't. Thanks for your help I managed to did myself.

 

[Peter_SSs]

Hi Peter,

It doesn't.

Thanks for the confirmation.