Return Unique List of values, Sorted in Ascending Order, which appear only in One Column and NOT the other
Return Unique List of values, Sorted in Ascending Order, which appear only in One Column and NOT the other
Column D: List column A values which do not appear in column B, duly sorted in Ascending Order. Column A can have either ONLY Text Values or ONLY Numericals, but not both.
Enter Array formula (Ctrl+Shift+Enter) in cell D2 and copy down.
———————————————————————————————–
In Column D: List column A values as many times they appear in column A and which do not appear in column B, duly sorted in Ascending Order
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.
———————————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
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.