I have a table.
When I am using formula:
=FILTER(A1:C7,B1:B7="red",)
what I am getting is:
What I would like to get however is:
Any ideas how to do it?
Note: Using the Filter function is a must.
-
I would look at unique() for column A then "red".Solar Mike– Solar Mike2023-11-15 19:04:34 +00:00Commented Nov 15, 2023 at 19:04
-
why not use a pivot table?Terry W– Terry W2023-11-15 23:44:45 +00:00Commented Nov 15, 2023 at 23:44
Add a comment
|
4 Answers
Try using the following formula:
• Formula used in cell E2
=LET(
α, FILTER(A2:C7,B2:B7="red"),
φ, INDEX(α,,1)&"|"&INDEX(α,,2),
UNIQUE(HSTACK(TEXTBEFORE(φ,"|"),TEXTAFTER(φ,"|"),
MMULT(N(φ=TOROW(φ)),DROP(α,,2)))))
Or, Using PIVOTBY() applicable to Beta Users
• Formula used in cell E2
=LET(
α, FILTER(A2:C7,B2:B7="red"),
PIVOTBY(TAKE(α,,2),,TAKE(α,,-1),SUM,,0))
Or Using GROUPBY()
=LET(α, FILTER(A2:C7,INDEX(A2:C7,,2)="red"), GROUPBY(TAKE(α,,2),TAKE(α,,-1),SUM,,0))
Or, Using PIVOTBY()
=PIVOTBY(A2:B7,,C2:C7,SUM,,0,,,,B2:B7="red")
Alternative version of MMULT() realized it would perform more efficiently & better than the earlier version.
=UNIQUE(FILTER(HSTACK(A2:B7,MMULT((A2:A7=TOROW(A2:A7))*(B2:B7="red"),C2:C7)),B2:B7="red"))
answered Nov 15, 2023 at 19:06
Mayukh Bhattacharya
29.2k9 gold badges31 silver badges46 bronze badges
2 Comments
Elmar
I am not a Beta User. So
GROUPBY() or PIVOTBY() are not available to me. But the very first option worked for me. Thank you! I hope GROUPBY() or PIVOTBY() are released to official version asap.Mayukh Bhattacharya
The
MMULT() should work, also I have just posted that as an alternative, I have found there are some issues with those functions for now, should nt be used as it is in Beta testing. shared as a knowledge purpose. Yes usually all new functions are released by batches as far I have learnt from experts! And not just that in workplace Beta will not be allowed to be enabled, usually unless there is a special grant for the same!Similar to Mayukh Bhattacharya's answer, but without text manipulation.
=LET(
valid_range, FILTER(A2:C7, B2:B7="red"),
values, MMULT(TRANSPOSE(INDEX(valid_range,,1) = TRANSPOSE(UNIQUE(INDEX(valid_range,,1))))*1,
INDEX(valid_range,,3)),
HSTACK(UNIQUE(DROP(valid_range,,-1)), values))
Comments
If applicable, use GROUPBY():
Formula in E2:
=GROUPBY(A2:B7,C2:C7,SUM,,0,,B2:B7="red")
You say using FILTER() is a must, however we have now used the internal groupby-filter which seems fine to me.
Alternatively, "old"-fashioned:
=UNIQUE(FILTER(HSTACK(A2:B7,SUMIFS(C2:C7,A2:A7,A2:A7,B2:B7,"red")),B2:B7="red"))
For those having access to Beta version of Excel:
=LET(f,FILTER(A2:C7,B2:B7="red"),PIVOTBY(TAKE(f,,2),,DROP(f,,2),SUM,,0))
or
=LET(f,FILTER(A2:C7,B2:B7="red"),GROUPBY(TAKE(f,,2),DROP(f,,2),SUM,,0))
or
=LET(g,GROUPBY(A2:B7,C2:C7,SUM,,0),FILTER(g,INDEX(g,,2)="red"))
