excelexcel-formulawildcard

Excel GroupBy Function Filter Array


=GROUPBY(row_fields, values, function, [field Headers], [total_depth], [sort order], [**filter_array**], [field relationship])

I would like to use the FILTER ARRAY for search and group partial text matches, for instance Venus would find "Venus Printing Company" or "The Planet Venus". Has anyone found a workaround to do this?

=GROUPBY(B:B,C:C,SUM,0,0,,B:B="Venus Stores Company") successfully pulls and filters "Venus Stores Company" but I would also like to have =GROUPBY(B:B,C:C,SUM,0,0,,B:B=" wildcard Venus wildcard") or =GROUPBY(B:B,C:C,SUM,0,0,,B:B= "wilcard" & "Venus Stores Company" & "wildcard") to work and neither do.


Solution

  • Just for fun as it seems you are using the beta-channel with GROUPBY() and eta-lambda syntax:

     =GROUPBY(B:B,C:C,SUM,0,0,,REGEXTEST(B:B,"venus",1))
    

    Tip: I'd maybe use some LET() variables to find the actual data's boundaries with TOCOL(). Should save a lot of computing time.