Return Values which are Duplicates in 2 Columns ie. which are Common in 2 Columns
Extract Duplicates / Non-Duplicates in 2 Columns – which are Common / Not-Common in 2 Columns
Column D: List all values which are common (Duplicates) in both columns A & B – covers Duplicates in BOTH columns & NOT within Column;
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”.
———————————————————————————————–
List all values which are common (Duplicates) in both columns A & B – covers Duplicates in BOTH columns & NOT within Column.
Cell D2 formula:
=IFERROR(INDEX(A$2:A$20, MATCH(0, INDEX( COUNTIF(D$1:D1, A$2:A$20)+ (COUNTIF( B$2:B$20, A$2:A$20)=0),), 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) COUNTIF(B$2:B$20, A$2:A$20): returns an array of values comprising of numbers and zeros – {0;0;2; 0;0;1; 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 3rd position indicates that cell A4 (3rd cell) value of “CCC” appears twice in column B (in cells B10 & B12), the next number 1 in 6th position indicates that cell A7 (6th cell) value of “FFF” appears once in column B (ie. cell B8), and so on. Equating with zero – “COUNTIF( B$2:B$20, A$2:A$20)=0” – returns TRUE for column A values which do not appear in column B while FALSE indicates column A values which also appear in column B, and when used in calculation TRUE evaluates to the number 1, and FALSE evaluates to the number 0.
3) COUNTIF(D$1:D1, A$2:A$20)+ (COUNTIF( B$2:B$20, A$2:A$20)=0): returns an array of zeros and 1s – {1;1;0; 1;1;0;1; 1;1;0;1; 1;1; 1;1; 1;1;1;1} – where zeros indicate non-blank cells of column A with values which also appear in column B and which do not appear in the preceding cells of the formula column (column D). INDEX is used with this to make it a non-array formula. This is matched with zero (lookup_value) to return position of cells with these values and then using “INDEX(A$2:A$20” returns the values from column A which also appear (ie. which are common) in column B.
———————————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Column E: List all values which are common (Duplicates) in both columns A & B OR within same column. This uses INDEX, MATCH and COUNTIF functions and is an Array formula. This also refers to previous cells in the column (similar to cell D2 formula) – refer the abovementioned Note – if cell D2 formula is in a different column, replace “D$1:D1” in the formula with that column. Enter Array formula (Ctrl+Shift+Enter) in cell E2 and copy down.
Column F: List all values which are NOT common (Non-Duplicates) in columns A & B, irrespective of values being repeated within same column or not. This uses INDEX, MATCH and COUNTIF functions and is a Non-Array formula. This also refers to previous cells in the column (similar to cell D2 formula) – refer the abovementioned Note – if cell D2 formula is in a different column, replace “D$1:D1” in the formula with that column. Enter normal / non-array formula in cell F2 and copy down.
Column G: List all values which are NOT common (Non-Duplicates) in columns A & B, and list values ONLY if Non-Duplicates (ie. not repeated) within same column. This uses INDEX, MATCH and COUNTIF functions and is a Non-Array formula. This also refers to previous cells in the column (similar to cell D2 formula) – refer the abovementioned Note – if cell D2 formula is in a different column, replace “D$1:D1” in the formula with that column. Enter normal / non-array formula in cell G2 and copy down.