Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help in comparing two worksheets
worksheetA = contains some manual entry plus data from monthly file
(worksheetB) worksheetB = monthly file that gets updated from another system worksheetC = contains parameters of the search criteria1 = column C, D, E (primary region secondary region, tertiary region) criteria2 = column H, I, J (status, category, type) worksheetD = contains count from worksheetA column A = countA (region) column B = countB (rest) I need to do the following: 1) compare worksheetA and worksheetB using id_number. if id_number on B does not exist on A, then add it on the last empty cell on A if id_number on B exists on A, compare if column M and N. If there is a change, highlight it in red. 2) after the above compare, I need to revise the count on worksheetD (column A and B) based on criteria on worksheetC I just need a sample code to get me started. Many thanks for everybody's time. -Bob |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help in comparing two worksheets
This code should get you started
Sub comparesheet() With Sheets("Sheet1") Sh1Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row Set Sh1IDRange = .Columns("A:A") End With With Sheets("Sheet2") Sh2LastRow = _ .Cells(Rows.Count, "A").End(xlUp).Row Set Sh2IDRange = .Range("A1:A" & Sh2LastRow) For Each cell In Sh2IDRange Set c = Sh1IDRange.Find( _ what:=cell, LookIn:=xlValues) If c Is Nothing Then Sh1Lastrow = Sh1Lastrow + 1 cell.EntireRow.Copy Destination:= _ Sheets("Sheet1"). _ Rows(Sh1Lastrow) Else Criteria_1 = .Cells(cell.Row, "M") Criteria_2 = .Cells(cell.Row, "N") With Sheets("Sheet1") If (.Cells(c.Row, "M") < Criteria_1) Or _ (.Cells(c.Row, "N") < Criteria_2) Then .Cells(c.Row, "M").Interior.ColorIndex = 3 .Cells(c.Row, "N").Interior.ColorIndex = 3 End If End With End If Next cell End With End Sub "casey" wrote: worksheetA = contains some manual entry plus data from monthly file (worksheetB) worksheetB = monthly file that gets updated from another system worksheetC = contains parameters of the search criteria1 = column C, D, E (primary region secondary region, tertiary region) criteria2 = column H, I, J (status, category, type) worksheetD = contains count from worksheetA column A = countA (region) column B = countB (rest) I need to do the following: 1) compare worksheetA and worksheetB using id_number. if id_number on B does not exist on A, then add it on the last empty cell on A if id_number on B exists on A, compare if column M and N. If there is a change, highlight it in red. 2) after the above compare, I need to revise the count on worksheetD (column A and B) based on criteria on worksheetC I just need a sample code to get me started. Many thanks for everybody's time. -Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing worksheets | Excel Worksheet Functions | |||
Comparing two worksheets | Excel Discussion (Misc queries) | |||
Comparing 5 worksheets | Excel Worksheet Functions | |||
Comparing two different Worksheets | Excel Worksheet Functions | |||
Comparing Two Worksheets | Excel Worksheet Functions |