There is a table for example,
Example data:
ColumnA | ColumnB |
ROW1 Code1 | There is an apple, and there wasn't an apple. |
ROW2 Code2 | The applepie is tasty. |
ROW3 Code3 | Apple is good for people. |
ROW4 Code4 | We buy the apple, and they sell apples! |
--------
The result I hope to show:
ColumnA | ColumnB | ColumnC | ColumnD | ColumnE | ColumnF |
ROW5 apple | 4 | Code1 | Code3 | Code4 | |
ROW6 apples | 1 | Code4 | | | |
ROW7 the | 2 | Code2 | Code4 | | |
I hope to write some formula to ...
(1) How to write formula for B5, B6?
There are two exact words at A5,A6. B5~B7 will show numbers that the sentence from B1:B4 matches the exact word(A5~A7).
(2) How to write formula for C5~F5,C6~F6?
Because the exact word "apple" is conclued in B1, B3, B4. I want to show the value at A1, A3, A4.
Because the exact word "the" is conclued in B2, B4. I want to show the value at A2, A4.
I've tried some formula, but can't perfect work.
Could you please help me with this question? Thanks for you help.
For Excel 2013, assuming the same layout as per JvdV's screenshot, and that you place your punctuation marks in C1, C2 and C3, formula in E1:
=SUMPRODUCT(2+LEN(B$1:B$4)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(" "&B$1:B$4&" "),C$1," "),C$2," "),C$3," ")," "&D1&" ","")))/(2+LEN(D1))
and copied down.
An additional SUBSTITUTE clause will be required for each additional punctuation mark added below that in C3. For example, with a new entry in C4, the above becomes:
=SUMPRODUCT(2+LEN(B$1:B$4)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(" "&B$1:B$4&" "),C$1," "),C$2," "),C$3," "),C$4," ")," "&D1&" ","")))/(2+LEN(D1))
Then, for the lists, in F1:
=IFERROR(INDEX($A$1:$A$4,AGGREGATE(15,6,(ROW($A$1:$A$4)-MIN(ROW($A$1:$A$4))+1)/ISNUMBER(SEARCH(" "&$D1&" ",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(" "&$B$1:$B$4&" "),$C$1," "),$C$2," "),$C$3," "))),COLUMNS($F1:F1))),"")
and copied right until you start to get blanks for the results. This formula can also be copied down to F2, F3, etc.