User Rating: 5 / 5     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.