Unique Function - Horizontal Spill

Copper Contributor

Hi,

I want the results of a list returned by Unique to spill horizontally along one same row - not vertically down a column.

Does anyone know how to do that please?

 

5 Replies

@E_C_Tony Use this:

=TRANSPOSE(UNIQUE(..........))

@E_C_Tony 

=TRANSPOSE(UNIQUE(B10:B17))

You can try UNIQUE inside TRANSPOSE. 

@OliverScheurich 

Hi both,

Thanks for your replies.

I can get Unique nested inside Transpose to work only when a fixed number of items are returned every time.

The number of items returned by the workbook I am using varies. 

The spill facility handles that very well but the workbook layout means I need that to spill horizontally, not vertically. 

Does anyone know a way to do that?

 

 

 

 

@E_C_Tony 

=TRANSPOSE(UNIQUE(FILTER(B3:B17,B3:B17>E2)))

I can only guess what you exactly want to do. For example the above formula dynamically spills the result horizontally because the original data is in vertical order in range B3:B17. The filter criteria is B3:B17>E2 which means you can change the value in cell E2 and the formula automatically spills the result (a varying number of cells) horizontally.

unique.JPG

Otherwise you might want to attach a screenshot without sensitive data which shows your data and expected result. 

@OliverScheurich 

 

Hi, thanks again for your reply. 

 

That looks great; I'll give it a try.