How
do I match data from 2 worksheets and highlight the differences using
MS-Excel VBA?
|
||||
| Excel 2007 | Excel 2003 | Macros Excel 2007 | Macros Excel 2003 | Excel 2010 |
|
|
||||
|
|
Let's say I have
some data in sheet1 in coulumn A which is my reference data. I also
have data in sheet2 in column A which I get from my colleague in
another city. Every evening we have to reply back whether the data
matches or some of the data is different. I work for a courier company
and the data consists of Airway Bills. I know how to solve this problem
with Vlookup. I want to automate the process and also highlight the
differences in sheet2. Can this be done through VBA in Excel? You can easily solve this problem using two methods in Excel VBA: (i) You can use a nested loop but with large amounts of data the process can become slow. This process compares each value in sheet1 with every value in sheet2. It then goes to the next value in sheet 1 and again compares the value with each value in sheet2 till the last value in sheet1 has been checked. (ii) You can use the 'collections' feature in Excel VBA. Collections are a kind of arrays but with some key differences like: (a) A 'collection' does not have to have data of the same type. (ii) You can easily add items to a collection and memory allocation happens dynamically. A 'collection' object uses 4 methods: Add, Count, Item and Remove. The Add method, for example, is implemented as follows: Collection.Add item, [key],[before],[after] You can use the add method to create a list of unique data. In our example we'll take the item value and assign it to the key value. Now duplicate items cannot be added to the list. If you should add data with a duplicate key, there will be an error. You can now use 'on error resume next' to bypass the error. In this manner you can skip the duplicates. Now we'll loop through our range in sheet1 and create a collection of unique values. Next we'll loop through the data in sheet2 and add data to the collection only if its different and skip the duplicates using 'on error resume next'. Also we'll add the different data to the beginning of the collection. If no error occurs then the value was not in the collection and therefore also not in our sheet1 data. At this point of time we can also format the different data in sheet2 to highlight it. Before going out of the loop we can then 'remove' the added data from the collection. This ensures that our collection contains only the unique items from sheet1. We do some initializations at the end so that our process works automatically. The macro-code Sub MatchData() 'we define our ranges and collection Dim R1 As Range Dim R2 As Range Dim R As Range Dim Nc As New Collection 'we select our range R1 in sheet1 With Worksheets("Sheet1") Set R1 = Intersect(.Range("A:A"), .UsedRange) End With 'we select our range R2 in sheet2 With Worksheets("Sheet2") Set R2 = Intersect(.Range("A:A"), .UsedRange) End With 'Don't display an error message if encounter a duplicate On Error Resume Next 'Create a collection from range R1 in sheet1 For Each R In R1 Nc.Add R.Value, R.Value Next R 'Now we loop through the data in sheet2 and check whether the same data is in sheet1 For Each R In R2 'Reset the errors to 0 Err = 0 'If the value is not in the collection add it to the beginning of the collection Nc.Add R.Value, R.Value, 1 'If no error occured then the value wasn't found in the collection and therefore also not on sheet 1 If Err = 0 Then 'Fill the cell with red color to highligh it R.Interior.Color = RGB(248, 78, 54) 'set the font color to white R.Font.ColorIndex = 2 'Remove the first item in the collection. This is the last item that was added 'This ensures that our collection contains only the unique items from sheet1 Nc.Remove 1 End If Next R 'Clear errors Err = 0 'Resume standard error handling On Error GoTo 0 End Sub The training video below shows the details of performing the calculations without a macro: More Tips (Solutions): How do I view and edit Microsoft Excel files on an iPad? Inventory Soluton How to rename and color worksheet tabs Copy Worksheet Quickly Criteria Range Animations in Microsoft Excel Create two charts at once How to avoid errors while working in Excel How to benefit from Microsoft Excel Templates How to use Microsoft Excel 2007 with Word 2007 - create mail merge labels Landscape Oriented Worksheet Template How to-make a worksheet fit a printed page How to make your charts more impressive Perpetual Calendar from 1900 to 9999 How to create your own custom add-in How to clear conditional formattng in Excel worksheets How to speed up data entry of decimal numbers How to change the color of the gridlines of an Excel worksheet or hide them How to use Autosum in Multiple Worksheet Cells Quickly How to have your free personal assistant in Mcrosoft Excel who reads out the data to you How to use the status bar in Microsoft Excel to do a quick data analysis How to calculate equal monthly payments or instalments using a mathematical equation Data Forms to Enter and Edit Data View 2 worksheets in same workbook side by side Accessing a specific worksheet in large workbooks with multiple worksheets Referencing a cell in another worksheet How to perform a what-if analysis using a scrollbar form control An interesting payroll solution Future Value Solution in Microsoft Excel Another Interesting Conditional Formatting Solution DSUM and Array Formulas for addition solutions IF Function Question How to use command buttons on a splash screen in MS-Excel Copy Paste Data from one Excel Worksheet to Another How to separate comma separated values (csv) in a worksheet cell in Microsoft Excel into rows or columns Another Interesting Solver solution in Microsoft Excel Find duplicates in two different Excel worksheets using a macro How to count data based on multiple criteria - countifs function How to convert 6/5/2011 into Sunday, 6/5/2011 User Form to perform calculations An interesting solution using conditional formatting in Microsoft Excel 2007 Show/Hide Controls on a User-Form using a Checkbox Populate Listbox and Textboxes with Excel data using VBA and Vlookup Protecting Specific Worksheets in an Excel Workbook using VBA How to calculate the difference in hours between two date-time values |
|||
|
|
||||
| Excel 2007 | Excel 2003 | Macros Excel 2007 | Macros Excel 2003 | Excel 2010 |
|
|
||||
|
|
||||