Group by using filter array and then join columnn and rows

Hi

i have a table and want to filter by conditions, then group them by one column, then concatenate the remaining columns, then text join the rows and all of this in one single formual
So far i am able to do all the things except the Group by part

I am able to use the Powerquery to get the desired results.
I figured out how to sort and filter the data with the dynamic array formula.

For the 21.08 and machine B I can get to this result with the arrays (formual in cell J21)

The final result is combining rows and columns to this:
mix=22=full=21=mix=12=full=5

But i need this one
mix=34, full=26

SO the issues I can not solve by my own are:
How can i sum the scrap amount by the reasons for my selection in the array

So from this table
mix 22
full 21
mix 12
full 5

to this one

mix 34
full 26



Thanks

Please see formula in cell [C23].

Formula:

Please Login or Register  to view this content.

Ohh, thats a bit easier than my formula, thanks a lot.

I forgot to mention one point, its in my formula, how can i sort by value in your solution?
e.g.
mix=13,test=55,full=5 should be
test=55,mix=13,full=5

So starting with the highest values descending.


And one thing, for my understanding of your formula:
Sumifs is just going trough the tabel and create an array with the summarized values.
The part TEXTJOIN(", ",,IF(x,UNIQUE(DATA[Reason])&"="&x,"")) is not clear for me, the if part.
I read the formula, go through the array from sumifs, if there is a value, take the unique value from Data[Reason] and add the = and value x.
But UNIQUE(DATA[Reason] is going back to the initial table, how can this link to the sumifs value for the single reason?



Thanks a lot

Please try this formula to get sort by value result:

Formula:

Please Login or Register  to view this content.

refer to your question in #3:
TEXTJOIN(", ",,IF(x,UNIQUE(DATA[Reason])&"="&x,""))

Let's take 21/8/2021, Machine B as an example:

variable x in LET formula will generate the array [34,0 ,26]

thus, IF(x, means IF({34,0,26}, which has the same effect as IF({true,false,true},


UNIQUE(DATA[Reason]) produces an array, which is used as a criteria parameter of SUMIFS.

You can try the below formula for a better understanding:

=SUMIFS(DATA[Scrap],DATA[Date],J10,DATA[Machine],K10,DATA[Reason],{"test";"full";"nothing"})

Please try
=LET(ur,UNIQUE(D3:D15),sm,SUMIFS(C3:C15,A3:A15,J10,B3:B15,K10,C3:C15,">"&Limit,D3:D15,ur),TEXTJOIN(", ",,TEXT(SORT(sm,,-1),""""&SORTBY(ur,sm,-1)&"""=0;;")))


PQ

Please Login or Register  to view this content.

That's good. Thank you for sharing your idea.