VB for Searching for ~ 20 words in a large table of text

[BobTheSpreadsheetBuilder]

Hi

I am wondering if anybody knows how to replace a grid of formulas with a VBA script ?

I am very familiar using formulas along with VB to obtain results in my spreadsheets. In my current project I

am using a grid of formulas in 20 columns x 20,000 rows to locate and extract data in the data sheet.

It is effectively a complex Search function, with 20 search cells, for up to 20 words at a time.
A typical use case is: you can place a novel with up to 20,000 lines of text in the data sheet. You can enter up

to 20 search words: each word has its own column to Search for that word in each Row.

There is a 21st column that has a formula that equals 1 if the other 20 columns on that row contain a result .

My VB script acts on the 21st column only, looping through the 20k rows, and where it finds a 1, copies the whole row to a Results sheet.

The problem is, while the VB scripts in my project are ~30 KB, the table of 20 x 20,000 formulas add ~15MB to the file size.

I had considered incorporating the formula into the VB script using Application.WorksheetFunction , however that might seriously slow down the script. The formula is long, 2x screen widths of the standard formula Bar and took weeks to construct. Currenltly, Excel natively updates the formula table in about ~10 seconds on first opening, and the VB loops on the 20,000 cells in Col 21 in ~3 seconds.

(The Data table has Range ("A11:A20010") : Search words are in a Range ("A1:T1")

Does anyone know if there's a way, possibly adding the 20 search words to an Array, to quickly check for matches in 20,000 lines, each line containing up to ~100 words, like comparing 2 arrays ? I'm not very familiar with manipulating Arrays. i read somewhere about Intersection or Union of 2 arrays but unsure if its applicable.

My current formulation was slow to set up, however it is methodical and I could progress stepwise over many evenings in a logical fashion. I feel my approach is clunky and old fashioned and I am missing out on a more agile way of getting results and shrinking my file size.

Could someone demonstrate a small example for say a 20 x 20 data table ?

Thanks for any advice on this problem

Rob

 

[mumps]

I can't promise you a solution but Forum members may find it easier to help if you could use the XL2BB add-in (icon in the menu) to attach the desired selected range (not a picture) of your data. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Snakehips]

@BobTheSpreadsheetBuilder Rob, as @mumps has posted, for any member to offer you, even in principle, a worthwhile alternative to what you already have they will need a far better understanding of what you have now and how you use it. Maybe if your methodical, albeit arguably 'clunky' formula / vba approach does the job without any significant issues, you should stick with it?

That said, I do perhaps have a suggestion that might be of value in respect of significantly reducing your file size. Hopefully the below will illustrate the principle?
My example assumes:
Search words to be in B1:U1.
Text to be searched to be A11: A20010 as a MAXIMUM range
*** Your formulas to be Permanent in B11:V11

The vba below will copy formulas from row 11 down only as far as data exists in A and then converts all formulas bar row 11 into values.
Your current vba can then work with those values?
File size will be significantly smaller by saving hard values rather than big formulas.

How you work with your workbook and how will determine if and when you could function with hard values rather than dynamic formulas.
Maybe you add something like it to your current vba?
Maybe you adapt it to call it independently, as and when you need to switch formulas and values?
Maybe have it install the formulas on opening and you accept the file size while working?
Then it could convert the formulas to values on closing the file in order to reduce file size?

Who knows?

TestTime.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Search Words >>>	random	Word2	cat	Word4	Fig	Word6	This	Word8	Word9	MAT	Word11	flying	Word13	Word14	Word15	Word16	Word17	Word18	Word19	Word20
2
3
4
5
6
7
8
9																						Result to be
10																						used in vba??
11	This is Just a load of random rubish	1						1														1
12	More random rubish	1																				1
13	The cat sat on the mat			1							1											1
14	The dog was not pleased , as it was his mat										1											1
15	The cat didn't give a flying fig			1		1							1									1
16	Surely I have something better to do on aSunday evening?
17	More rubish
18	More rubish
19	More rubish
20	More rubish
21	More rubish
22	More rubish
23	More rubish
24	More rubish
25	More rubish
26	More rubish
27	More random rubish	1																				1
Sheet6

Cell Formulas
Range	Formula
B11:U11	B11	=IF(IF(OR(B$1="",$A11=""),"",ISNUMBER(FIND(UPPER(B$1),UPPER($A11),1))),1,"")
V11	V11	= IF(SUM(B11:U11)>0,1,"")

Sub ReduceFileSize()
Dim lrw As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

lrw = Range("A" & Rows.Count).End(xlUp).Row 'last data row determined b content in column A
If lrw < 12 Then Exit Sub  ' guard against no data in A11:
Range("B12:V20010").ClearContents  'clear to max of 20Krows of data
Range("B11:V" & lrw).Formula = Range("B11:V11").Formula  'extend row 1 formulas to last row
Range("B12:V" & lrw).Value = Range("B12:V" & lrw).Value  'change formulas to hard values in ALL BAR ROW 11 **

'Do some stuff ???
'?????
For Each cell In Range("V11:V" & lrw)
    '?????
        If cell.Value = 1 Then Count = Count + 1
Next cell
     '?????
     
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

HTH

 

[BobTheSpreadsheetBuilder]

Hi Snakehips,

thank you for replying - This is exactly what I was looking for, so thank you for spending your Sunday afternoon working on it

I haven't seen .Formula before so it certainly makes things a lot simpler ( I thought of using autofill but your .Formula method looks neater) and changing the formulas to fixed values will make the file a lot more compact.

I will spend a few days moving it into my project - lots of formulas so I will double check when I integrate it..

"The cat sat on the mat" ha ha yes i have used sample data like that .. " The dog was not pleased as it was his mat" LoL ... well that's certainly very imaginative !!!

Thank you and enjoy the rest of your Sunday

Rob