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

 

[felixstraube]

It could be done with formulas. Depending on your version of excel you could use formulas in row 12 or 13:

Libro1
	A	B	C	D	E
1		PO=KR3TA2007-VEN-FIN_KR3TA200701	PO=AnotherText01	PO=AnotherText02	PO=AnotherText03
2		OG=Veneer Finish	OG=Veneer Finish	OG=Veneer Finish	OG=Veneer Finish
3		ON=(UND)	ON=(UND)	ON=(UND)	ON=(UND)
4		IM=UNDCOL	IM=UNDCOL	IM=UNDCOL RCBM	IM=UNDCOL
5		ON=038A	ON=038A	ON=038A	ON=038A
6		OD=VEN: Rustic Walnut / Low Gloss / Closed Pore	OD=VEN: Rustic Walnut / Low Gloss / Closed Pore	OD=VEN: Rustic Walnut / Low Gloss / Closed Pore	OD=VEN: Rustic Walnut RCBM / Low Gloss / Closed Pore
7		ON=039A	ON=039A	ON=039A	ON=039A
8		OD=VEN: Pippy Oak / Low Gloss / Closed Pore	OD=VEN: Pippy Oak / Low Gloss / Closed Pore	OD=VEN: Pippy Oak / Low Gloss / Closed Pore	OD=VEN: Pippy Oak / Low Gloss / Closed Pore
9		ON=V414	ON=V414	ON=V414	ON=V415
10		OD=VEN: Natural Oak RCBM	OD=VEN: NO text	OD=VEN: NO text	OD=VEN: NO text
11
12	Excel 365	KR3TA2007-VEN-FIN_KR3TA200701		AnotherText02	AnotherText03
13	Excel 2016	KR3TA2007-VEN-FIN_KR3TA200701		AnotherText02	AnotherText03
Hoja1

Cell Formulas
Range	Formula
B12:E12	B12	=LET( d,B1:B10, t,SUM(ISNUMBER(FIND("RCBM",d))*1), IF(t,TEXTAFTER(CHOOSEROWS(d,1),"PO="),"") )
B13:E13	B13	=IF(SUM(ISNUMBER(FIND("RCBM",B1:B10))*1),RIGHT(B1,LEN(B1)-3),"")

 

[shstrating]

@felixstraube
Thank you for your response.

I'm not exactly sure how to use the formula solution with my full data (as opposed to the miniscule example I was able to provide in a Forum post), but I'll work with it to see what I can come up with.

The reason I believe it has to be VBA is because the solution has to be able find and write out multiple results in each file.

