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
Oct 29, 2020

CodyBarber If I understand correctly you have a column where at the top you use the UNIQUE() function on a different column of numbers.  at the bottom of the resulting list you want a SUM().  The problem is if there are 3 unique values that SUM/Total would be in the 4th row but then if you add another unique value to the original list you SUM equation in the 4th row would now interfere with the UNIQUE() function spill. 

So the easiest solution is to just flip it over and make row 1 your Total and then the UNIQUE() function would be in row 2 and spill down.

If you insist on doing it the other way it can be done but each option I see is 'not pretty'.  My first 'solution' was to use a helper column with the UNIQUE() function (which could be hidden) and then in the next column use a formula that basically looks at the other column and if there is a value then use it else if the previous row had a value than use SUM() of previous column else "".  Something like this (with the hidden UNIQUE() function in col C):

=IFS(ROW(C:C)<=COUNT(C1#),C1#,ROW(C:C)-1=COUNT(C1#),SUM(C:C),1=1,"")

but then I got creative and came up with a single formula solution:

=CHOOSE(FLOOR.MATH(SEQUENCE(COUNTA(UNIQUE(A:A))+1,1,1,1/COUNTA(UNIQUE(A:A)))),UNIQUE($A:$A),SUM(UNIQUE(A:A)))

like I said 'not pretty' and just putting the sum in Row 1 would be easier.

 

 

  • 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