Excel Formula for Categorising Products

Estado: Closed
Premio: £10
Propuestas recibidas: 8
Ganador: sakhawat2

Winner

Resumen del concurso

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.

Habilidades recomendadas

Excel

Winner

Publica un concurso similar

Principales propuestas de este concurso

sakhawat2 Bangladesh

0
Farrukh Saeed Pakistan

0
rahilvasaya Pakistan

0
abirrajdut Bangladesh

0
zaidur101 Bangladesh

0
Mehadi0721 Bangladesh

0

Ver más participaciones

Tablero de aclaración pública

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