The smallest file I'm working with is a single column of over 75,000 rows and 929 PO= groups with 76 instances of cells containing RCBM and a corresponding 76 PO='s that I have to find.
The largest file is a single column of over 19 million rows (which I'll have to chop up to get it into Excel) and 421,352 PO= groups with 3566 RCBM/PO= pairs to find.

So it's a bit of a needles in a haystack situation.

It's probable that I'm not understanding how to properly use your solution so my apologies if I'm being dense.

 

[felixstraube]

Maybe you can upload the file somewhere and post the link? Or upload a bigger sample of the data your have. And I'll see what I can come up with

 

[Peter_SSs]

Using Excel for Microsoft 365 ... on Win

Please add that information to your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Assuming ...

1. Data in column A
2. Results in column B
3. RCBM, if it exists is the last text in the line (as in your sample)

.. try this with a copy of your data.

Sub RCBM()
  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 Right(a(i, 1), 4) = "RCBM" Then
      k = k + 1: b(k, 1) = CurrPO
    End If
  Next i
  If k > 0 Then Range("B1").Resize(k).Value = b
End Sub

 

[shstrating]

@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. It's very difficult to explain in words, but the data is set up with many, many 'tables' that begin with a PO= value and continue to the next PO= value.

@felixstraube: I have uploaded the smallest of my data files to WeTransfer at this link: KRP3.txt

I just set up the WeTransfer account so I'm not sure of all the ins and outs and how long the link is valid for, but I hope it works.
I just found that the link will expire on 12/20/23 so if you cannot access it let me know and I will upload it again.

The uploaded file contains 76 instances of RCBM scattered throughout the file and 76 associated PO= values.

Thank you both so much for trying to assist on this inquiry.

 

[Fluff]

Please update your profile as requested by Peter in post#5

How about

=LET(f,FILTER(A1:A80000,(LEFT(A1:A80000,3)="PO=")+(ISNUMBER(SEARCH("rcbm",A1:A80000)))),TEXTAFTER(CHOOSEROWS(f,FILTER(SEQUENCE(ROWS(f)),ISNUMBER(SEARCH("rcbm",f)))-1),"="))

Although this sounds more like a job for power query.

 

[felixstraube]

How about this?:

VBA to find string.xlsx
	A	B
1	PO=2-FIN-OPTN	EDGEV414
2	OG=2.0 Finish Options	KR3TA2007-VEN-FIN_KR3TA200701
3	ON=B	KR3TA2007-VEN-FIN_KR3TA200702
4	OD=Black	KR3TA2007-VEN-FIN_KR3TA200703
5	O1=0	KR3TA2007-VEN-FIN_KR3TA200704
6	O2=0	KR3TA2007-VEN-FIN_KR3TA200705
7	O3=0	KR3TA2007-VEN-FIN_KR3TA200706
8	O4=0	KR3TA2007-VEN-FIN_KR3TA200707
9	O5=0	KR3TA2007-VEN-FIN_KR3TA200708
10	O6=0	KR3TA2007-VEN-FIN_KR3TA200709
11	ON=G	KR3TA2007-VEN-FIN_KR3TA200710
12	OD=Grey	KR3TA2007-VEN-FIN_KR3TA200711
13	O1=0	KR3TA2007-VEN-FIN_KR3TA200712
14	O2=0	KRW-DOOR-VEN-FIN
15	O3=0	TALL-TABLE-TAPER-LAM-EDGE
16	O4=0	TALL-TABLE-TAPER-V414-EDGE
17	O5=0	TALL-TABLE-TAPER-VEN-FIN_KR3TA201401
18	O6=0	TALL-TABLE-TAPER-VEN-FIN_KR3TA201402
19	ON=W	TALL-TABLE-TAPER-VEN-FIN_KR3TA201403
20	OD=White	TALL-TABLE-TAPER-VEN-FIN_KR3TA201404
21	O1=0	TALL-TABLE-TAPER-VEN-FIN_KR3TA201405
22	O2=0	TALL-TABLE-TAPER-VEN-FIN_KR3TA201406
23	O3=0	TALL-TABLE-TAPER-VEN-FIN_KR3TA201407
24	O4=0	TALL-TABLE-TAPER-VEN-FIN_KR3TA201408
25	O5=0	TALL-TABLE-TAPER-VEN-FIN_KR3TA201409
26	O6=0	TALL-TABLE-TAPER-VEN-FIN_KR3TA201410
27	PO=3-FIN-OPTN	TALL-TABLE-TAPER-VEN-FIN_KR3TA201411
28	OG=3.0 Finish Option	TALL-TABLE-TAPER-VEN-FIN_KR3TA201412
Hoja1

Cell Formulas
Range	Formula
B1:B76	B1	=LET(rd,A1:A75669, r_hasRCBM,FILTER(ROW(rd),ISNUMBER(FIND("RCBM",rd))), r_hasPO,FILTER(ROW(rd),ISNUMBER(FIND("PO=",rd))), textIndex,BYROW(r_hasRCBM,LAMBDA(x,MAX(FILTER(r_hasPO,r_hasPO<x)))), TEXTAFTER(CHOOSEROWS(rd,textIndex),"PO=") )
Dynamic array formulas.

 

[shstrating]

@Fluff: Sorry about that. I haven't visited the Board in a long time and I'm not sure Office Version and Platform options were available back then. I did put the detailed info into my Signature line, thinking that's where it belonged. I've now updated the rest of my Account so should be all good now.
I will try your solution and thank you for the feedback.

@felixstraube: I just tried out your solution on another data file that I know contains 267 RCBM/PO= pairs and your formula nailed it.
Thank you so much!

I am constantly telling people how great this group is.

Thank you to everyone who contributed to my inquiry.

 

[felixstraube]

Happy to help. Thanks for the feedback!