Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing worksheets with non-identical rows of information
Hi,
I am a newcomer when it comes to functions and programming in Excel which makes this task all the more difficult. I have a worksheet that is updated continuously with new/added information and information that is deleted. This is an inventory worksheet with retail items listed in rows and aspects of its description located in adjacent cells. Basically, I cannot depend on an item to be located in the same row each time the worksheet is updated since things are added and deleted. What I would like to do is have the original (non-updated) worksheet be compared to the updated worksheet by: 1. Using the info starting from C3 down (the UPC number for the product) in the original worksheet and search for this number in the same column (C) in the updated worksheet. 2. Highlight any differences in columns G, H, I, for the row in question where the identical UPC numbers are found in both worksheets. (Highlighted in the original worksheet) This is a very involved task I would assume, so if an answer cannot be provided, I would certainly appreciate any suggestions regarding reference material. Thank you for your time Glenn Yamada |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing worksheets with non-identical rows of information
This is a very simple program. Written it plenty of times. Can you let me
know the wroksheets names and the column where the UPC number is located. I would recommend to do this task the opposite way from the way you suggested. Go line by line on the updated worksheet and compare to the original worksheet. This will catch the items that was added to the updated sheet and not in the original sheet. Because you are requesting to highlight differences in the updated sheet your way will miss these updated lines because they don't exist in the original sheet. "G. Yamada" wrote: Hi, I am a newcomer when it comes to functions and programming in Excel which makes this task all the more difficult. I have a worksheet that is updated continuously with new/added information and information that is deleted. This is an inventory worksheet with retail items listed in rows and aspects of its description located in adjacent cells. Basically, I cannot depend on an item to be located in the same row each time the worksheet is updated since things are added and deleted. What I would like to do is have the original (non-updated) worksheet be compared to the updated worksheet by: 1. Using the info starting from C3 down (the UPC number for the product) in the original worksheet and search for this number in the same column (C) in the updated worksheet. 2. Highlight any differences in columns G, H, I, for the row in question where the identical UPC numbers are found in both worksheets. (Highlighted in the original worksheet) This is a very involved task I would assume, so if an answer cannot be provided, I would certainly appreciate any suggestions regarding reference material. Thank you for your time Glenn Yamada |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing worksheets with non-identical rows of information
Hello,
Thank you for your reply. The updated worksheet name is "updatedDVDdatabase" and the original worksheet name is "DVDdatabase". The UPCs are located in column C. Thanks again for your help. Glenn "Joel" wrote: This is a very simple program. Written it plenty of times. Can you let me know the wroksheets names and the column where the UPC number is located. I would recommend to do this task the opposite way from the way you suggested. Go line by line on the updated worksheet and compare to the original worksheet. This will catch the items that was added to the updated sheet and not in the original sheet. Because you are requesting to highlight differences in the updated sheet your way will miss these updated lines because they don't exist in the original sheet. "G. Yamada" wrote: Hi, I am a newcomer when it comes to functions and programming in Excel which makes this task all the more difficult. I have a worksheet that is updated continuously with new/added information and information that is deleted. This is an inventory worksheet with retail items listed in rows and aspects of its description located in adjacent cells. Basically, I cannot depend on an item to be located in the same row each time the worksheet is updated since things are added and deleted. What I would like to do is have the original (non-updated) worksheet be compared to the updated worksheet by: 1. Using the info starting from C3 down (the UPC number for the product) in the original worksheet and search for this number in the same column (C) in the updated worksheet. 2. Highlight any differences in columns G, H, I, for the row in question where the identical UPC numbers are found in both worksheets. (Highlighted in the original worksheet) This is a very involved task I would assume, so if an answer cannot be provided, I would certainly appreciate any suggestions regarding reference material. Thank you for your time Glenn Yamada |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing worksheets with non-identical rows of information
Sub CompareSheets()
With Sheets("updatedDVDdatabase") RowCount = 3 Do While .Range("C" & RowCount) < "" UPC = .Range("C" & RowCount) With Sheets("DVDdatabase") Set c = .Columns("C").Find(what:=UPC, _ LookIn:=xlValues, lookat:=xlWhole) End With If c Is Nothing Then .Range("C" & RowCount).Interior.ColorIndex = 3 .Range("G" & RowCount).Interior.ColorIndex = 3 .Range("H" & RowCount).Interior.ColorIndex = 3 .Range("I" & RowCount).Interior.ColorIndex = 3 Else .Range("C" & RowCount).Interior.ColorIndex = 4 With Sheets("DVDdatabase") .Range("C" & c.Row).Interior.ColorIndex = 4 End With For ColCount = Range("G1").Column To _ Range("I1").Column If .Cells(RowCount, ColCount) = _ Sheets("DVDdatabase").Cells(c.Row, ColCount) Then .Cells(RowCount, ColCount).Interior.ColorIndex = 4 Sheets("DVDdatabase").Cells(c.Row, ColCount) _ .Interior.ColorIndex = 4 Else .Cells(RowCount, ColCount).Interior.ColorIndex = 3 Sheets("DVDdatabase").Cells(c.Row, ColCount) _ .Interior.ColorIndex = 3 End If Next ColCount End If RowCount = RowCount + 1 Loop End With End Sub "G. Yamada" wrote: Hello, Thank you for your reply. The updated worksheet name is "updatedDVDdatabase" and the original worksheet name is "DVDdatabase". The UPCs are located in column C. Thanks again for your help. Glenn "Joel" wrote: This is a very simple program. Written it plenty of times. Can you let me know the wroksheets names and the column where the UPC number is located. I would recommend to do this task the opposite way from the way you suggested. Go line by line on the updated worksheet and compare to the original worksheet. This will catch the items that was added to the updated sheet and not in the original sheet. Because you are requesting to highlight differences in the updated sheet your way will miss these updated lines because they don't exist in the original sheet. "G. Yamada" wrote: Hi, I am a newcomer when it comes to functions and programming in Excel which makes this task all the more difficult. I have a worksheet that is updated continuously with new/added information and information that is deleted. This is an inventory worksheet with retail items listed in rows and aspects of its description located in adjacent cells. Basically, I cannot depend on an item to be located in the same row each time the worksheet is updated since things are added and deleted. What I would like to do is have the original (non-updated) worksheet be compared to the updated worksheet by: 1. Using the info starting from C3 down (the UPC number for the product) in the original worksheet and search for this number in the same column (C) in the updated worksheet. 2. Highlight any differences in columns G, H, I, for the row in question where the identical UPC numbers are found in both worksheets. (Highlighted in the original worksheet) This is a very involved task I would assume, so if an answer cannot be provided, I would certainly appreciate any suggestions regarding reference material. Thank you for your time Glenn Yamada |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Thank you!
I appreciate the help!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COMPARING TWO COLUMNS OF INFORMATION, ACROSS TWO WORKSHEETS | Excel Worksheet Functions | |||
Comparing information on 2 worksheets | Excel Discussion (Misc queries) | |||
comparing identical rows in different sheets | Excel Programming | |||
comparing identical rows in different sheets | Excel Programming | |||
Comparing rows of data in two worksheets | Excel Programming |