Unique function with sorting and exceptions

%3CLINGO-SUB%20id%3D%22lingo-sub-1626463%22%20slang%3D%22en-US%22%3EUnique%20function%20with%20sorting%20and%20exceptions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1626463%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20table%20in%20Excel%20that%20gains%20and%2For%20changes%20its%20data%20everyday%2C%20keeping%20only%20the%20set%20of%20column%20variables%20fixed.%20Like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22igorcmendonca_0-1598996544666.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F215930iA46F17D27CA5698E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22igorcmendonca_0-1598996544666.png%22%20alt%3D%22igorcmendonca_0-1598996544666.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20need%20to%20create%20is%20another%20table%2C%20on%20a%20second%20sheet%2C%20in%20which%20I%20identify%20all%20unique%20values%20of%20the%20Column%20I.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20at%20first%2C%20I%20created%20a%20spill%20with%20the%20Unique%20Function%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DUNIQUE(Sheet1!I%3AI)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20this%20spill%2C%20I%20manage%20to%20create%20a%20whole%20new%20table%2C%20using%20SUMIF.%20Like%20This%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22igorcmendonca_1-1598996808328.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F215931i678803509E8F938B%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22igorcmendonca_1-1598996808328.png%22%20alt%3D%22igorcmendonca_1-1598996808328.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EOK%2C%20but%20this%20table%20is%20actually%20just%20an%20intermediary%20for%20many%20others.%20And%20I%20need%20to%20sort%20this%20table%20in%20a%20custom%20order%2C%20which%20is%20not%20necessarily%20alphabetical.%20The%20order%20criteria%20is%20defined%20buy%20a%20third%20table%20(call%20it%20Sheet_3).%20And%20this%20criteria%20is%20established%20in%20one%20row%20-%20the%20first%20one%20on%20Sheet_3%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22igorcmendonca_2-1598996916467.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F215933iE436A7A5A07D189E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22igorcmendonca_2-1598996916467.png%22%20alt%3D%22igorcmendonca_2-1598996916467.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENot%20only%20I%20have%20a%20problem%20with%20comparing%20columns%20with%20rows...%20these%20are%20also%20of%20different%20sizes%3A%20Sheet_1%20changes%20its%20number%20of%20rows%20with%20values%20everyday.%20Today%20it%20has%2024.405%3B%20tomorrow%20it%20might%20be%2023.333.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%3A%20Can't%20use%20dynamic%20table...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20already%20burned%20every%20neuron%20I%20had...%20can%20anyone%20help%20me%20out%3F%3C%2FP%3E%3CP%3E(I%20can't%20upload%20the%20original%20file%2C%20so%20I%20created%20an%20improvised%20example)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1626463%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1626518%22%20slang%3D%22en-US%22%3ERe%3A%20Unique%20function%20with%20sorting%20and%20exceptions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1626518%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F778752%22%20target%3D%22_blank%22%3E%40igorcmendonca%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENot%20sure%20what%20the%20problem%20is%3B%20seems%20to%20be%20going%20OK%20this%20far.%3C%2FP%3E%3CP%3EI%20do%20not%20see%20why%20you%20shouldn't%20use%20a%20table%20for%20the%20input%20data.%3C%2FP%3E%3CP%3EIn%20your%20example%20the%20transpose%20of%20headers%20in%20sheet%203%20is%20the%20same%20as%20the%20sorted-unique%20values%20from%20sheet%201.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1631976%22%20slang%3D%22en-US%22%3ERe%3A%20Unique%20function%20with%20sorting%20and%20exceptions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1631976%22%20slang%3D%22en-US%22%3EBecause%2C%20in%20this%20example%2C%20the%20Sheet_3%20is%20in%20alphabetical%20order%2C%20which%20is%20not%20always%20true.%3CBR%20%2F%3E%3CBR%20%2F%3ENevertheless%2C%20you%20solved%20something%20very%20important%20to%20me%20now%3A%20by%20using%20the%20first%20row%20as%20headers%20and%20using%20the%20Table%20function%2C%20you%20freed%20me%20of%20the%20title%20of%20the%20column%2C%20which%20was%20disturbing%20my%20sort.%3CBR%20%2F%3E%3CBR%20%2F%3EProblem%20is%2C%20Sheet_1%20and%20Sheet_3%20aren't%20always%20this%20smooth%2C%20alphabetical%20order.%20Sometimes%2C%20new%20values%20appear%20in%20Sheet_1%20Column%20I%20that%20does%20not%20exist%20yet%20in%20Sheet_3.%20When%20this%20happens%2C%20how%20can%20I%20detect%20it%3F%20Will%20I%20need%20to%20keep%20the%20comparison%20table%20you%20created%20on%20Sheet_2%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1632426%22%20slang%3D%22en-US%22%3ERe%3A%20Unique%20function%20with%20sorting%20and%20exceptions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1632426%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F778752%22%20target%3D%22_blank%22%3E%40igorcmendonca%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20want%20the%20order%20of%20presentation%20to%20be%20determined%20by%20Sheet_3%20then%20you%20could%20simply%20use%20the%20transpose%20of%20the%20header%20row%20as%20the%20criterion%20column.%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20TRANSPOSE(headers)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ETo%20remove%20any%20unused%20categories%2C%20this%20might%20become%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20TRANSPOSE(%20FILTER(%20headers%2C%20COUNTIFS(Table1%5BNO_MOEDA%5D%2C%20headers)%20)%20)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20find%20whether%20there%20are%20any%20Sheet_1%20column%201%20entries%20that%20do%20not%20yet%20appear%20on%20Sheet_3%2C%20either%20XLOOKUP%20or%20COUNTIFS%20will%20detect%20the%20absence%20of%20a%20matching%20header.%26nbsp%3B%3C%2FP%3E%3CP%3EStep%201%3A%26nbsp%3B%3CSTRONG%3E%3D%20UNIQUE(Table1%5BNO_MOEDA%5D)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EStep%202%3A%26nbsp%3B%3CSTRONG%3E%3D%20COUNTIFS(%20headers%2C%20UNIQUE(Table1%5BNO_MOEDA%5D)%20)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20access%20to%20the%20LET%20function%20this%20could%20be%20written%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20LET(%0A%20%20unique%2C%20UNIQUE(Table1%5BNO_MOEDA%5D)%2C%0A%20%20count%2C%20%20COUNTIFS(headers%2C%20unique)%2C%0A%20%20missingEntries%2C%20FILTER(unique%2C%20NOT(count))%2C%0A%20%20missingEntries%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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:

 

igorcmendonca_0-1598996544666.png

 

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:

igorcmendonca_1-1598996808328.png

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:

 

igorcmendonca_2-1598996916467.png

 

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

@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.

Highlighted
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

@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 )