I'm looking for a way (lightweight one-cell formula solution) how to generate a mirrored sequencing based on the count of unique values. example for 2, 3 and 4 given values:
input values are always unique (if that matters)
output sequence needs to be spread across the range of cells (as shown on image)
Please try the following
={TRANSPOSE(SEQUENCE(ROW(A15)-ROW(A12)+1)),
TRANSPOSE(SORT(SEQUENCE(ROW(A15)-ROW(A12)+1),1,0))}
Later addition
As commented by player0, one can also use
={TRANSPOSE(SEQUENCE(COUNTUNIQUE(A12:A15)+1)),
TRANSPOSE(SORT(SEQUENCE(COUNTUNIQUE(A12:A15)+1),1,0))}
The difference being that using the first formula one can create the sequence even when referencing just empty cells.
I set myself the challenge of doing this in a single function - it must be possible, right? You just need a symmetrical function.
After some thought I came up with a function of the form:
y=c-abs(x)
So if you can generate x-values like (for the case of n=3)
-2.5 -1.5 -.5 0.5 1.5 2.5
you just need to subtract their absolute values from 3.5 to get
1 2 3 3 2 1
Unfortunately Sequence will only generate integer values so the formula isn't as tidy as one would wish:
=ArrayFormula(counta(A:A)+0.5-abs(sequence(1,counta(A:A)*2,-counta(A:A))+0.5))
