Forum Discussion

igorcmendonca's avatar
igorcmendonca
Copper Contributor
Sep 01, 2020

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!

 

 

excel
Formulas and Functions
office 365

3 Replies

Sort By
    • igorcmendonca's avatar
      igorcmendonca
      Copper Contributor
      Sep 03, 2020
      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?
      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Sep 03, 2020

        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 )

         

         

Resources