Find cells which are within 3 non-blank cells of another (with an offset)

[ellison]

Hi, we are trying to find which cells in Column D are within 3 non-blank cells of entries in Column B, and mark them up in Column E.
... above and below of the entries in Col B

On the table, we've put Verdict in Col E.

In other words, we are trying to find...
- for the entries in Header-1
- where the next 3 non blank cells in the Info Column (D) are
- both above and below
- And mark those cells in the Verdict Column (E)

eg Cell E2 and E3 will not be marked because...
- the nearest entry in Header-1 is in cell B10
- and the nearest entries in Info Column D (so above cell D10) are D8, D6 and D4
- therefore cells E8, E6 and E4 are marked to be included in the Verdict Column (E)

I've tried searching for "next non-blank cells", "offsets" and all sorts but I'm going around in circles...

Wondered if somebody could take a look?

Thanks

mark-up-if-within-5-rows-above-or-below-which-are-NOT-blank.xlsm
	A	B	C	D	E
1	Row	Header-1	Header-2	Info	Verdict
2	2		ALPHA	Info
3	3		beta	Is
4	4		Charlie	Scattered	y
5	5		57
6	6		94	Throughout	y
7	7		Delta 50
8	8		Echo	Here	y
9	9		Foxtrot
10	10	Golf	Golf
11	11		Hotel
12	12		India
13	13		One
14	14	Two	Two
15	15		1	With	y
16	16		2
17	17		3	Lots	y
18	18		Four
19	19		Five	Of	y
20	20		Six
21	21		Juliet	Blanks
22	22		Kilo	and
23	23		London
24	24		Mike
25	25		November
Sheet1 (2)

 

[Peter_SSs]

I have read through that several times and am still not sure I understand, but you can see if this meets the requirements.

23 10 22.xlsm
	B		D	E
1	Header-1		Info	Verdict
2			Info
3			Is
4			Scattered	y
5
6			Throughout	y
7
8			Here	y
9
10	Golf
11
12
13
14	Two
15			With	y
16
17			Lots	y
18
19			Of	y
20
21			Blanks
22			and
23
24
25
Sheet5

Cell Formulas
Range	Formula
E2:E25	E2	=IF(AND(D2<>"",OR(IFNA(COUNTIF(D2:INDEX(D2:D$100,XMATCH("*",B2:B$100,2)),"?*")<=3,),IFNA(COUNTIF(D2:INDEX(D$2:D2,XMATCH("*",B$2:B2,2)),"?*")<=3,))),"y","")

 

[ellison]

Hi Peter, huge thanks for looking into this Peter - and firstly apologies, I tried drafting & re-drafting that question but I totally get that it still looks "a bit vague" (understatement!).

I've looked into trying to use the formula but sorry to say that it's throwing up a couple of surprises if / when I change some of the entries around.

For example if I keep everything the same apart from change the entries in Header-1 to this, then I've got the following cells which I think would appear differently:

Book3
	A	B	C	D	E	F
1	Row	Header-1	Header-2	Info	Formula
2	2	ALPHA	ALPHA	Info	y	y
3	3		beta	Is	y	y
4	4	Charlie	Charlie	Scattered	y	y
5	5		57
6	6		94	Throughout		This should by "y" as it it is within 3 non blank Col-D-cells below row 4 where next entry is in Col B
7	7		Delta 50
8	8		Echo	Here		This should by "y" as it it is within 3 non blank Col-D-cells below row 4 where next entry is in Col B
9	9		Foxtrot
10	10		Golf
11	11		Hotel
12	12		India
13	13		One
14	14		Two
15	15		1	With
16	16		2
17	17		3	Lots	y	y
18	18		Four
19	19		Five	Of	y	y
20	20		Six
21	21	Juliet	Juliet	Blanks	y	y
22	22		Kilo	and		This should by "y" as it it is within 3 non blank cells below row 21 where next entry is in Col B
23	23		London
24	24		Mike
25	25		November
1-orig

Cell Formulas
Range	Formula
E2:E25	E2	=IF(AND(D2<>"",OR(IFNA(COUNTIF(D2:INDEX(D2:D$100,XMATCH("*",B2:B$100,2)),"?*")<=3,),IFNA(COUNTIF(D2:INDEX(D$2:D2,XMATCH("*",B$2:B2,2)),"?*")<=3,))),"y","")

I'm wondering if I should break this problem down a bit more so that I can explain it more clearly?

Or I could try one more go using the above info if that may help?

- Our info is arranged in rows.
- And for the entries in Header-1 (Col-B), we are trying to find the next 3 non-blank cells in the info column above or below it. And mark those cells with a "y" in Column E (aka "Formula")
- So using the above example, cell B4 has entry (Charlie). The next 3 non-blank cells above it are D4, D3 and D2. So therefore (as your formula has done), the entries in cells E4, E3 and E2 are all "y".
- But...
- the next 3 non blank cells in Col D that are below row 4 are D4, D6 and D8. D4 is already being found by the formula. But D6 and D8 and not being picked up.

I hope this makes a bit more sense.

And huge thanks for persevering with this Peter (I've re-drafted this answer about a dozen times as well, so apologies for my delay in getting back to you!)

Warm Regards

 

[Peter_SSs]

Oops, I forgot to tell the second XMATCH to look upwards instead of downwards.
See if this is better.

ellison.xlsm
	B		D	E
2	ALPHA		Info	y
3			Is	y
4	Charlie		Scattered	y
5
6			Throughout	y
7
8			Here	y
9
10
11
12
13
14
15			With
16
17			Lots	y
18
19			Of	y
20
21	Juliet		Blanks	y
22			and	y
23
24
25
Sheet2

Cell Formulas
Range	Formula
E2:E25	E2	=IF(AND(D2<>"",OR(IFNA(COUNTIF(D2:INDEX(D2:D$100,XMATCH("*",B2:B$100,2)),"?*")<=3,),IFNA(COUNTIF(D2:INDEX(D$2:D2,XMATCH("*",B$2:B2,2,-1)),"?*")<=3,))),"y","")

 

[ellison]

Absolutely AMAZING - thank you so much for so much working on this, really appreciated!

 

[Peter_SSs]

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