Forum Discussion
Solved
Summarise data and group together
Hello! i was wondering if something like this is possible. we have a food menu for ordering. when the orders are placed on the top tables, i want then a formula or function to summarize the orders an...
It's like
with
=LET( main, LET(t, INDEX(dataMonday,0,1), FILTER(t, t<>"") ), Side1, LET(t, INDEX(dataMonday,0,2), FILTER(t, t<>"") ), Side2, LET(t, INDEX(dataMonday,0,3), FILTER(t, t<>"") ), nA, ROWS(main), nB, ROWS(Side1), nC, ROWS(Side2), n, nA + nB + nC, j, SEQUENCE(n), fullList, IF( j <= nA, INDEX(main, j), IF( j<= nA+nB, INDEX(Side1, j-nA), INDEX( Side2, j-nA-nB) ) ), names, UNIQUE(fullList), countNames, MMULT(--(names=TRANSPOSE(fullList)), SEQUENCE(n,,1,0)), IF({1,0}, names, countNames) )
As variant, if like this
=LET(
n, ROWS(dataMonday),
main, INDEX(dataMonday,0,1),
Side1, INDEX(dataMonday,0,2),
Side2, INDEX(dataMonday,0,3),
nameMain, UNIQUE(main),
countMain, MMULT(--(nameMain=TRANSPOSE(main)), SEQUENCE(n,,1,0)),
nameSide1, UNIQUE(Side1),
countSide1, MMULT(--(nameSide1=TRANSPOSE(Side1)), SEQUENCE(n,,1,0)),
nameSide2, UNIQUE(Side2),
countSide2, MMULT(--(nameSide2=TRANSPOSE(Side2)), SEQUENCE(n,,1,0)),
mMain, ROWS(nameMain),
mSide1, ROWS(nameSide1),
mSide2, ROWS(nameSide2),
k, SEQUENCE(mMain+mSide1+mSide2),
names, IF( k <= mMain, INDEX(nameMain, k),
IF( k<= mMain+mSide1, INDEX(nameSide1, k-mMain),
INDEX(nameSide2, k-mMain-mSide1))),
counts, IF( k <= mMain, INDEX(countMain, k),
IF( k<= mMain+mSide1, INDEX(countSide1, k-mMain),
INDEX(countSide2, k-mMain-mSide1))),
IF({1,0}, names, counts) )
SergeiBaklan the solution seems to work, but it also counts cells with no value. is there a way to skip it when the value is an empty cell?
It's like
with
=LET( main, LET(t, INDEX(dataMonday,0,1), FILTER(t, t<>"") ), Side1, LET(t, INDEX(dataMonday,0,2), FILTER(t, t<>"") ), Side2, LET(t, INDEX(dataMonday,0,3), FILTER(t, t<>"") ), nA, ROWS(main), nB, ROWS(Side1), nC, ROWS(Side2), n, nA + nB + nC, j, SEQUENCE(n), fullList, IF( j <= nA, INDEX(main, j), IF( j<= nA+nB, INDEX(Side1, j-nA), INDEX( Side2, j-nA-nB) ) ), names, UNIQUE(fullList), countNames, MMULT(--(names=TRANSPOSE(fullList)), SEQUENCE(n,,1,0)), IF({1,0}, names, countNames) )- thank you works perfectly!
aavgoustinoss , you are welcome
Got it, will update bit later today.