Forum Discussion
Order of multiple functions in a formula.
It can be done in one of a number of orders. See the attached workbook.
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))
<< 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.
Simply - WOW. Thank you. I made some modifications and took advantage of conditional formatting to achieve the desired results. You have made me a very happy man. I have learned a bunch from your help. Thank you again.
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.
