Search for key words from 1 field and return a value from another

wesimmo

Board Regular
Joined
Nov 23, 2005
Messages
149
I have an Excel spreadsheet i knocked up that searches text strings for key words and then returns a value dependent on what value is found.

e.g. if it finds "excl", "exclusive" or "Exc" it returns that the line item I'm looking at is an "Exclusive".

There are quite a few of these key words i am looking for and the report is being used and updated regularly as more data becomes available so there is now too much data for Excel to handle, therefore i want to build the same thing in Access but don't know how.

What i want ideally is to have a table of key words to search for and then in a second field the "grouping" that the line item should be assigned.

i can then write a query that says if the "Order Description" or the "PO Description" in Table A contains any of these words in Field 1 of Table B return the value in Field 2 of Table B? if that makes sense?
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
I'm not coming up with any great ideas. Fuzzy matching is not really my thing, so all I can think of is to write a function that would use a lot of Like statements. Whether the data is stored in a table is immaterial but *might* make it easier for end users to add to or remove from. Then your function would have to read from the table as it's first step. It's unclear whether this would really provide you with any superiority over Excel - it all depends on the details and how you would implement your processes.
ξ
 

wesimmo

Board Regular
Joined
Nov 23, 2005
Messages
149
I've found a way that works when i tested it briefly last night, I'm going to work further on it today as I'm not sure what will happen should a line item contain 2 key words, and i need to check 2 fields for key words as opposed to the one that I have got it working for.

Code:
SELECT data.[PO Desc], lookups.Return
FROM data, lookups
WHERE data.[PO Desc] LIKE "*" & lookups.lookup & "*";

edit: The problem with Excel is that i now have over 250,000 records and adding about 25k a week, which when i then do matches etc across about 20 key words causes my PC to throw a wobbly, given it's an i5 clocked to 4.6ghz and 16GB of RAM it's obvious Excel isn't the solution for this job!
 
Last edited:

wesimmo

Board Regular
Joined
Nov 23, 2005
Messages
149
OK, i've got it almost there but i just need to finish it off and I'm struggling.

My first query simply checks for the key words and if it finds them returns a value.

Code:
SELECT [Weekly Data].ID, Keywords.Return
FROM Keywords, [Weekly Data]
WHERE ((([Weekly Data].Provider) Like "*" & [keywords].[lookup] & "*"));

I then use another query to use either the returned value or if there wasn't one, the original Provider.

Code:
SELECT [Weekly Data].ID, IIf([Q_Key_Search]![Return]<>"",[Q_Key_Search]![Return],[Weekly Data]![Provider]) AS Act_Provider
FROM [Weekly Data] LEFT JOIN Q_Key_Search ON [Weekly Data].ID = Q_Key_Search.ID;

I now want to get a unique record for each [Weekly Data].ID, at the moment if 2 keywords are found then there are 2 records for that ID.

I will order the keywords in priority order and then order the first query based on that.

How do i then get only the first record produced for each [Weekly Data].ID?
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Hi,

Just in your second query
Code:
SELECT DISTINCT ...

I think that would probably work for you in this case.
 

wesimmo

Board Regular
Joined
Nov 23, 2005
Messages
149
Thanks, but I couldn't get DISTINCT to work.

I have got there, but I'm using 4 Queries....

The first checks if there is a match on Keywords.

Q_Key_Search
Code:
SELECT [Weekly Data].ID, Keywords.Return
FROM Keywords, [Weekly Data]
WHERE ((([Weekly Data].Provider) Like "*" & [keywords].[lookup] & "*"));

The second then brings back either the normalised provider if i found a match, or the original value if not.

Q_Consol
Code:
SELECT [Weekly Data].ID, IIf([Q_Key_Search]![Return]<>"",[Q_Key_Search]![Return],[Weekly Data]![Provider]) AS Act_Provider
FROM [Weekly Data] LEFT JOIN Q_Key_Search ON [Weekly Data].ID = Q_Key_Search.ID;

The third then gets rid of duplicates, bringing back the first value in the consolidated provider field.

Q_Dedupe
Code:
SELECT Q_Consol.ID, First(Q_Consol.Act_Provider) AS FirstOfAct_Provider
FROM Q_Consol
GROUP BY Q_Consol.ID;

The fourth is the end result, how many records per normalised provider.

Q_Count
Code:
SELECT Count(Q_Dedupe.ID) AS CountOfID, Q_Dedupe.FirstOfAct_Provider
FROM Q_Dedupe
GROUP BY Q_Dedupe.FirstOfAct_Provider
ORDER BY Count(Q_Dedupe.ID) DESC;

I'm sure there's a more elegant solution but it works....
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
I was assuming the ID was the same regardless of how many records are returned in Query 2. That must not be the case. What does these duplicates look like in Query 2?
 

wesimmo

Board Regular
Joined
Nov 23, 2005
Messages
149
It's tricky to explain, but.....

There's a series of Groups i want to put the records in based on text strings in a couple of fields, but those groups have a sort of priority.

Whether it's an "Exclusive" it is priority 1, whether it's a "Ltd Dist" is priority 2.

So if it meets the criteria for both the above, which is possible, i want it to be tagged as "Exclusive".

Therefore the first query creates 2 records where a record in the source date meets both criteria.

Sorry if that doesn't explain, I've been out.....
 

Forum statistics

Threads
1,137,060
Messages
5,679,376
Members
419,824
Latest member
Mercy kiara

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top