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

[CodyBarber]

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 items get in the way of the unique (by adding items to the table) I will get a spill error

Is there a way to keep the Total at the bottom of the unique function regardless of its length?

Thanks for the help.

[#2]

@CodyBarber 

With your permission, if I can recommend. It can help us all if you upload an Excel file (without sensitive data), no picture. Even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases. You could get a precise solution much faster with a file (w/out sensitive data). This would also be a blessing for all of us, as we can understand the problem much better, a win-win situation for everyone.

* Knowing the Excel version and operating system would also be an advantage.

Thank you for your understanding and patience

Nikolino

I know I don't know anything (Socrates)

[#3]

@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.

[#4]

@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.

[#5]

@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)
         )
     )

[#6]

@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?