Excel Formula for Categorising Products

Status: Closed
Hadiah: £10
Penyertaan Diterima: 8
Pemenang: sakhawat2

Winner

Ringkasan Peraduan

I'm currently trying to finish some formula that I've been working on for a little while. Please see the following example link:

https://docs.google.com/spreadsheets/d/1nK6N0DwaXTYcMD8kSDlFYf1hqOFNxnakONE1tWtBOL4/edit?usp=sharing

*It'd be preferable to create your own copy of the example spreadsheet

The aim of the formula is to do the following -
1. Search the text in the title column and look for a match on the "Array Key" tab using the "Keywords" column.
2. If a Keyword is found within the title, the "Category" that is associated with that Keyword is used.
3. If no result is found using the Title of the product then the "SKU" column should be searched and matched using the "Keywords" on the Array Tab under the "Theme by SKU" subsection.
4. All results must be shown but no duplicates. For example; "Star Wars Destiny: Convergence Booster Box" should have the following themes - Space | Film
5. All results should be joined together with a " | ".

As you can see on the spreadsheet I have the formula almost in a working state. Currently the issue I'm having is the formula doesn't seem to like spaces. "Star Wars" for example isn't found but "Star" is found. It's also only giving me 1 result per keyword. If I have "Star" as a keyword for the Film category and the Space category, it won't pull back both results but only 1.

Kemahiran Disyorkan

Excel

Winner

Siarkan Peraduan Seperti Ini

Penyertaan teratas dari peraduan ini

sakhawat2 Bangladesh

0
Farrukh Saeed Pakistan

0
rahilvasaya Pakistan

0
abirrajdut Bangladesh

0
zaidur101 Bangladesh

0
Mehadi0721 Bangladesh

0

Lihat Lebih Banyak Entri

Papan Penjelasan Umum

blui88
- 4 tahun yang lalu
Hi there, Do you want search for whole word/ exact match or want to use wild card. e.g. "Like * Mice * ".
- 4 tahun yang lalu
1. blui88
  - 4 tahun yang lalu
  And Will VBA based solution is fine for you or only want Excel Solution.
  - 4 tahun yang lalu
Aragornsan
- 4 tahun yang lalu
Solved it
- 4 tahun yang lalu
sakhawat2
- 4 tahun yang lalu
Hi, would you like a VBA solution to this? It would get much more expandable.
- 4 tahun yang lalu
Farrukh Saeed
- 4 tahun yang lalu
check #2 check all 3 images and read the description
- 4 tahun yang lalu
Farrukh Saeed
- 4 tahun yang lalu
hold on i am working on it
- 4 tahun yang lalu