Modifying LET formula to include an additional data check

[shstrating]

I was graciously provided a LET formula by member felixstraube that I would now like to modify to include an additional check of the data and only return values that match this extra condition.

Here is the original LET formula: =LET(rd,$A$1:$A$131788, r_hasCCP,FILTER(ROW(rd),ISNUMBER(FIND("IM=LAM124",rd))), r_hasPO,FILTER(ROW(rd),ISNUMBER(FIND("PO=",rd))), textIndex,BYROW(r_hasCCP,LAMBDA(x,MAX(FILTER(r_hasPO,r_hasPO<x)))), TEXTAFTER(CHOOSEROWS(rd,textIndex),"PO=") )

I admit I do not fully understand how the textIndex BYROW calculation works or how the CHOOSEROWS in the final calculation works, but the formula got me the results I needed.
It queries my column of data to find every instance of "IM=LAM124" and then it looks backwards from IM=LAM124 and returns the value of the corresponding PO= field minus the "PO=" prefix.
I have the same single column of data that I now want to query for a slightly different condition with a slightly modified output.

So now I would like to modify this formula to do the following:
- Perform the same calculation as the original "r_hasCCP" function (to be renamed "r_hasUND") but looking for the string "ON=(UND)" instead of "IM=LAM124". (I know how to do this)
- When "ON=(UND)" is found then check the 9th row following that record and determine whether the string begins with "PO". (I don't know how to do the "...check the 9th row following..." bit)
- If "PO" is found, look backwards and return the value of the "PO=" entry that comes before the "ON=(UND)" that was found previously. (Maybe the existing TEXTAFTER calc in the original formula would still work?)
- If "PO" is not found, do nothing.
- Continue down the column of data performing the same steps.

By way of explaining the data a bit, all records that begin with "PO=" are what we call PO Tables.
Each PO= record is the beginning of a new PO table.
What I am trying to accomplish is to get a list of every PO Table that contains only one "ON=" record where that one "ON=" record must equal "ON=(UND)".
There may be PO Tables that contain only one "ON=" record that is not equal to "(UND)" and these are to be ignored.

In the following set of example data when the modified LET function (or other solution) is working properly it should return the result shown in C1 because the 9th row after the ON=(UND) in ColB Row28 begins with "PO".
DPGP-HVGRD-124 would be an incorrect result because the 9th row after the ON=(UND) in ColB Row5 begins with "ON".

Thanks in advance for any assistance offered.

kbe3-NEW.xlsx
	B	C
1	PO=DPGP-HVGRD-124	DPGP-HVGRD-125
2	OG=Grain Direction
3	POLA=AFUFU-3D-GS-SD1
4	POLA=AFUFU-3D-GS-SD2
5	ON=(UND)
6	IM=UNDCOL
7	OD=*** Undecided Color ***
8	O1=0
9	O2=0
10	O3=0
11	O4=0
12	O5=0
13	O6=0
14	ON=H
15	IM=LAM124
16	OD=OPT: Horizontal Grain Direction
17	O1=0
18	O2=0
19	O3=0
20	O4=0
21	O5=0
22	O6=0
23	SO=DPGP-LAM-EDGE
24	PO=DPGP-HVGRD-125
25	OG=Grain Direction
26	POLA=AFUFU-3D-GS-SD1
27	POLA=AFUFU-3D-GS-SD2
28	ON=(UND)
29	IM=UNDCOL
30	OD=*** Undecided Color ***
31	O1=0
32	O2=0
33	O3=0
34	O4=0
35	O5=0
36	O6=0
37	PO=DPGP-HVGRD-126
kbe

 

[Cubist]

Try:

=LET(d,B1:B37,r,IF(LEFT(d,8)="ON=(UND)",ROW(d)),TOCOL(TEXTAFTER(IF(LEFT(INDEX(d,r+9),3)="PO=",INDEX(d,r-4)),"PO="),2))

 

[Cubist]

Minor change to make this more robust if data isn't always starting from row 1.

=LET(d,B1:B37,r,IF(LEFT(d,8)="ON=(UND)",SEQUENCE(ROWS(d))),TOCOL(TEXTAFTER(IF(LEFT(INDEX(d,r+9),3)="PO=",INDEX(d,r-4)),"PO="),2))

 

[shstrating]

@Cubist: Thank you for that formula!
It works a treat!
Now I just need to spend some time trying to 'reverse engineer' your hard work to try to understand how it works.

 

[shstrating]

Well, I spoke too soon.
That LET formula is not working when run against something other than my sample data.
I haven't sorted out how the formula is working yet in order to troubleshoot, but I suspect the issue is because of one thing I neglected to mention in my original post.
The PO= record will not always be 4 rows above the 'orphaned' ON=(UND) record and I am guessing that this is tripping up the formula.
The PO= record will never be less than 2 rows above the ON=(UND) record, but it could be any number of rows greater than 2 rows above the ON=(UND) record.
Here are three examples showing how much that row count can change within a single file.

ks2.3
	A	B
1	PO=BS2C22-BLL-LAM-FRNT_BS2C2230BLL
2	OG=Laminate Front Finish
3	POLA=AFUFU-3D-SC-DF-H
4	POLA=AFUFU-3D-SC-DF-V
5	POLR=90
6	POLA=AFUFU-3D-SP-DF-H
7	POLA=AFUFU-3D-SP-DF-V	11 rows
8	POLR=90
9	POLA=AFUFU-3D-SC-TP-H
10	POLA=AFUFU-3D-SC-TP-V
11	POLR=90
12	ON=(UND)
13	IM=UNDCOL
14	OD=*** Undecided Color ***
15	O1=0
16	O2=0
17	O3=0
18	O4=0
19	O5=0
20	O6=0
21	PO=BS2C22-BLL-LAM-TOP_BS2C2230BLL
22	OG=Laminate Top Finish
23	POLA=AFUFU-3D-WS	3 rows
24	ON=(UND)
25	IM=UNDCOL
26	OD=*** Undecided Color ***
27	O1=0
28	O2=0
29	O3=0
30	O4=0
31	O5=0
32	O6=0
33	PO=CORE-PNT_BS2DW18O
34	OG=Paint Finish
35	POLA=AFUFU-3D-SV-DF-H
36	POLA=AFUFU-3D-SV-DF-V
37	POLR=90
38	POLA=AFUFU-3D-SV-SU
39	POLA=AFUFU-3D-SV-DF
40	POLA=AFUFU-3D-SC-TP-H
41	POLA=AFUFU-3D-SC-TP-V
42	POLR=90
43	POLA=AFUFU-3D-SC-DF-H
44	POLA=AFUFU-3D-SC-DF-V	22 rows
45	POLR=90
46	POLA=AFUFU-3D-SC
47	POLA=AFUFU-3D-SC-DF
48	POLA=AFUFU-3D-SP
49	POLA=AFUFU-3D-SP-DF
50	POLA=AFUFU-3D-SP-DF-H
51	POLA=AFUFU-3D-SP-DF-V
52	POLR=90
53	POLA=AFUFU-3D-SV
54	POLA=AFUFU-3D-SV-BK
55	ON=(UND)
56	IM=UNDCOL
57	OD=*** Undecided Color ***
58	O1=0
59	O2=0
60	O3=0
61	O4=0
62	O5=0
63	O6=0
Sheet1

 

[Cubist]

Ok, How about this?

=LET(d,A1:A100,c,"PO=",r,IF(LEFT(d,8)="ON=(UND)",SEQUENCE(ROWS(d))),
TEXTAFTER(FILTER(SCAN(0,d,LAMBDA(a,b,IF(LEFT(b,3)=c,b,a))),IF(ISNUMBER(r),LEFT(INDEX(d,r+9),3)=c)),c))

 

[shstrating]

@Cubist: You are amazing!
Hot dang! You nailed it!
Thank you again!

 

[Cubist]

You're welcome.