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

%3CLINGO-SUB%20id%3D%22lingo-sub-1831501%22%20slang%3D%22en-US%22%3EHow%20to%20Move%20a%20row%20to%20the%20bottom%20of%20%22Unique%22%20function%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1831501%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20table%20that%20can%20change%2C%3C%2FP%3E%3CP%3EAdding%20to%20the%20table%20will%20effect%20a%20unique%20function%20on%20another%20sheet%2C%3C%2FP%3E%3CP%3EI%20want%20to%20always%20show%20the%20Total%20at%20the%20bottom%20of%20the%20Unique%20function%2C%20however%2C%20if%20items%20get%20in%20the%20way%20of%20the%20unique%20(by%20adding%20items%20to%20the%20table)%20I%20will%20get%20a%20spill%20error%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20keep%20the%20Total%20at%20the%20bottom%20of%20the%20unique%20function%20regardless%20of%20its%20length%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1831501%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1834572%22%20slang%3D%22de-DE%22%3ESubject%3A%20How%20to%20Move%20a%20row%20to%20the%20bottom%20of%20%22Unique%22%20function%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1834572%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F850065%22%20target%3D%22_blank%22%3E%40CodyBarber%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20your%20permission%2C%20if%20I%20can%20recommend.%20It%20can%20help%20us%20all%20if%20you%20upload%20an%20Excel%20file%20(without%20sensitive%20data)%2C%20no%20picture.%20Even%20if%20it%20is%20said%20that%20a%20picture%20can%20say%20a%20thousand%20words%2C%20it%20is%20certainly%20not%20in%20the%20case%20of%20Excel%2C%20on%20the%20contrary%20in%20some%20cases.%20You%20could%20get%20a%20precise%20solution%20much%20faster%20with%20a%20file%20(w%2Fout%20sensitive%20data).%20This%20would%20also%20be%20a%20blessing%20for%20all%20of%20us%2C%20as%20we%20can%20understand%20the%20problem%20much%20better%2C%20a%20win-win%20situation%20for%20everyone.%3C%2FP%3E%3CP%3E*%20Knowing%20the%20Excel%20version%20and%20operating%20system%20would%20be%20an%20advantage.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

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 Replies
Highlighted

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

Highlighted

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