Forum Discussion
Unique function with sorting and exceptions
Not sure what the problem is; seems to be going OK this far.
I do not see why you shouldn't use a table for the input data.
In your example the transpose of headers in sheet 3 is the same as the sorted-unique values from sheet 1.
Nevertheless, you solved something very important to me now: by using the first row as headers and using the Table function, you freed me of the title of the column, which was disturbing my sort.
Problem is, Sheet_1 and Sheet_3 aren't always this smooth, alphabetical order. Sometimes, new values appear in Sheet_1 Column I that does not exist yet in Sheet_3. When this happens, how can I detect it? Will I need to keep the comparison table you created on Sheet_2?
If you want the order of presentation to be determined by Sheet_3 then you could simply use the transpose of the header row as the criterion column.
= TRANSPOSE(headers)
To remove any unused categories, this might become
= TRANSPOSE( FILTER( headers, COUNTIFS(Table1[NO_MOEDA], headers) ) )
To find whether there are any Sheet_1 column 1 entries that do not yet appear on Sheet_3, either XLOOKUP or COUNTIFS will detect the absence of a matching header.
Step 1: = UNIQUE(Table1[NO_MOEDA])
Step 2: = COUNTIFS( headers, UNIQUE(Table1[NO_MOEDA]) )
If you have access to the LET function this could be written
= LET( unique, UNIQUE(Table1[NO_MOEDA]), count, COUNTIFS(headers, unique), missingEntries, FILTER(unique, NOT(count)), missingEntries )