- #COMPARE TWO COLUMNS IN EXCEL AND FIND DIFFERENCES HOW TO#
- #COMPARE TWO COLUMNS IN EXCEL AND FIND DIFFERENCES CODE#
- #COMPARE TWO COLUMNS IN EXCEL AND FIND DIFFERENCES DOWNLOAD#
#COMPARE TWO COLUMNS IN EXCEL AND FIND DIFFERENCES CODE#
Now, it will open Microsoft Visual Basic for Applications.Ī Module will open then type the following code in the opened Module. To begin with, open the Developer tab > select Visual Basic Here, I have introduced two extra columns named Unique Name and Unique Address.
#COMPARE TWO COLUMNS IN EXCEL AND FIND DIFFERENCES HOW TO#
In this section, I’ll show you how to do that using VBA macro. You may need to extract unique values by comparing two columns. Using Macro to Extract Unique Values From Two Columns
Next, open the View tab > from Macros > select View Macros Then, if the compared value is equal then it will put the values in the adjacent column. Also used, FOR loops to check all values of Selection and CompareRange. I used an IF statement to compare whether the value of x and y is equal or not. Provided the cell range F5:F11 to Compare values. Here, I declared the Sub procedure Find_Matches_Comaring2Columns where CompareRange, x, and y are Variant type variables. Sub Find_Matches_Comaring2Columns()ĭim CompareRange As Variant, x As Variant, y As Variant Then, it will open Microsoft Visual Basic for Applications.Ī Module will open then type the following code in the opened Module. To begin the procedure, open the Developer tab > select Visual Basic Here, I want to find the matches of the Email_ID column. To compare two columns and to find matches you can use the VBA Macro as well. Using Macro to Find Matches Comparing Two Columns Related Content: Excel formula to compare two columns and return a value (5 examples) 2. Here both columns unique values are Highlighted.
Hence, it will Highlight all the unique values of two-column.Īgain, you can run the Macro to compare Addresses. Now, from the Macro name select the Highlighting_Comparing_2Columns also select the workbook within Macros in. Next, open the View tab > from Macros > select View Macros Then, select the cell range to apply the VBA Then, if the compared value is unique by row, then it will Highlight it with Yellow color as I used ColorIndex = 6.įinally, Save the code and go back to the worksheet. I used an IF statement to compare the values of the selected cell range, also I used a FOR loop to check all the rows. Here, I declared the Sub procedure Highlighting_Comparing_2Columns where Twocolumns is Range type and i is Integer type variables. Next, it will open Microsoft Visual Basic for Applications.įrom there, open Insert > select Module.Ī Module will open then type the following code in the opened Module. You also can use the ALT + F11 keyboard to open the VBA editor. Now, open the Developer tab > select Visual Basic To do it I will take the same type of columns together.
Using Macro to Highlight Unique Values Comparing Two Columnsīy using VBA Macro, you can Highlight the Unique values of two columns. Now, to check the rows for all the other columns, I can hold down my Ctrl key and highlight the next pair of columns and hit the F4 key and it will continue to highlight the cells for those that are different.4 Ways to Use Excel Macro to Compare Two Columns 1. In this case it highlighted cell B7 where "moulding" was spelled incorrectly, and B9 where the description was totally different. Now when I click on OK, it will highlight the cells in column B that are different than those in column K: Then I'll use my keyboard to enter the following keystrokes: To start, we'll highlight the two description columns, which are columns B & K: Now, with this process you can only compare two columns of data at a time, but once you've compared the first set of columns, the rest is quite easy. I can painstakingly go through each line to try to find the differences, or I can solicit Excel's help through the "Go To Special" command. Here I have two sets of data that appear to be the same:īut when I scroll down to the bottom I can see that the totals are slightly different:
#COMPARE TWO COLUMNS IN EXCEL AND FIND DIFFERENCES DOWNLOAD#
If you get a preview, look for the download arrow in the upper right hand corner. You can download the file here and follow along. We'll use the Go To Special command to help us highlight those differences. In this tutorial we are going to compare two data ranges that were supposed to be exact, but have some minor differences.