Unique Function - Horizontal Spill

New Contributor


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:




You can try UNIQUE inside TRANSPOSE. 


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?







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.


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



Hi, thanks again for your reply. 


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