Is it possible to filter an array by an partial string and list multiple entries only once using a single formula (without autofilter/advanced filter, VBA, or additional columns) For example, I have the following spreadsheet:
If I filter the column B by Class "A/*" but showing their value only once, the result should be:
instead of
Filter the column by the partial string works (adapted that description) to somehow like this:
that results in an array with the indexes of the list $B$2:$B$9:
and so
So far I haven't found any way how to combine that with the "unique name list" approach
as described here
The little I having is that but it's not working that well and causes quite a cpu load, e.g. cell C8
Code:
A | B | C | D | F
-----------------------------------
01| ID | Class | Value | Filter
02| 1 | A/as | V1 |
03| 1 | B/as | V2 |
04| 2 | A/ab | V3 |
05| 3 | B/ab | V4 |
06| 3 | B/as | V5 |
07| 2 | B/ab | V5 |
08| 1 | A/as | V5 |
09| 3 | A/ab | V5 |
If I filter the column B by Class "A/*" but showing their value only once, the result should be:
Code:
A | B | C | D | F
-----------------------------------
01| ID | Class | Value | Filter
02| 1 | A/as | V1 | V1
03| 1 | B/as | V2 | V3
04| 2 | A/ab | V3 | V2
05| 3 | A/ab | V1 |
06| 3 | B/as | V4 |
07| 2 | B/ab | V5 |
08| 1 | A/as | V2 |
09| 3 | A/ab | V2 |
instead of
Code:
A | B | C | D | F
-----------------------------------
01| ID | Class | Value | Filter
02| 1 | A/as | V1 | V1
03| 1 | B/as | V2 | V3
04| 2 | A/ab | V3 | V1
05| 3 | A/ab | V1 | V2
06| 3 | B/as | V4 | V2
07| 2 | B/ab | V5 |
08| 1 | A/as | V2 |
09| 3 | A/ab | V6 |
Filter the column by the partial string works (adapted that description) to somehow like this:
Code:
...
IF(
ISNUMBER(Search("A/*"; $B$2:$B$9))
...
that results in an array with the indexes of the list $B$2:$B$9:
Code:
ISNUMBER(Search("A/*"; $B$2:$B$9): {1;0;1;1;0;0;1;1}
Code:
IF(IS...): {1;3;4;8;9}
So far I haven't found any way how to combine that with the "unique name list" approach
Code:
`MATCH(0;INDEX(COUNTIF(`
as described here
The little I having is that but it's not working that well and causes quite a cpu load, e.g. cell C8
Code:
{=IFERROR(INDEX(
INDEX($D$2:$D$9;
IF(ISNUMBER(SEARCH("A/*";$C$2:$C$9));
ROW($D$2:$D$9)-ROW($D$2)+1)));
MATCH(0;
INDEX(COUNTIF($C$2:C7;
INDEX($D$2:$D$9;
IF(ISNUMBER(SEARCH("A/*";$C$2:$C$9));
ROW($D$2:$D$9)-ROW($D$2)+1)));
0;0);0));
"error")}