Forum Discussion

E_C_Tony's avatar
E_C_Tony
Copper Contributor
Sep 08, 2022

Unique Function - Horizontal Spill

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?  
excel
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Sep 08, 2022

E_C_Tony 

=TRANSPOSE(UNIQUE(B10:B17))

You can try UNIQUE inside TRANSPOSE. 

  • E_C_Tony's avatar
    E_C_Tony
    Copper Contributor
    Sep 09, 2022

    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?

     

     

     

     

    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      Sep 09, 2022

      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.

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

      • E_C_Tony's avatar
        E_C_Tony
        Copper Contributor
        Sep 12, 2022

        OliverScheurich 

         

        Hi, thanks again for your reply. 

         

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

         

         

Resources