Hi,
I have a set of data in column A with text string in each row under Data sheet and would like to split text string in each row based on a listing of keywords ( Unique name ) under List sheet.
I copied set of formulas from google search and modified, the result is not met my expectation.
{=IFERROR(INDEX(List!$A$2:$A$71, SMALL(IF(COUNTIF($A2, "*"&List!$A$2:$A$71&"*"), MATCH(ROW(List!$A$2:$A$71), ROW(List!$A$2:$A$71)), ""), COLUMNS($M$1:M1))), "")}
The expected result is to separate text string in each row by columnar result or the result can be concatenated with "+" as per sample below : -
Or Expected Results :-
Appreciate if someone can help to use better set of formulas or vba to solve the above problem
Thanks in advance
Regards
Lenard
I have a set of data in column A with text string in each row under Data sheet and would like to split text string in each row based on a listing of keywords ( Unique name ) under List sheet.
I copied set of formulas from google search and modified, the result is not met my expectation.
{=IFERROR(INDEX(List!$A$2:$A$71, SMALL(IF(COUNTIF($A2, "*"&List!$A$2:$A$71&"*"), MATCH(ROW(List!$A$2:$A$71), ROW(List!$A$2:$A$71)), ""), COLUMNS($M$1:M1))), "")}
| Sample file_working.xlsx | |||
|---|---|---|---|
| A | |||
| 1 | Keyword | ||
| 2 | ABS | ||
| 3 | ADDRESS | ||
| 4 | AGGREGATE | ||
| 5 | AND | ||
| 6 | AVERAGE | ||
| 7 | CELL | ||
| 8 | COLUMN | ||
| 9 | CONCAT | ||
| 10 | COUNT | ||
| 11 | COUNTA | ||
| 12 | COUNTBLANK | ||
| 13 | COUNTIF | ||
| 14 | COUNTIFS | ||
| 15 | DATE | ||
| 16 | EDATE | ||
| 17 | EOMONTH | ||
| 18 | EXACT | ||
| 19 | FILTER | ||
| 20 | FIND | ||
| 21 | FLOOR | ||
| 22 | FREQUENCY | ||
| 23 | HYPERLINK | ||
| 24 | IF | ||
| 25 | INDEX | ||
| 26 | INDIRECT | ||
| 27 | ISBLANK | ||
| 28 | ISERR | ||
| 29 | ISERROR | ||
| 30 | ISNA | ||
| 31 | ISNUMBER | ||
| 32 | LARGE | ||
| 33 | LEFT | ||
| 34 | LEN | ||
| 35 | LET | ||
| 36 | LOWER | ||
| 37 | MATCH | ||
| 38 | MAX | ||
| 39 | MID | ||
| 40 | MIN | ||
| 41 | MMULT | ||
| 42 | MOD | ||
| 43 | NOT | ||
| 44 | ODD | ||
| 45 | OFFSET | ||
| 46 | OR | ||
| 47 | POWER | ||
| 48 | PROPER | ||
| 49 | RANK | ||
| 50 | REPT | ||
| 51 | RIGHT | ||
| 52 | ROW | ||
| 53 | SEARCH | ||
| 54 | SEQUENCE | ||
| 55 | SMALL | ||
| 56 | SORT | ||
| 57 | SUBTOTAL | ||
| 58 | SUM | ||
| 59 | SUMIF | ||
| 60 | SUMPRODUCT | ||
| 61 | TEXT | ||
| 62 | TODAY | ||
| 63 | TRANSPOSE | ||
| 64 | UNIQUE | ||
| 65 | UPPER | ||
| 66 | LOOKUP | ||
| 67 | XLOOKUP | ||
| 68 | HLOOKUP | ||
| 69 | VLOOKUP | ||
| 70 | WEEKDAY | ||
| 71 | YEAR | ||
List | |||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C21:I30,C20:J20,C14:I19,C7:J13,C2:I6 | C2 | =IFERROR(INDEX(List!$A$2:$A$71, SMALL(IF(COUNTIF($A2, "*"&List!$A$2:$A$71&"*"), MATCH(ROW(List!$A$2:$A$71), ROW(List!$A$2:$A$71)), ""), COLUMNS($M$1:M1))), "") |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
The expected result is to separate text string in each row by columnar result or the result can be concatenated with "+" as per sample below : -
Or Expected Results :-
Appreciate if someone can help to use better set of formulas or vba to solve the above problem
Thanks in advance
Regards
Lenard
