Elegant and customizable searching? (More VBA, less cell formula...) Maybe access?

[jose32]

Hi, first post on this site for me!

I have created a useful and what I think is user-friendly search facility so a user can try and locate the correct item, but I am wondering if there is a more elegant means to do the same using less cell formula and purely VBA (at the moment it is a mix).

I have tried a VBA approach previously (but I think it was a little clumsy) and resulted in a very long time to calculate in comparison to my more functional approach where a result is near-instant, but involves much more cell formula instead.

About the file:

- The list of over 7000 items has 5 fields (columns), order code, other number, long and short descriptions etc.
- The user inputs text to search for an item e.g. "pipe motor ABC" (that's 3 search terms)

- The user can choose which of the fields to search using 5 check boxes.
- For each item in the 7000, a concatenated string is calculated for all the selected fields.

- VBA code splits the users search into words (search terms, maximum of 10).

- Using search() I have a 10x7000 cell range that checks whether each word is in the concatenated data for that item/row.
- Each item in the 7000 then has a sum cell where the result of how many of the search terms are included in the selected fields (held in the concatenated string).

- VBA code then copies all fields of the items that match the criteria, starting with those with the highest value of matches in the selected fields.


In the current format as I say, it calculates instantly which I think is quite good - the only downfall is increased file size compared to more VBA code...? and more cells to protect the user from editing!

I have also easily included added flexibility for searching, like all uppercase words MUST be included in matched items and to only return results that have a minimum of X search terms included, and match all search terms.

Umm, hopefully you can sort of understand that? I'm sure there must be an easy efficient and fast way of doing this, perhaps though it's using Access instead?

My general approach is to get something that is functional, but it is usually by no means the best and often is revised drastically various times afterwards.

Any help of thoughts would be greatly appreciated.

Jo

[Leith Ross]

Hello jose32,

There are many ways to extract and parse information using VBA. The best method depends on the data layout and the results you need.It would help if posted your workbook for review. If it contains any sensitive information, please redact it before posting.

[jose32]

I've added an example workbook to show how the data is held and formatted with a few notes.

Jo

[mikerickson]

You don't show any expected results in your attachment.

Splitting the space delimited string into its separate search terms doesn't require VBA.

If the user enters the search term in Sheet1!B1, you could define these names

Name:DString
RefersTo: =TRIM(Sheet1!$B$1)

Name: TermCount
RefersTo: =LEN(DString)-LEN(SUBSTITUTE(DString," ",""))+1

Name: OneToTermCount
RefersTo: =ROW(INDEX(Sheet1!$B:$B,1,1):INDEX(Sheet1!$B:$B,TermCount,1))

Name: SearchTerms
RefersTo: =TRIM(MID(SUBSTITUTE(SUBSTITUTE(" "&DString," ",REPT(" ",255),OneToTermCount+1)," ",REPT(" ",255),OneToTermCount),255,255))

SearchTerms is a (column-wise) array of the search terms.

If you assign each of the checkboxes to a cell (Sheet1!B5:F5 in the attached) and name that range
Name: ChosenFields
RefersTo: =Sheet1!$B$5:$F$5

The formula
=SUMPRODUCT(--ISNUMBER(FIND(" "&searchTerms&" ", " "&REPT(B10:F10,ChosenFields)&" ")))
will count how many of the search terms are found in the selected fields of the data in row 10

Adding a helper column to the data and then sorting on that column will go a long way to getting what you want.

[jose32]

Hi and thanks for the reply, the expected results are not shown, but they would be in the Search worksheet (Fields1-5) in the table at the bottom - this should display all matched items (as commented to the right).

My methods so far have created file sizes that are about 5 times the size of the data itself without my search bit on top. (ie if I remove the cells to do with searching the file is 80% smaller). So I am happy to do a cell formula based approach - if it keeps the file size down.

I will have a look at the file you attached, probably not till Monday (I have an important deadline to meet on Monday!)

Many thanks again for your reply and post back asap.

Jo

[jose32]

mikerickson, that is a really great help!

I think I get most of it, but a few points will take a bit of looking into...

Thanks very much, this will definitely be the way I'll move forward with the next file version

Jo

[jose32]

I've created a file that does most of what I did before using this new method (thanks mikerickson!)

The file size has reduced by over 80% as a result!

The downside is the time it takes Excel to use With .sort, it takes several seconds which is so much longer than my previous method.

For a number of reasons I want to speed this up, one being that the number of rows of data might increase to say 20-30,000 eventually.

The other thing is, only data with matches needs to be sorted - not the entire set of 1000s of rows, so I was thinking of having all of the data in another sheet and using VB (or another method) to add in the matched results (starting with the highest matches).

Any alternative suggestions on this?

Thanks

Jo