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