Unique function with sorting and exceptions

[igorcmendonca]

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 second sheet, in which I identify all unique values of the Column I.

So at first, I created a spill with the Unique Function:

=UNIQUE(Sheet1!I:I)

With this spill, I manage to create a whole new table, using SUMIF. Like This:

OK, but this table is actually just an intermediary for many others. And I need to sort this table in a custom order, which is not necessarily alphabetical. The order criteria is defined buy a third table (call it Sheet_3). And this criteria is established in one row - the first one on Sheet_3:

Not only I have a problem with comparing columns with rows... these are also of different sizes: Sheet_1 changes its number of rows with values everyday. Today it has 24.405; tomorrow it might be 23.333.

Note: Can't use dynamic table...

I already burned every neuron I had... can anyone help me out?

(I can't upload the original file, so I created an improvised example)

Thanks!

[#2]

@igorcmendonca 

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.

[#3]

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?

[#4]

@igorcmendonca 

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 )