VBA to find string, search previous, return result in single column of data

[shstrating]

Using Excel for Microsoft 365 32-bit on Win10 Pro v.22H2 64-bit system.

I'm fairly experienced with Excel, but no experience with VBA other than asking this group for help and plagiarizing what is provided.

I have a single column of data that contains several groups of data.

Each group of data begins with a cell containing the string PO= as the prefix.

I need to Search the data to find instances of the string RCBM.

When an instance is found I then need to Search back up the column of data to find the first occurrence of PO= and return the string minus the PO= prefix.

I have to do this across several files, some of which contain only one instance of RCBM while others contain over 3500 instances, meaning I need to find anywhere from 1 to 3500+ PO= strings depending on the file.

Here is a very shortened example of what the data looks like:
PO=KR3TA2007-VEN-FIN_KR3TA200701
OG=Veneer Finish
ON=(UND)
IM=UNDCOL
ON=038A
OD=VEN: Rustic Walnut / Low Gloss / Closed Pore
ON=039A
OD=VEN: Pippy Oak / Low Gloss / Closed Pore
ON=V414
OD=VEN: Natural Oak RCBM

So in this example data I need to search down the column until I find RCBM and then search back up the column to find the PO= prefix and return the value KR3TA2007-VEN-FIN_KR3TA200701.

It's frustrating because I believe this is a no-brainer using VBA, but I don't even know where to start because I have no VBA knowledge.

If it's possible to do this with formulas I am also open to that.

Thanks,
Steve

 

[Peter_SSs]

@Peter_SSs: Thank you for your response. Unfortunately my data is not set up so that RCBM only occurs once and at the end of the data set.

That is the problem with only giving one example.
However, it is an easy change. See how this goes for performance.

Sub RCBM_v2()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
  Dim CurrPO As String
  
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    If Left(a(i, 1), 3) = "PO=" Then CurrPO = Mid(a(i, 1), 4)
    If InStr(1, a(i, 1), "RCBM") > 0 Then
      k = k + 1: b(k, 1) = CurrPO
    End If
  Next i
  If k > 0 Then Range("B1").Resize(k).Value = b
End Sub

 

[Peter_SSs]

If you did want to persist with a formula approach, here is another reasonably short one to consider.

=LET(f,BYROW(A1:A80000,LAMBDA(r,IF(ISNUMBER(FIND("RCBM",r)),REPLACE(XLOOKUP("PO=*",A$1:r,A$1:r,,2,-1),1,3,""),""))),FILTER(f,f<>""))

Depending on who may be using the worksheet and what might happen with it, this could have an added advantage in that it will still return the correct results if any new rows are inserted at the top of the worksheet (eg for headings)