Generally, it is considered easier to compare the ranges of two sets in a single Excel spreadsheet and to find the duplicate values than comparing them between two distinct spreadsheets. I shall describe the method to find the ranges and duplicate values in columns of two different spreadsheets.
- Compare two columns with similar headers in two spreadsheets in MS Excel
You can compare two columns of two different spreadsheets by using the formula in Excel. A recommended formula is mentioned below. You can apply it as per the steps described below:
Type this formula in a blank cell in a new sheet:
Press the Enter button now.
Select the range that you are comparing with the one in the second spreadsheet. The number ‘0’ implies duplicates in two ranges.
· Compare two columns or ranges in two spreadsheets for VBA
You can also compare ranges for VBA.
Press ALT and F11 simultaneously. It will open Microsoft Visual Basic for Application (VBA) window.
Select Insert > Module, and paste the VBA code in there. Select Run or push F5. It will run VBA code. Select a range that you are comparing and place it in the dialogue box that will appear on the screen as soon as you run the VBA code.
Click on Ok and a new dialogue box will appear for you. You need to select the 2nd range here and to click Ok. It will highlight the duplicate values of both the ranges with red color background in 1st range.
You can follow all the above mentioned steps for comparing ranges for two different spreadsheets for VBA.