Transpose and return a row values in a columns limited to one unique and one duplicate

Maybe like this:

=LET(d,B3:B26,u,UNIQUE(FILTER(d,d<>"")),Cu,COUNTA(u),s,INT(SEQUENCE(,2*Cu,1,0.5)),INDEX(TRANSPOSE(u),,s))

Nice, Glenn!

Since the values are numeric, perhaps we can simplify?

=LET(α,B3:B25,β,UNIQUE(α),SMALL(β,1+QUOTIENT(SEQUENCE(,2*COUNT(β),0),2)))

Regards

XOR LX... are you still there??

Nice... but I could do with a bit of help understanding it. How does SMALL manage to ignore the zero in this context? The smallest value is zero... or am I being dense?? I guess this also depends on the numbers being in ascending order, too...

Originally Posted by Glenn Kennedy

How does SMALL manage to ignore the zero in this context?

No, that's just the Evaluate Formula window unhelpfully rendering the blanks as numerical zeroes in that step, but then evaluating them correctly as blanks in the final evaluation.

Originally Posted by Glenn Kennedy

I guess this also depends on the numbers being in ascending order, too...

Eh? Why?

Cheers

So to quote a former US President... it's "FAKE NEWS!" Evaluate formula is so useful... it's pity that this is another example of its failings... Many thanks for the explanation, though!

By ascending order I meant that if the 1214s were all replaced by 8888, they would appear in 3rd place (our of sequence), being preceeded by 1450 & 1560.

Please try

=LET(z,B3:B25,y,B4:B26,TRANSPOSE(FILTER(z,z*(z<>y))))

Originally Posted by Glenn Kennedy

By ascending order I meant that if the 1214s were all replaced by 8888, they would appear in 3rd place (our of sequence), being preceeded by 1450 & 1560.

Ah, yes. I assumed that the OP would want them sorting numerically, but that assumption might not be correct, you're right.

Regards