0

Is it possible to filter an array by an partial string and list multiple entries only once using a single formula (without autofilter, 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")  
6
  • You can use advanced filter
    – user555689
    Apr 14, 2016 at 17:14
  • you mean the ordinary filter function given in the home ribbon (in right end)? That's not what I'm looking for. I want to solve it by formula only.
    – Stefan
    Apr 15, 2016 at 7:00
  • have you tried pivot table? that would do exactly what you need, without any complex formula. Jun 3, 2016 at 11:21
  • Yes but it is not that charming simple as a formula. The user shell enter the expression for the filter not more not less. A pivot table is too complex for the standard user. Actually I have an acceptable solutions but the stack exchange algorithm doesn't allow me to answer currently (may only if you up vote my question) The answer is very comprehensive and gives you quite good understanding of arrays in excel (compare my answers on mrexcel.com)
    – Stefan
    Jun 5, 2016 at 10:50
  • please don't edit your answer into your question, but post it separately as an answer. Jun 16, 2017 at 11:04

1 Answer 1

1

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 E list all items of column C if the corresponding cell in column B contains expression of D3. Formula in cell E4 what is copied to E5-E14:

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

You have to hit ctrl-shift-enter to enter the formula as an array but be careful it can take quite a while when you got a large table 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 (arg2) of column C (arg1). N is calculated in Small.
  • SMALL(arg1, arg2) is supposed to return the k-th (arg2) smallest value in a data set (arg1).
    This function returns values with a particular relative standing in a data set. That's exactly what is needed for to the proper use of IF and ROWS, deeper nested.
  • 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 just FALSE where the condition is not true)

    • 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
      3. 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: 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$4:E4) 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.

Column F filters duplicates, what was the most difficult part. Column F will "only" list all items listed in column E once.
That is the formula entered in F5! (F4 is the same as in E4) using ctrl-shift-enter:

{=IFERROR(INDEX($C$2:$C$14;
                MATCH(0;
                      COUNTIF($E$4:E4;
                              $C$2:$C$14);
                      0));
          "")}

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 the range arg2, what is c06 in the upper example.

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

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

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.
  • 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 I won't do it today).

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .