I have a spreadsheet in which I need to do something unusual.
Each cell will contain a list of keywords. Each keyword is worth a specific value (1-4). A cell may contain any number of keywords in any order, however each keyword only repeats once.
Part1 :Getting a list that transposes the words in the cell to a list of associated values.
In the results sheet I expect the value list to return 1, 2, 4
I've done this 2 ways but I feel both are ugly and nonuser friendly, would be great if someone had a better/simpler way to do this.
These work but are ugly:
Part2 : Sum up the associated values for every word in the cell.
[S]I've looked at sumifs and sumproduct but I couldn't get this to work! [/S]
Note: I have an example spreadsheet but the upload isn't working.