Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparison of records between two spreadsheets
We have two spreadsheets that are identical, one went through a filtering
process in an attempt to automate the reporting of the records. After doing so we are noticed there were a number of records missing. Instead of manually going through the sheets, we would like to set something up to automatically tell us which records/rows are missing from the other sheet. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparison of records between two spreadsheets
Carolyn
You can use the following macro. Make sure the first sheet, the sheet that has all the data, is the active sheet. I assumed in this macro that the sheet with the missing data is named "Two". Change that as you wish. This macro loops through all the entries in Column A (starting with A2) of the first sheet and checks if that entry is in Column A of the second sheet. If it is NOT, the macro will color that cell in the first sheet RED. Change the color as you wish also. HTH Otto Sub FindMissingRecords() Dim ColAOne As Range Dim ColATwo As Range Dim i As Range Set ColAOne = Range("A2", Range("A" & Rows.Count).End(xlUp)) With Sheets("Two") Set ColATwo = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) End With For Each i In ColAOne If ColATwo.Find(What:=i.Value, LookAt:=xlWhole) Is Nothing Then i.Interior.ColorIndex = 3 Next i End Sub "Carolyn at VW" <Carolyn at wrote in message ... We have two spreadsheets that are identical, one went through a filtering process in an attempt to automate the reporting of the records. After doing so we are noticed there were a number of records missing. Instead of manually going through the sheets, we would like to set something up to automatically tell us which records/rows are missing from the other sheet. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparison of records between two spreadsheets
Thanks for your quick response, I created the macro as you stated, I copied
the second file into another worksheet and named it "Two" as you indicated but I'm receiving a compiling error and was wondering if I could ask for your help again. The error is "Next without for" When I hit the help key this is the response: "A Next statement must have a preceding For statement that matches. This error has the following cause and solution: A Next statement is used without a corresponding For statement. Check other control structures within the For...Next structure and verify that they are correctly matched. For example, an If without a matching End If inside the For...Next structure generates this error. "Carolyn at VW" wrote: We have two spreadsheets that are identical, one went through a filtering process in an attempt to automate the reporting of the records. After doing so we are noticed there were a number of records missing. Instead of manually going through the sheets, we would like to set something up to automatically tell us which records/rows are missing from the other sheet. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparison of records between two spreadsheets
I am replying to this one even though I intended to reply to the last message
(but this one has the code I wanted to refer to.) I believe I can point you in the right direction based on the error you were getting and the code offerred. Most likely you are getting an error because of unintended line wrap. The If statement should either be entirely on one line (which means that i.Interior.ColorIndex = 3 should be on the same line as the If statement, or you need to use an End If statement on the line following i.Interior.ColorIndex = 3. Either should work but because of line wrap it appears that that line is on a line all by itself (and there is no End if) This is more than likely why you are getting the error you are seeing. HTH -- Kevin Vaughn "Otto Moehrbach" wrote: Carolyn You can use the following macro. Make sure the first sheet, the sheet that has all the data, is the active sheet. I assumed in this macro that the sheet with the missing data is named "Two". Change that as you wish. This macro loops through all the entries in Column A (starting with A2) of the first sheet and checks if that entry is in Column A of the second sheet. If it is NOT, the macro will color that cell in the first sheet RED. Change the color as you wish also. HTH Otto Sub FindMissingRecords() Dim ColAOne As Range Dim ColATwo As Range Dim i As Range Set ColAOne = Range("A2", Range("A" & Rows.Count).End(xlUp)) With Sheets("Two") Set ColATwo = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) End With For Each i In ColAOne If ColATwo.Find(What:=i.Value, LookAt:=xlWhole) Is Nothing Then i.Interior.ColorIndex = 3 Next i End Sub "Carolyn at VW" <Carolyn at wrote in message ... We have two spreadsheets that are identical, one went through a filtering process in an attempt to automate the reporting of the records. After doing so we are noticed there were a number of records missing. Instead of manually going through the sheets, we would like to set something up to automatically tell us which records/rows are missing from the other sheet. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparison of records between two spreadsheets
Carolyn
Either do what Kevin suggests or have "Then" at the end of the line and add a space and an underscore character after the Then, as in "Then _". Don't leave out the space. Otto "Carolyn at VW" wrote in message ... Thanks for your quick response, I created the macro as you stated, I copied the second file into another worksheet and named it "Two" as you indicated but I'm receiving a compiling error and was wondering if I could ask for your help again. The error is "Next without for" When I hit the help key this is the response: "A Next statement must have a preceding For statement that matches. This error has the following cause and solution: A Next statement is used without a corresponding For statement. Check other control structures within the For...Next structure and verify that they are correctly matched. For example, an If without a matching End If inside the For...Next structure generates this error. "Carolyn at VW" wrote: We have two spreadsheets that are identical, one went through a filtering process in an attempt to automate the reporting of the records. After doing so we are noticed there were a number of records missing. Instead of manually going through the sheets, we would like to set something up to automatically tell us which records/rows are missing from the other sheet. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Comparison of records between two spreadsheets
Right. I forgot that one, didn't I? :)
-- Kevin Vaughn "Otto Moehrbach" wrote: Carolyn Either do what Kevin suggests or have "Then" at the end of the line and add a space and an underscore character after the Then, as in "Then _". Don't leave out the space. Otto "Carolyn at VW" wrote in message ... Thanks for your quick response, I created the macro as you stated, I copied the second file into another worksheet and named it "Two" as you indicated but I'm receiving a compiling error and was wondering if I could ask for your help again. The error is "Next without for" When I hit the help key this is the response: "A Next statement must have a preceding For statement that matches. This error has the following cause and solution: A Next statement is used without a corresponding For statement. Check other control structures within the For...Next structure and verify that they are correctly matched. For example, an If without a matching End If inside the For...Next structure generates this error. "Carolyn at VW" wrote: We have two spreadsheets that are identical, one went through a filtering process in an attempt to automate the reporting of the records. After doing so we are noticed there were a number of records missing. Instead of manually going through the sheets, we would like to set something up to automatically tell us which records/rows are missing from the other sheet. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check and Remove Records | Excel Discussion (Misc queries) | |||
Comparison tool between 2 spreadsheets? | Excel Discussion (Misc queries) | |||
Deleting specific records | Excel Discussion (Misc queries) | |||
compare two spreadsheets and identify records that have any change | Excel Discussion (Misc queries) | |||
Comparison of data between three spreadsheets | Excel Worksheet Functions |