Forum Discussion

CodyBarber's avatar
CodyBarber
Copper Contributor
Oct 29, 2020

How to Move a row to the bottom of "Unique" function automatically

Hi guys,   I have a table that can change, Adding to the table will effect a unique function on another sheet, I want to always show the Total at the bottom of the Unique function, however, if it...
excel
mtarler's avatar
mtarler
Silver Contributor
Apr 04, 2022

Moises7184  We now have the LET() function so this sort of action is much easier to do and read:

=LET(A, UNIQUE(A:A),
     B, UNIQUE(B:B),
     L_A, ROWS(A),
     L_B, ROWS(B),
     S, SEQUENCE(L_A+L_B+2,1,1),
     IFS(S<=L_A, A,
         S=L_A+1, SUM(A),
         S<=L_A+L_B+1, INDEX(B,S-L_A-1),
         TRUE, SUM(B)
         )
     )

 

Lorenzo's avatar
Lorenzo
Silver Contributor
Apr 05, 2022

mtarler 

 

With the first set of LAMBDA functions

 

 

in E2:

=LET(
    rng, ListsOne,
    cnu, BYCOL(rng,
            LAMBDA(cl, COUNT(UNIQUE(FILTER(cl,cl<>""))))
    ),
    cls, COLUMNS(rng),
    sqc, SEQUENCE(,cls),
    ttr, MAKEARRAY(1,cls,
            LAMBDA(rw,cl, SUM(INDEX(cnu,SEQUENCE(,cl)))+cl)
    ),
    MAKEARRAY(SUM(cnu)+cls,1,
        LAMBDA(rw,cl,
            LET(
                mtr, XMATCH(rw,ttr),
                IF(ISNUMBER(mtr),SUM(UNIQUE(INDEX(rng,,mtr))),
                   INDEX(
                        UNIQUE(INDEX(rng,,XLOOKUP(rw,ttr,sqc,,1))),
                        rw-XLOOKUP(rw,ttr,ttr,0,-1)
                   )
                )
            )
        )
    )
)

 

Can this be simplified / Is there a better way?

Resources