User Rating: 5 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Active
 

 

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.

  

List Duplicates in 2 Columns

  

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.