Forum Discussion
Unique function with sorting and exceptions
Hi! I have a table in Excel that gains and/or changes its data everyday, keeping only the set of column variables fixed. Like this: What I need to create is another table, on a secon...
Because, in this example, the Sheet_3 is in alphabetical order, which is not always true.
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?
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 )