I am trying to use the new dynamic arrays (currently still only available on the office insider releases) to pull a list of sorted unique values from across multiple columns into 1 column. I can not seem to figure it out though.
The scenario: A row represents a project and there can be unlimited rows.
Each "c#" column value is a company who has requested info for that project. The names are not entered in any particular order.
Ultimately I will be using the unique column as data validation for the "c#" values to choose from. This way, as a unique value which is not already present gets added to one of the columns, the data validation column will have that value as an option in the future to help maintain data integrity (correct spelling, formatting, etc.)
The attached workbook has the base data table, an example of the unique values from each "c#" column, an example of those values stacked, an example of the unique values overall, and then the example of how the finals result should look (the unique values sorted). I also have yellow highlighted cell where the solution can go. I am trying to avoid helper columns and only use a single cell formula that uses the new dynamic arrays.
Any help would be appreciated because at this point I think my head hurts from trying to sort through this. I just don't think I know enough other excel formula tricks that would assist in making this possible.
Thanks in advance for any help.
Chris
Bookmarks