Forum Discussion
Order of multiple functions in a formula.
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.