0 
=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.

Improve this question

4 Answers 4

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Would Regextext be more efficient than Is number/Search combo?
I agree. Not sure how familiar you're with regex, enter "(venus|pluto)" for either one.
2

Not tested (don't have access to Excel 365 at the moment) but something like this should work:

 =GROUPBY(B:B, C:C, SUM, 0, 0, ISNUMBER(SEARCH("Venus", B:B)))
Improve this answer

1 Comment

did not work. #VALUE error
2

Instead of using the entire range, I will convert my data into Structured References aka Tables and use the GROUPBY() function in the following manner:

Syntax :

=GROUPBY(row_fields,
         values,
         function,
         [field_headers],
         [total_depth],
         [sort_order],
         [filter_array],
         [field_relationship])

enter image description here

=GROUPBY(Company,Values,SUM,,0,,1-ISERR(SEARCH(" Venus "," "&Company&" ")))

It is highly suggested and recommended not to use the following function, which are not available for current channel and only available for Office Insiders. Please read here: Release

enter image description here

Therefore, an alternative option:

=LET(
     _Filter, FILTER(CompTable,1-ISERR(SEARCH(" Venus "," "&Company&" "))),
     _Comp, TAKE(_Filter,,1),
     _Uniq, UNIQUE(_Comp),
     _Sum, MAP(_Uniq, LAMBDA(α, SUM((α=_Comp)*DROP(_Filter,,1)))),
     HSTACK(_Uniq, _Sum))

Or,

=LET(
     _Extract, UNIQUE(HSTACK(Company,SUMIF(Company,Company,Values))),
     FILTER(_Extract,1-ISERR(SEARCH(" Venus "," "&TAKE(_Extract,,1)&" "))))
Improve this answer

3 Comments

I got a "NAME" error under all three options?
@RWB you are supposed to explain and proper justification how all answers of mine returns #NAME error while your toggling of answers accepting between two specific users doesnt return #NAME error.
Not sure I have that knowledge, thanks I will try.
1

Talking about planets is exciting ;)

Expanding Michal's use of SEARCH to include multiple terms (why not?), and adding boundaries as JvdV suggested,

=LET(
    terms, TRIM(TEXTSPLIT($F$1, ",")),
    group_by, B1:B10,
    sum_range, C1:C10,
    filter_arr, BYROW(
        group_by,
        LAMBDA(row, OR(ISNUMBER(SEARCH(terms, row))))
    ),
    GROUPBY(group_by, sum_range, SUM, 0, 0, , filter_arr)
)

Result

2024-08-09, Another variant for fun

=LET(
    terms, TEXTSPLIT(TRIM($F$1), ","),
    group_by, B1:B10,
    sum_range, C1:C10,
    is_match, ISNUMBER(SEARCH(terms, group_by)),
    matches, IF(is_match, group_by, ""),
    sum_for_terms, MMULT(TRANSPOSE(--sum_range), --is_match),
    VSTACK(terms, sum_for_terms, matches)
)

Term sums

Improve this answer

4 Comments

Works good for the first term! However, the second term (Pluto, in your example) does not sum up if there are multiple second terms. That does not impact my question since I was only asking for a single term with possible wildcards -R
Works good for the first term! However, the second term (pluto in your example) does not sum up if there are multiple second terms. That does not impact my question since I was only asking for a single term with possible wildcards
My Mistake above. My second term (Pluto ) had a "space" at the end of the word. When I cleaned the data, it consolidated the total to that second term. Yikes, something to watch for
I'm usually careful about trimming, but forgot to add it here. I've now remedied it. Also added a variant for fun. Thanks for testing and your feedback!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.