Filter multi column list with repeating entries by partial string only with formula

[PackElend]

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:

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:

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

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:

...
    IF(
                    ISNUMBER(Search("A/*"; $B$2:$B$9))  
...

that results in an array with the indexes of the list $B$2:$B$9:

ISNUMBER(Search("A/*"; $B$2:$B$9): {1;0;1;1;0;0;1;1}

and so

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

`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

{=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")}

 

[Oeldere]

with a pivot table.

Book1
	B	C	D
68	ID	Class	Value
69	1	A/as	V1
70	1	B/as	V2
71	2	A/ab	V3
72	3	B/ab	V4
73	3	B/as	V5
74	2	B/ab	V5
75	1	A/as	V5
76	3	A/ab	V5
77
78
79
80	Class	Value
81	A/ab	V3
82		V5
83	A/as	V1
84		V5
85	Eindtotaal
86
Blad1

 

[PackElend]

thx for the reply
a pivot table does the job quite well but you still have to adjust the filter manually. So I created one pivot table for each filter in that case one for V1, one for V2...

Make the filter depended on a cell value would be more convenient. Isn't there a way to tell Excel to chose only those values in an array what indexed in another array?
The index array shell be a result of an search.

A | B     | C     | D       | F    
----------------------------------  
01| Array | Text  | Array   | arrays 
02| Source| sought| search  | B & D
03|       | *a*   | results | combined
04| aa    |       | 1       | aa
05| ca    |       | 2       | ca
06| ad    |       | 3       | ad
07| ee    |       | 5       | fa
08| fa    |       |         |
09| gg    |       |         |
10| bg    |       |         |

 

[Oeldere]

use an extra column in the table, to detemine if the value belongs to the criteria.

You can use the VLookup formula to lookup up the value in column A in your "another array".

 

[PackElend]

Hallo Oeldere, sorry for my late answer but I haven't got the time to continue on my Excel yet. Eventually I'm able to make some time for it but unfortunately I lost track of our conversation. Can you a bit more detailed. What value you want me to look up by VLookup and what do you mean by "another array". thanks a lot

 

[PackElend]

I solved it, not perfectly (it needs 3 columns) but it works like a charm.

A | B     | C     | D      | E       | F       | G      
-------------------------------------------------------  
01| Array |Array  | Text  | search   | search  | ordered
02| Source|Source | sought| results  | results | 
03|   #1  |  #2   | *a*   |    #1    |    #2   | 
04| aa    | c12   |       | c12      | c12     | c02
05| ca    | c13   |       | c13      | c13     | c06    
06| ad    | c06   |       | c06      | c06     | c12
07| ee    | c11   |       | c02      | c02     | c13
08| fa    | c02   |       | c06      | c25     | c25       
09| gg    | c12   |       | c13      |         |
10| ba    | c06   |       | c06      |         |
11| aa    | c13   |       | c25      |         |
12| ad    | c06   |       | #NUM!    |         |
13| gt    | c12   |       | #NUM!    |         |
14| aa    | c25   |       | #NUM!    |         |

Column F list all items of column C where the column B contains expression of D3.
Formula in cell E4 what is copied to E5-E14

[B]{[/B]=INDEX(C:C;
        SMALL(IF(ISNUMBER(SEARCH($D$3&"/*";
                                 $B$4:$B$14));
                 ROW($B$4:$B$14));
              ROWS($E$2:E2)))[B]}[/B]

You have to hit ctrl-shift-enter to enter it as array but be careful it can take quite a while when you got a large tabel being sought.
I have 1300 cells that, took more than minute but only for entering the formula, coping it to the other cells is without any delay.

Here comes what is does:

• INDEX (arg1, arg2) will output the value of item/cell n of arg1 (column C) given in arg2 (Small)
• SMALL(arg1, arg2) is supposed to return the k-th (arg2) smallest value in a data set (arg1). You shell use this function to return values with a particular relative standing in a data set.
  That's exactly what is need due to the proper use of IF and ROWS.
• IF (logical test , value if true) is the main part of the trick: it builds an array of the row numbers where the IF condition is true (note that the IF has no 'else' value, it will be FALSE anyway)
  • logical test: ISNUMBER(SEARCH($D$3&"/*"; $B$4:$B$14)) returns an array of True and False depending if SEARCH results in a numerical value or not for each cell given in the range $B$4:$B$15.
    The result for the upper example is:
    1. SEARCH: 1, 2, 1, #VALUE, 2, #VALUE, 2, 1, 1, #VALUE, 1
    2. ISNUMBER: TRUE, TRUE, TRUE, FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, FALSE, TRUE
  • value if true: ROW($B$4:$B$14)) returns an array populated by the row numbers of the array $B$4:$B$14
    The result for the upper example is:
    1. 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14

IF will combine logical test #1 and value if true #2. All values in #1 will be ignored where #2 says FALSE at same position within array #2. Finally we have an array, indicating in which row of column B, within the range given in ROW(...) the expression of D3 is found.
The result for the upper example is:
4,5,6,8,10,11,12,14
​

• ROWS($E$2:E2) is just a trick that will give you an incrementing number (ie 1 in F2, 2 in F3...). what is used in SMALL as arg2. The result in the first cell ROWS(...)=1) will be 4 (lowest value), in the second 5 and so on.
  At the end each following up cell will show the row number/position in column B where the expression of D3 is found.

That was the most difficult part. The next one is to avoid duplicates. Column F will "only" list all items listed in column F once. That is the formula entered in E6! (E4 is the same value as in F4) using ctrl-shift-enter:

[B]{[/B]=IFERROR(INDEX($C$2:$C$14;
                MATCH(0;
                      COUNTIF($E$4:E5;
                              $C$2:$C$14);
                      0));
          "")[B]}[/B]

Here comes what is does:

• COUNTIF(arg1, arg2) results an array of the length of the range arg1, indicating by 1 where where is a match of the entries in arg2.
  The result for the upper example is:
  1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0

• Match(lookup value, array , match type) looks for the first occurrence of 0 in the resulting array of COUNTIF(...) what is the 3rd position in the upper example
  • lookup value: 0, first new value/non-duplicate
  • array: resulting array of COUNTIF(...)
  • match type: 0 = exactly
• INDEX(arg2 of COUNTIF, Match(...)) will finally show the new/not duplicated value what is on the 3rd position of hte range arg2, what is c06 in the upper example.

Finally all items will be ordered alphabetically. That is the formula entered in G4 using ctrl-shift-enter:

[B]{[/B]=IFERROR(INDEX($F$4:$F$14;
                        MATCH(ROWS($G$4:$G4);
                              COUNTIF($F$4:$F$14;
                                      "<="&$F$4:$F$14);
                              0));
        "")[B]}[/B]

Here comes what is does:

• COUNTIF(arg1, arg2)is the main part of the trick: it compares the text values given in arg2 with all the other text values given in arg1 and returns its relative rank (alphabetical order).
  The result for the upper example is:
  3, 4, 2, 1, 5
• ROWS($E$2:E2) is just a trick that will give you an incrementing number (ie 1 in G2, 2 in G3...). what is used in Match as lookup value.
  The result for the upper example is in the fi:
  • Match(lookup value, array , match type) looks for the first occurrence of ROWS(...) in the resulting array of COUNTIF(...).
    The result for the upper example is:
    4, 3, 1, 2, 5
    1. cell: ROWS(...)=1 => 4
    2. cell: ROWS(...)=2 => 3
    3. cell: ROWS(...)=3 => 1
    4. cell: ROWS(...)=4 => 2
    5. cell: ROWS(...)=5 => 5
• INDEX(arg1 of COUNTIF, Match(...)) will finally show the sorted entry corresponding to its ROWS(...) result.
  The result for the upper example is:
  1. cell: ROWS(...)=1 => 4 in COUNTIF array => c02
  2. cell: ROWS(...)=2 => 3 in COUNTIF array => c06
  3. cell: ROWS(...)=3 => 1 in COUNTIF array => c12
  4. cell: ROWS(...)=4 => 2 in COUNTIF array => c13
  5. cell: ROWS(...)=5 => 5 in COUNTIF array => c25

So far so good the final step would be to combine all in one column. At least a found some help merging columns F&G but not today .