User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

Return Unique List of values, Sorted in Ascending Order, which appear only in One Column and NOT the other

 

Related Links: 

1. Excel VLOOKP Function, with examples.

2. Left Lookup with VLookup Excel function.

3. Left Lookup, with Index, Match & Offset Excel functions.

4. Vlookup Multiple Values - Return MULTIPLE corresponding values for ONE Lookup Value.

 


 

Return Unique List of values, Sorted in Ascending Order, which appear only in One Column and NOT the other

 

List column A values which do not appear in column B - in columns D, E, G &H, duly sorted in Ascending Order. Column A can have either ONLY Text Values or ONLY Numericals, but not both;

 

Columns D & E: List all values from column A as many times they appear in column A;

 

Column G & H: List all values from column A only once, even if they appear multiple times in column A;

 

Enter Array formulas (Ctrl+Shift+Enter) in cell D2 / G2 and copy down; Enter normal (Non-Array) formulas in cell E2 / H2 and copy down;

 

Note: If cell G2 formula is in a different column, replace "G$1:G1" in the formula with that column (ex. if formula is in column F then update to "F$1:F1"); if cell G2 formula starts from a different cell, say G16 instead of G2, then replace "G$1:G1" with "G$15:G15"; do similarly for cell H2 formula;

 

-----------------------------------------------------------------------------------------------

 

List column A values in column D, as many times they appear in column A, which do not appear in column B, duly sorted in Ascending Order

 

 

Unique List Sorted - Values in One Column, Not in Other

"Open Image in New Tab" for a full and clear view. 

 

   

Cell D2 formula:

 

=IFERROR(INDEX(A$2:A$20, MATCH(SMALL( IF((A$2:A$20="")+ COUNTIF(B$2:B$20, A$2:A$20)=0, COUNTIF(A$2:A$20, "<"& A$2:A$20)), ROW(A1)), IF((A$2:A$20="")+ COUNTIF(B$2:B$20, A$2:A$20)=0, COUNTIF( A$2:A$20, "<"& A$2:A$20)), 0)),"") 

 

Explanation - Cell D2 formula (Array formula - Ctrl+Shift+Enter):

 

1) IF((A$2:A$20="")+ COUNTIF(B$2:B$20, A$2:A$20)=0, COUNTIF( A$2:A$20, "<"&A$2:A$20)): returns an array of numbers and FALSE values - {FALSE; 5; FALSE; FALSE; FALSE;7; FALSE; FALSE; 0;0; 5; FALSE; ...} - the numbers are returned for each column A cell value, and is the count of column A values which are less than it. The numbers are returned for only those column A cells: (i) which are not blank, and (ii) whose value is not present in column B. Ex. the 1st FALSE value indicates that cell A2 value ("CCC") is present in column B; the 1st number 5 indicates that there are 5 values in column A ("AAA", "AAA", "BBB", "CCC" & "DDD") which are smaller than ie. alphabetically precede the cell A2 value ("EEE"); and so on. Note: the numbers are returned even for duplicate values in column A where they do not occur in column B. The COUNTIF function using - A$2:A$20, "<"&A$2:A$20 - is used to sort column A values in alphabetical order.

 

2) Using the SMALL function with "ROW(A1)" returns the smallest number, in 1) above, in the 1st cell, the 2nd smallest number in the 2nd cell and so on. Using the MATCH function, each of these "smallest" numbers (this is the lookup_value) is matched with the array of all the numbers (lookup_array). The smallest number indicates that the least number of other column A values alphabetically precede it and hence its the highest in alphabetical order. The MATCH function returns 9 in cell A2 (the smallest number zero occurs in the 9th position which is cell A10 value of "AAA"), and used with the INDEX function "INDEX(A$2:A$20," it returns cell A10 value ("AAA"). Because the 2nd smallest is also 9, the same "AAA" value is returned in cell A3. In this way the formula will list all column A values (which do not appear in column B) as many times they appear in column A. 

 

 

Column E: Column E also Lists all values from column A as many times they appear in column A, sorted in Ascending order, similar to listing in column D. The Logic of cell E2 formula is the same as D2 formula except that column E formula is Non-Array / Normal formula whereas column D formula is an Array formula. 

 

Column G: Column G Lists all values (sorted in Ascending order) from column A only once, even if they appear multiple times in column A. This is an Array formula (Ctrl+Shift+Enter). If cell G2 formula is in a different column, replace "G$1:G1" in the formula with that column (ex. if formula is in column F then update to "F$1:F1"); if cell G2 formula starts from a different cell, say G16 instead of G2, then replace "G$1:G1" with "G$15:G15".

 

Column H: Column H formula is similar to column G formula and also Lists all values (sorted in Ascending order) from column A only once, even if they appear multiple times in column A. It also needs to be adjusted like cell G2 formula if the start cell is different. The Logic of cell H2 formula is the same as G2 formula except that column H formula is Non-Array / Normal formula whereas column G formula is an Array formula.

 

 

 

Only One of the Multiple formulas is illustrated above. For full details and explanation refer to Ebook of Excel Formulas.