compare two columns in excel highlight matches

#COMPARE TWO COLUMNS IN EXCEL HIGHLIGHT MATCHES CODE#

Set CompareRange = Range(str2 & '1:' & Selection. Set To_Be_Compared = Range(str1 & '1:' & Selection.Address) Str3 = InputBox('Enter Column Name to put the Result') Str2 = InputBox('Enter Column Name to Compare') Str1 = InputBox('Enter Column Name to be Compared')

#COMPARE TWO COLUMNS IN EXCEL HIGHLIGHT MATCHES CODE#

Zero ' 0' means a perfect match, and the sample code we give below highlights the cell if the result is NOT equal to 0. This function returns an integer based on the result of the comparison. It can complare any Column with any Column and list all the duplicates in any of the column. VBA has a built in function called StrComp, which can compare two separate strings. This function will ask for Column to Compare, Column To be compared and Column to list the Duplicates. It will list exactly in the same row which is duplicate.ģ.

This function will compare Column A with Column B and list the Duplicates in Column C but not in a Sequence. 'If you want to exchange the columns like if you want toĢ. Set CompareRange = Range('B1:' & Selection.Address) The result returned by this new formula is TRUE or FALSE. So now we will insert the previous formula into an ISNA function. The function will return 2 values: TRUE: when the function VLOOKUP returns N/A. We will use the ISNA function to customize the result. This time we created the VLOOKUP in the customer’s worksheet. However, as a double check we then decided to do a comparison from the customer’s records, just in case we had missed something.

Set To_Be_Compared = Range('A1:' & Selection.Address) Step 2: Use the ISNA function to perform a test. We had now successfully compared our records to the customer’s. This function will compare Column A with Column B and list the Duplicates in Column C in a Sequence.ĭim CompareRange As Variant, To_Be_Compared As Variant, x As Variant, y As Variant Compare two columns by using Excel Macro: 1. Note: In the above formula, whichever cell it is finding as duplicate in Column A, it will populate in the same row.

Compare two columns in excel highlight matches