Forum Discussion

Joseph715's avatar
Joseph715
Copper Contributor
Jun 26, 2025

Order of multiple functions in a formula.

Door AreaDoorTruck #RouteDeparture Time First DeliveryDriver
Shipping1121751DO1 9:30 AM 
Shipping1121751DO1 9:30 AM 
Shipping1121751DO1 9:30 AM 
Shipping1121751DO1 9:30 AM 
Shipping1121751DO1 10:00 AM 
Shipping1021751DL1 9:45 AM 
Shipping1021751DL1 10:15 AM 
Shipping1021751DL1 10:45 AM 
Shipping1021751DL1 10:45 AM 
Shipping1021751DL1 11:00 AM 

 

=SORT(FILTER(CHOOSE({1,2,3,4,5,6,7},Raw_Data_tbl[Area],Raw_Data_tbl[Door],Raw_Data_tbl[Truck],Raw_Data_tbl[Service Route],Raw_Data_tbl[Departure Time],Raw_Data_tbl[Order  Pickup/ Delivery Time],Raw_Data_tbl[Driver]),Raw_Data_tbl[Agency Code]>0,""),{1,2},{-1,-1})

 

I am not sure where I should place the UNIQUE function in this formula.  The need the formula to return only show one of each ROUTES.  

Any help or advice appreciated.

 

JP

 

excel
Formulas and Functions

8 Replies

    • Joseph715's avatar
      Joseph715
      Copper Contributor
      Jun 27, 2025

      Thank you so much for your help.  This is the formula giving my desired columns and returns the data I want - however I am not getting UNIQUE values in the ROUTE column. (raw_data_tbl has the route column named Service Route)  Any further help is so greatly appreciated.

      =UNIQUE(CHOOSECOLS( SORT(FILTER(Raw_Data_tbl, Raw_Data_tbl[Agency Code]>0,""), {1,2}, {-1,-1}), 15,16,17,7,18,4,19))

       

      • SnowMan55's avatar
        SnowMan55
        Bronze Contributor
        Jun 27, 2025

        << I am not getting UNIQUE values in the ROUTE column >>
        Correct.  The UNIQUE function, when applied to a 2-dimensional range, returns a row for each "unique" (distinct, actually, by default) combination of values.  The Microsoft documentation does a poor job of displaying this; even this Ablebits.com tutorial is weak on it.

        So if you do not want multiple rows, exclude the columns (via omission in the CHOOSECOLS function, most likely) that would result in multiple rows before you apply the UNIQUE function.

        In the sample data you show, the Departure Time is the same for Route 1DO2 in both entries,there is only one truck identifier, and it is used for both Shipping and Receiving, and at multiple Doors.  

        OTOH, if what you want is seven lists of independent distinct values, one from each of the columns that stand next to each other, then your multi-column sort order criteria has no meaning.  You could use seven formulas, but...

        See the changes in the attached workbook.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jun 27, 2025

      As a comment, I tried on 1M rows x 10 columns array, UNIQUE( SORT( data ) ) is about 10% faster compare to SORT( UNIQUE( data ) )

      • Joseph715's avatar
        Joseph715
        Copper Contributor
        Jun 27, 2025

        Thank you for your comment.  

        If I can pick your brain for a minute.  This is the formula giving my desired columns and returns the data I want - however I am not getting UNIQUE values in the ROUTE column. (raw_data_tbl has the route column named Service Route)  Any further help is so greatly appreciated.

        =UNIQUE(CHOOSECOLS( SORT(FILTER(Raw_Data_tbl, Raw_Data_tbl[Agency Code]>0,""), {1,2}, {-1,-1}), 15,16,17,7,18,4,19))

         

        raw_data_tbl

        results

Resources