Need Help!! Want to search through 3 columns for a list of keywords

[The Moose]

I have three columns -- product-name, company-category,
product-description.

I'd like to search through these three columns for a list of keywords.
If the keyword or keywords exist, I want to enter it into a list.

For example, search through column1 & column2 & column3 for the words
basket, vase, wicker, red and return the matching words; e.g.:
basket, wicker, red
vase, red

Some products might fall into 6 or 8 different keywords. I only need
to get 3 or 4 of them into a list.

I've been looking at match, vlookup, index -- I'm assuming it's
probably some combination of these. I just can't get my head around
it.

Oh, the other problem is column3 (product-description) contains HUGE
volumes of text sometimes. I didn't create this thing. I only need to
search through the first 100 or so characters. I've seen a way to
limit the search on a large text field so you don't run into that
famous/helpful "formula is too long" message. Can't remember where I
saw it though.

Thanks.

Barb

 

[PY & Associates]

Does not seem to be too difficult but the sample is too short to see the
details you require.

Cheers

 

[The Moose]

I'd like to search through these three columns for a list of keywords.
If the keyword or keywords exist, I want to enter it into a list.

For example, search through column1 & column2 & column3 for the words
dolphin, clock, fish, hook, bank, pig and return the matching words;
e.g.:
--hook, dolphin, clock, fish
--bank, pig

Some products might fall into 6 or 8 different keywords. I only need
to get 3 or 4 of them into a list.


Column One:
CAST IRON HOOK - WHITE DOLPHIN/FISH SPECIAL PRICE!
LAND-ARTISTIC DOLPHIN FIGURE
DOLPHIN DISH BLUE
CLOCK DOLPHINS SET/3
FISH FRESH WATER SET OF 2
PAIR OF GREEN PIG BANKS
LARGE PEANUT BANK SPECIAL PRICE!

Column Two: (some of these paragraphs have HUGE amounts of text.)
White Hook Fish FigureCast Iron Hook - White Dolphin/Fish Cast iron
white painted dolphin hook.It measures 8 in. long and stretch out 4 3/4
in. from the wall. There are two holes for mounting.
Land-Artistic Dolphin FigureColourful dolphin Made of resin and
measures 9 in.tall. Has tag that says: Land Artistic Dolphin Artist:
Aina Nergaard Nammack Sponsor: Prudential Gallo, REALTORS P/T
Exclusives, L.L.C.
3.75 tall dolphin covered dish. One of the smartest mamals next to man,
the dolphin also has been known to rescue drowning sailors.
Clock Dolphins Set/3 - You actually get all three clocks for one money!
The front of the clocks are Mirrored, each with a dolphin scene. The
edges of the glass are chipped around the edges to give a rugged
beveled look. Each clock comes packaged in its own gift box. AA
battery, measures 6 1/4 in. x 5 in..
Fish Fresh Water Set of 2What a cute gift for that favourite fisherman!
Made of stone resin and measures 5.5 in. x 3 in.at the base and is 8
in. tall
A pair of green glass pig banks, they are 4 1/2 long.
Large cast iron peanut bank standing with cane, 11 1/2" tall Previous
number 92-1026L

Column Three:
HatRacksHooks,NauticalFish,castiron
homeother,NauticalFish,Animalsother,Other
NEWoct9_14,Clocks,NauticalFish
NauticalFish,castiron
NauticalFish
banks,pigs,otheranimals
banks,castiron


Keywords:
african american
alligator
americana
angel
angels
animalsother
apple
bank
bedbath
clock
dolphin
fish
hook
pig

I was thinking if I concatenate column-one + column-two + column-three
and then do a match against the keyword list, I'd probably get close to
the results that I want. (Column-two would have to be truncated to
just the first 100 or so characters since some of them in the real data
are so long.) I want a list of keywords separated by commas as a
result -- like so:
--hook, dolphin, clock, fish
--bank, pig

I can do the lookup. I just can get the multiple results to return.


Thanks.

Barb

 

[PY & Associates]

We tried to reconstruct your 3 columns and guessed
A1="CAST IRON HOOK - WHITE DOLPHIN/FISH SPECIAL PRICE!"

B1="White Hook Fish FigureCast Iron Hook - White Dolphin/Fish Cast iron
white painted dolphin hook.It measures 8 in. long and stretch out 4 3/4 in.
from the wall. There are two holes for mounting."

C1="HatRacksHooks,NauticalFish,castiron"

You mentioned concatenating Column 1, 2, and 3.
So do you mean A1+B1+C1 only, or all of them please?

If A1+B1+C1, where is "clock" come from in the first result?

If you concatenate all of the data, why there are two resulting groups?

Interesting.
Cheers

 

[The Moose]

The data is about 4,000 lines of product. The keywords is one column
with 200+ words or short phrases in it.

When I said "clock", it was just an example because it was one of the
words that was in the sample data that I gave you.

I admire your perseverance :GRIN:

Basically, I want to search through the 4,000 lines of product and find
occurrences of the 200+ keywords. If any of the 200+ words are found
in a row that references one product, I want to string together all the
possible results. In that one example below that you put into A1, B1,
C1 then D1 would contain: dolphin, fish, hooks, cast iron. So ... on
each row next to A!, B1, C1 would be column D1 which would contain 1 to
? keywords strung together with commas and spaces.

I understand that there's a limit of 254 characters that you can do a
vlookup on. So ... A1 would be constrained to 75 characters, B1 would
be constrained to 82 characters, and C1 would be constrained to 95
characters. When you concatenate them with single-space between
elements, you arrive at the total of 254 (75+1+82+1+95=254).

Thanks.

Barb

 

[PY & Associates]

Check this out.
Adjust to suit.

http://www.savefile.com/files/373336

cheers

 

[The Moose]

That's EXACTLY what I'm looking for. Now, I've got to figure out how
to get it to work on over 4,000 lines of product and how to update
automatically when the product line changes.

Thanks so much. I really appreciate it.

Barb

 

[PY & Associates]

Post back or direct to us if you need help

Cheers