\n2.<\/strong> By Using Excel Macro<\/p>\n
Compare two columns by using Excel Formulas:<\/h3>\n
By using ISERROR<\/strong> and MATCH<\/strong> formula we can compare 2 columns. Where:<\/strong> Compare Two Column in Excel Formula<\/p><\/div>\n Note:<\/strong> In the above formula, whichever cell it is finding as duplicate in Column A, it will populate in the same row. As shown in the above image.<\/p>\n Many times we require to compare two columns’ data and find out all the Duplicates. So in this article, you are going to see how we can compare two columns in Excel and find out duplicates. There are two ways of comparing two columns in an Excel Worksheet. 1.By Using Excel Formula 2. By Using […]<\/p>\n","protected":false},"author":45,"featured_media":242555,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1673,1246],"tags":[],"class_list":["post-12114","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-functions","category-macro"],"yoast_head":"\n
\nType the below formula in the column where you want the list of all duplicates in both the column<\/p>\n=IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),\"\",A1) <\/code><\/pre>\n
\nA1 –<\/strong> Is the column Which you want to be compared
\n$C$1:$C$5 –<\/strong> Is the Range which you want compared with<\/p>\n![\"Compare](\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2012\/01\/Compare-two-column-1.png\")
Compare two columns by using Excel Macro:<\/h3>\n
1. This function will compare Column A with Column B and list the Duplicates in Column C in a Sequence.<\/h2>\n
\r\n\r\nPrivate Sub CommandButton1_Click()\r\nDim CompareRange As Variant, To_Be_Compared As Variant, x As Variant, y As Variant\r\n\r\nRange(\"A1\").Select\r\nSelection.End(xlDown).Select\r\nSet To_Be_Compared = Range(\"A1:\" & Selection.Address)\r\nRange(\"B1\").Select\r\nSelection.End(xlDown).Select\r\nSet CompareRange = Range(\"B1:\" & Selection.Address)\r\n\r\n\r\n'If you want to exchange the columns like if you want to\r\n'compare B with A then chnage the range\r\n'and selection or vise-versa\r\n\r\ni = 1\r\nTo_Be_Compared.Select\r\n\r\n For Each x In Selection\r\n For Each y In CompareRange\r\n If x = y Then\r\n Range(\"C\" & i).Value = x\r\n i = i + 1\r\n End If\r\n Next y\r\n Next x\r\nEnd Sub\r\n<\/code><\/pre>\n
2. This function will compare Column A with Column B and list the Duplicates in Column C but not in a Sequence. It will list exactly in the same row which is duplicate.<\/h2>\n
\r\nPrivate Sub CommandButton2_Click()\r\nDim CompareRange As Variant, To_Be_Compared As Variant, x As Variant, y As Variant\r\n\r\nRange(\"A1\").Select\r\nSelection.End(xlDown).Select\r\nSet To_Be_Compared = Range(\"A1:\" & Selection.Address)\r\nRange(\"B1\").Select\r\nSelection.End(xlDown).Select\r\nSet CompareRange = Range(\"B1:\" & Selection.Address)\r\n\r\n'If you want to exchange the columns like if you want to\r\n'compare B with A then chnage the range\r\n'and selection or vise-versa\r\n\r\nTo_Be_Compared.Select\r\n\r\n For Each x In Selection\r\n For Each y In CompareRange\r\n If x = y Then x.Offset(0, 2) = x\r\n Next y\r\n Next x\r\nEnd Sub\r\n<\/code><\/pre>\n
3. This function will ask for Column to Compare, Column To be compared and Column to list the Duplicates. This is completely dynamic. It can complare any Column with any Column and list all the duplicates in any of the column. <\/h2>\n
\r\n\r\nPrivate Sub CommandButton1_Click()\r\nDim CompareRange As Variant, To_Be_Compared As Variant, x As Variant, y As Variant\r\nstr1 = InputBox(\"Enter Column Name to be Compared\")\r\nstr2 = InputBox(\"Enter Column Name to Compare\")\r\nstr3 = InputBox(\"Enter Column Name to put the Result\")\r\nRange(str1 & \"1\").Select\r\nSelection.End(xlDown).Select\r\nSet To_Be_Compared = Range(str1 & \"1:\" & Selection.Address)\r\nRange(str2 & \"1\").Select\r\nSelection.End(xlDown).Select\r\nSet CompareRange = Range(str2 & \"1:\" & Selection.Address)\r\n\r\n\r\n'If you want to exchange the columns like if you want to\r\n'compare B with A then chnage the range\r\n'and selection or vise-versa\r\n\r\ni = 1\r\nTo_Be_Compared.Select\r\n\r\n For Each x In Selection\r\n For Each y In CompareRange\r\n If x = y Then\r\n Range(str3 & i).Value = x\r\n i = i + 1\r\n End If\r\n Next y\r\n Next x\r\nEnd Sub\r\n<\/code><\/pre>\n<\/span>","protected":false},"excerpt":{"rendered":"