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
Moises7184's avatar
Moises7184
Copper Contributor
Apr 04, 2022

mtarler I have just tried your second formula and it's pretty cool. Here's another thought, let's say right below the summation cell (that is continuously shifting as more numbers are being added from the original table) you would like to apply the same formula. Furthermore, it also needs to move all of it's numbers because it will be below the first unique function.

 

In a nutshell, You want another unique function right below the first unique function, but the unique function (being the second unique function) right below the first unique function has to move down as the first unique function grows. Naturally, the second unique function would grow as well. 

 

I'm assuming that the second unique function will use the first unique functions formula (as shown below in figure 1) and some more if it's prone to be shifting as the first unique function grows.

 

 

 

Figure 1.

 

Thank you for your time.

 

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