Dynamic Text Search Help

[dronka]

I have a very large data set (38,000 rows). I've created a sample spreadsheet (attached) that is much smaller, but that illustrates what I want to do. Table 1 is my data set. Table 2 is a table that contains a list of words that I want to use to search Table 1 with.

I'd like a function for the Flag field in Table 1 that returns a "1" if the text string in the Text column in Table 1 contains any of the words in Table 2. I'd like to be able to add words to Table 2 and automatically have Table 1 return a "1" for any new words that are added.

Because the data set is so large, the function needs to be optimized for performance.

Thanks!
David

 

[Smallman]

Hi Dronka

Give the following a try.

=IFERROR(LOOKUP(2^15,SEARCH($D$4:$D$7,A4),$E$4:$E$7),"")

Answer on the NoTable sheet. It was not working as expected with the tables so I got rid.

Take care

Smallman

 

[Somendra Misra]

Hi,

Another formula can be:

=MAX(IFERROR(IF(ISNUMBER(SEARCH(Table2[Search values],[@Text])),1,0),0))

See the attached file. I had also left a macro to do the same task, in case formulas get you slow try it.

Just advise on the performance part.

Regards,

 

[dronka]

Thank you both for these functions. I've tried Smallman's and it works well.

Somendra - It looks like you're using an array formula. My experience with arrays is that they slow things down. What's your experience? And do you think your solution will have higher performance?

 

[Somendra Misra]

Hi dronka,

Yes it's an array formula, Array formula slows down depending upon the formula construct, based on those results are other refrences connected, CF on them, system configuration . I tried creating duplicates of your table with table1 of around 50000 rows and table 2 of around 11000 rows. Above both formula took approximate same time.

If you see my file it has a VBA code also, you can try that also.

Regards,

 

[dronka]

Thank you, SM.