Highlighted
New Contributor

# 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 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!

3 Replies
Highlighted

# Re: 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.

Highlighted

# Re: Unique function with sorting and exceptions

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?
Highlighted

# Re: Unique function with sorting and exceptions

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.

To remove any unused categories, this might become

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]),