Return Unique List of values which appear only in One Column and NOT the other – or Unique List from Both Columns
Return Unique List of Values from a Column which do not appear in the Other Column
Column D: Extract Unique List of values from column A (each column A value is extracted to appear only once irrespective of the number of times it appears in column A), excluding those which appear in column B;
Enter non-array formula in cell D2 and copy down.
Note: If cell D2 formula is in a different column, replace “D$1:D1” in the formula with that column (ex. if formula is in column J then update to “J$1:J1”) and if cell D2 formula starts from a different cell, say D16 instead of D2, then replace “D$1:D1” with “D$15:D15”.
———————————————————————————————–
Extract Unique List of values from column A (each column A value is extracted to appear only once irrespective of the number of times it appears in column A), excluding those which appear in column B
Cell D2 formula:
=IFERROR(INDEX(A$2:A$20, MATCH(0, INDEX( COUNTIF( D$1:D1, A$2:A$20)+ (A$2:A$20=””)+ COUNTIF(B$2:B$20, A$2:A$20),), 0)),””)
Explanation – Cell D2 formula (non-Array formula):
1) COUNTIF(D$1:D1, A$2:A$20): counts values of column A in the preceding cells of the formula column (column D) as the formula gets copied down. It returns an array of 1s and zeros, with 1 indicating column A values which already occur in these preceding cells.
2) (A$2:A$20=””): returns TRUE for blank cells. When used in calculation TRUE evaluates to the number 1, and FALSE evaluates to the number 0.
3) COUNTIF(B$2:B$20,A$2:A$20): returns an array of values comprising of numbers and zeros – {0;2;0; 1;0;0; 0;0;0; 1;0; 0;0; 0;0;0; 0;0;0} – the numbers indicate the number of times each column A cell value appears in column B. Zeros indicate blank cells or if a column A value does not appear in column B. For example, the 1st number 2 in 2nd position indicates that cell A2 (2nd cell) value of “BBB” appears twice in column B (in cells B2 & B10), the next number 1 in 4th position indicates that cell A5 (4th cell) value of “DDD” appears once in column B (ie. cell B7), and so on.
4) COUNTIF(D$1:D1, A$2:A$20)+ (A$2:A$20=””)+ COUNTIF( B$2:B$20, A$2:A$20): this combines the 1), 2) and 3) above to return an array of numbers and zeros – {0;2;1; 1;0;0; 1;1;0; 1;0;1; 1;1;1; 1;1; 1;1} – with zeros indicating where all the 3 above conditions are satisfied. For example, the 1st zero indicates that cell A2 (1st cell) is not blank, and its value of “AAA” does not appear in column B, and it also does not appear in any preceding cell of the formula column (ie. column D). INDEX is used with this to make it a non-array formula.
5) The array in 4) above is matched with zero (ie. lookup_value of zero) to determine the position of zeros which indicate: (i) column A value not occurring in the preceding cells, (ii) blank cells in column A are skipped, and (iii) column A cell value does not occur in column B. Using “INDEX(A$2:A$20” returns the corresponding values from column A.
———————————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Column E: Extract Unique List of values from column B (each column B value is extracted to appear only once irrespective of the number of times it appears in column B), excluding those which appear in column A. The Logic of cell E2 formula is the same as D2 formula except that columns A-B are reversed and in column E the Unique values are returned from column B which do not appear in column A.
Column F: List ALL values (ie. as many times as they appear) from column A, excluding those which appear in column B. Uses the INDEX-SMALL functions with COUNTIF to List ALL values (ie. as many times as they appear) from column A, excluding those which appear in column B – Array formula. This formula is not dependent on previous cells of the column like column D formula ie. cell F2 formula can be entered in any cell without requiring a change as mentioned in the abovementioned Note (If cell D2 formula is in a different column, replace “D$1:D1” in the formula with that column ….).
Column G: List ALL values (ie. as many times as they appear) from column A, excluding those which appear in column B. Uses the AGGREGATE function with COUNTIF to List ALL values (ie. as many times as they appear) from column A, excluding those which appear in column B – Non-Array formula. This formula is not dependent on previous cells of the column like column D formula ie. cell G2 formula can be entered in any cell without requiring a change as mentioned in the abovementioned Note (If cell D2 formula is in a different column, replace “D$1:D1” in the formula with that column ….).
Column H: Unique List of values from BOTH column A & B – all column A & B values are extracted to appear only once irrespective if it appears in the other column or not (if a value appears in both columns A & B it is extracted only once) and irrespective of the number of times a value appears in its respective column (if a value appears multiple times in a column it is extracted only once). Uses the INDEX-MATCH functions with COUNTIF to return a Unique List of values from BOTH column A & B – Non-Array formula.