Auto Categorize Products

[Gidion]

Hi All,

I've been researching for weeks now on an easy way to help me categorize my products. I have over 3000 of them to do.
I think Vlookup is what I'm looking for but just can't figure out how to implement it on my data.

I have multiple keywords assigned to each category, how can I use the keywords to assign the relevant category to my product? Is it even possible to use an array in VLookup's first element?

The data I have is:

Product Title - Category - Keywords(for each category)
SDS1234 - Bugs - Butterfly, Bee, Worm, Ant etc.

Any help is much appreciated as well as suggestions if I'm on the right track here with Vlookup.

Kind Regards
Gidion

[ORoos]

Hi Gidion,
A sample workbook would be useful to understand what exactly you try to achieve, how the data is organised and what variables you have. See yellow bar at top.

[Gidion]

Thanks for your reply ORoos,

Attached is a small workbook of some example data, 1st sheet is a list of product names with the relevant category as an example on the right.
Categories also has to be in a certain format as I will be exporting the sheet as a CSV for import on the website.

See the second sheet for all info on the categories per my example.

Kind Regards
Gidion

[ORoos]

Do you want to create the item category list on sheet 'EXPORT' column B ?
For example: 'SD1151 Horse Bookends', we would have to find 'Horse' in the CAT_INOF list and return the category list. Then same with 'Bookend'...? Problem is, neither of these two words are in your categories to be found.

[Gidion]

Yeap, I want to use the category's keywords on CAT_INFO sheet to assign the relevant category to product on EXPORT SHEET.
I probably just missed that one as I copied from the actual product list. Have a look at "SD1159 Cupcake Stand" - The word "Cupcake" will be used to assign that product to the Category "Kitchen"

This data is just a representation I typed up quick to show what I need to achieve, the actual data is a huge long list I still need to get sorted.

Hope if gives you a good understanding of the issue.

[ORoos]

Please have a look at the attached.
On the Export tab, I have split the product titles into 3 columns (don't need the first one as it is just the number).
From there we do a 'lookup' of the retrieved items and return the column header = category.
Finally we combine the result into one string.
From there you may need to copy > paste values for your csv file.

[Gidion]

You're a genius!! Wow, also took an interesting approach to split the Titles in 3 sections, would never have thought of that.
Thanks a mil! I'll be sitting tonight figuring out how the formulas work and how to expand the keywords section to accommodate all the other products on the list.

But this helps me out a TON already, thanks again for your help! Much appreciated.

Kind Regards
Gidion

[ORoos]

You are welcome Gidion,
Note the splitting is only taking off the number first (first space), then the next single word, then the rest. So you may need to adjust the odd key words.
If you want to split each and every word, you could use 'Test-to-column'.

Anyway, if you are happy with the solution, please flag the Thread as 'Solved'.

[Gidion]

Thanks ORoos, Will have a look at the Test-to-Column option too, might help to refine the process.

Enjoy the rest of your night.

Kind Regards
Gidion