Forum Discussion
Order of multiple functions in a formula.
As a comment, I tried on 1M rows x 10 columns array, UNIQUE( SORT( data ) ) is about 10% faster compare to SORT( UNIQUE( data ) )
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
You have unique result for all 7 columns, not for the Route. If only Route shall be unique it's not clear what to with the rest fields. For example, First Delivery - take minimum time, or combine all times, or what. Same for other fields. Perhaps for above sample you may generate desired result manually and share with us.
Thank you again for your response. This is the result from the initial formula I posted. I want to FILTER and SORT the data and select the columns I want shown. But, I want to weed out the duplicates in the ROUTE column - to show the each ROUTE only once. (1DO2,1DL1,DO1, DO2, 1DO3...) Basically, each route has several stops. I only need one of the stops for each route.
I have attached the data and the report form. And again - I am grateful for any help you can provide me.