Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Data from two 2 worksheets
Hi Guys,
I'm new to this workgroup and fairly new to Excel so please be payient. I have reports sent to me in excel on a monthly basis which I need to compare. The reports are basically horizontal data tables of parts that our company sells. The worksheets contains approximately 7000 rows of data.The column headings are as follows. Part Number Description Annual Sales Volume Technical features Price Customer What I would like to do is find an automaitc way of comapring say the "November" data to the previous "October" one. Changes normally occurr on the existing data or indeed rows are added (for new parts) or deleted (for redundent ones). What (I think) would absolutely ideal would be a function or something that compares the same parts on the 2 reports by Part Number and reports the following: Changes in any of the other columns corresponding to the part number compared New part part number not present on the previous month's report Part numbers presented on the previous report but deleted on the new one. I hope this all makes sense to someone and I've been told Excel "can do anything" so I'm hopefull!!! I would also really appreciate it if any replies could be copied to my email address as I do not have access to this newsgroup at work. Thanks very much in advance to all who want to help!! Roberto R |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Data from two 2 worksheets
So the Part numbers are unique and never duplicated (on each sheet), right?
Here's one I saved that kind of does what you want. But just to make sure, create a new workbook and put the two sheets into that new workbook. Put headers in row 1 of each sheet if you don't have them there already. Modify this pair of lines: Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") to point at the worksheet names you use. Mstrwks will become the "report" worksheet. It removes the fill color and marks the differences in red (.colorindex =3). It also adds an indicator in a column with the change type. You can filter on that later???? And if you added more records in Sheet2, it'll add them to the bottom and mark them "added". And if the key was missing from the other sheet, it puts a message there, too. Make sure you try it out against a test copy of your workbook--just in case (or close without saving if it breaks things too much!). Option Explicit Sub testme() Application.ScreenUpdating = False Dim MstrWks As Worksheet Dim NewWks As Worksheet Dim MstrKeyRange As Range Dim NewKeyRange As Range Dim myCell As Range Dim destCell As Range Dim LastCol As Long Dim iCol As Long Dim res As Variant Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") With MstrWks Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone 'remove all fill color! End With With NewWks Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) End With LastCol = 6 'A to F MstrWks.Columns(LastCol + 1).Clear For Each myCell In MstrKeyRange.Cells With myCell res = Application.Match(.Value, NewKeyRange, 0) If IsError(res) Then .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Not on other sheet" Else For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Changed" End If Next iCol End If End With Next myCell 'check for newly added entries For Each myCell In NewKeyRange.Cells With myCell res = Application.Match(.Value, MstrKeyRange, 0) If IsError(res) Then 'missing from new workbook! With MstrWks Set destCell _ = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With .Resize(1, LastCol).Copy _ Destination:=destCell destCell.Parent.Cells(destCell.Row, LastCol + 1).Value _ = "Added" Else 'already in the master 'don't do anything End If End With Next myCell Application.ScreenUpdating = True End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Roberto R wrote: Hi Guys, I'm new to this workgroup and fairly new to Excel so please be payient. I have reports sent to me in excel on a monthly basis which I need to compare. The reports are basically horizontal data tables of parts that our company sells. The worksheets contains approximately 7000 rows of data.The column headings are as follows. Part Number Description Annual Sales Volume Technical features Price Customer What I would like to do is find an automaitc way of comapring say the "November" data to the previous "October" one. Changes normally occurr on the existing data or indeed rows are added (for new parts) or deleted (for redundent ones). What (I think) would absolutely ideal would be a function or something that compares the same parts on the 2 reports by Part Number and reports the following: Changes in any of the other columns corresponding to the part number compared New part part number not present on the previous month's report Part numbers presented on the previous report but deleted on the new one. I hope this all makes sense to someone and I've been told Excel "can do anything" so I'm hopefull!!! I would also really appreciate it if any replies could be copied to my email address as I do not have access to this newsgroup at work. Thanks very much in advance to all who want to help!! Roberto R -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Data from two 2 worksheets
Hi Dave, thanks for the lightning quick answer. I opened up the VB editor
and pasted your lines in. I think it created a macro called "testme". I ensured the column headers were in row1 on both sheets and modified the lines you suggested to put exactly the names of the worksheets but nothing happens when I run the macro itself! I must be doing soemthing wrong but I can't figure out what! Any suggestions? I hope to get it working because it sounds like your macro does exactly what I reuire!! Thanks again, Roberto R "Dave Peterson" wrote in message ... So the Part numbers are unique and never duplicated (on each sheet), right? Here's one I saved that kind of does what you want. But just to make sure, create a new workbook and put the two sheets into that new workbook. Put headers in row 1 of each sheet if you don't have them there already. Modify this pair of lines: Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") to point at the worksheet names you use. Mstrwks will become the "report" worksheet. It removes the fill color and marks the differences in red (.colorindex =3). It also adds an indicator in a column with the change type. You can filter on that later???? And if you added more records in Sheet2, it'll add them to the bottom and mark them "added". And if the key was missing from the other sheet, it puts a message there, too. Make sure you try it out against a test copy of your workbook--just in case (or close without saving if it breaks things too much!). Option Explicit Sub testme() Application.ScreenUpdating = False Dim MstrWks As Worksheet Dim NewWks As Worksheet Dim MstrKeyRange As Range Dim NewKeyRange As Range Dim myCell As Range Dim destCell As Range Dim LastCol As Long Dim iCol As Long Dim res As Variant Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") With MstrWks Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone 'remove all fill color! End With With NewWks Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) End With LastCol = 6 'A to F MstrWks.Columns(LastCol + 1).Clear For Each myCell In MstrKeyRange.Cells With myCell res = Application.Match(.Value, NewKeyRange, 0) If IsError(res) Then .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Not on other sheet" Else For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Changed" End If Next iCol End If End With Next myCell 'check for newly added entries For Each myCell In NewKeyRange.Cells With myCell res = Application.Match(.Value, MstrKeyRange, 0) If IsError(res) Then 'missing from new workbook! With MstrWks Set destCell _ = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With .Resize(1, LastCol).Copy _ Destination:=destCell destCell.Parent.Cells(destCell.Row, LastCol + 1).Value _ = "Added" Else 'already in the master 'don't do anything End If End With Next myCell Application.ScreenUpdating = True End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Roberto R wrote: Hi Guys, I'm new to this workgroup and fairly new to Excel so please be payient. I have reports sent to me in excel on a monthly basis which I need to compare. The reports are basically horizontal data tables of parts that our company sells. The worksheets contains approximately 7000 rows of data.The column headings are as follows. Part Number Description Annual Sales Volume Technical features Price Customer What I would like to do is find an automaitc way of comapring say the "November" data to the previous "October" one. Changes normally occurr on the existing data or indeed rows are added (for new parts) or deleted (for redundent ones). What (I think) would absolutely ideal would be a function or something that compares the same parts on the 2 reports by Part Number and reports the following: Changes in any of the other columns corresponding to the part number compared New part part number not present on the previous month's report Part numbers presented on the previous report but deleted on the new one. I hope this all makes sense to someone and I've been told Excel "can do anything" so I'm hopefull!!! I would also really appreciate it if any replies could be copied to my address as I do not have access to this newsgroup at work. Thanks very much in advance to all who want to help!! Roberto R -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Data from two 2 worksheets
Can you expand on what "nothing happens" means?
But before you do that, look at the other sheet. Maybe you weren't looking at the right one. If that doesn't help, go back to the VBE. Put the cursor somewhere in that code and hit F8 and step through the code. Maybe you'll see what's happening. Roberto R wrote: Hi Dave, thanks for the lightning quick answer. I opened up the VB editor and pasted your lines in. I think it created a macro called "testme". I ensured the column headers were in row1 on both sheets and modified the lines you suggested to put exactly the names of the worksheets but nothing happens when I run the macro itself! I must be doing soemthing wrong but I can't figure out what! Any suggestions? I hope to get it working because it sounds like your macro does exactly what I reuire!! Thanks again, Roberto R "Dave Peterson" wrote in message ... So the Part numbers are unique and never duplicated (on each sheet), right? Here's one I saved that kind of does what you want. But just to make sure, create a new workbook and put the two sheets into that new workbook. Put headers in row 1 of each sheet if you don't have them there already. Modify this pair of lines: Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") to point at the worksheet names you use. Mstrwks will become the "report" worksheet. It removes the fill color and marks the differences in red (.colorindex =3). It also adds an indicator in a column with the change type. You can filter on that later???? And if you added more records in Sheet2, it'll add them to the bottom and mark them "added". And if the key was missing from the other sheet, it puts a message there, too. Make sure you try it out against a test copy of your workbook--just in case (or close without saving if it breaks things too much!). Option Explicit Sub testme() Application.ScreenUpdating = False Dim MstrWks As Worksheet Dim NewWks As Worksheet Dim MstrKeyRange As Range Dim NewKeyRange As Range Dim myCell As Range Dim destCell As Range Dim LastCol As Long Dim iCol As Long Dim res As Variant Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") With MstrWks Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone 'remove all fill color! End With With NewWks Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) End With LastCol = 6 'A to F MstrWks.Columns(LastCol + 1).Clear For Each myCell In MstrKeyRange.Cells With myCell res = Application.Match(.Value, NewKeyRange, 0) If IsError(res) Then .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Not on other sheet" Else For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Changed" End If Next iCol End If End With Next myCell 'check for newly added entries For Each myCell In NewKeyRange.Cells With myCell res = Application.Match(.Value, MstrKeyRange, 0) If IsError(res) Then 'missing from new workbook! With MstrWks Set destCell _ = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With .Resize(1, LastCol).Copy _ Destination:=destCell destCell.Parent.Cells(destCell.Row, LastCol + 1).Value _ = "Added" Else 'already in the master 'don't do anything End If End With Next myCell Application.ScreenUpdating = True End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Roberto R wrote: Hi Guys, I'm new to this workgroup and fairly new to Excel so please be payient. I have reports sent to me in excel on a monthly basis which I need to compare. The reports are basically horizontal data tables of parts that our company sells. The worksheets contains approximately 7000 rows of data.The column headings are as follows. Part Number Description Annual Sales Volume Technical features Price Customer What I would like to do is find an automaitc way of comapring say the "November" data to the previous "October" one. Changes normally occurr on the existing data or indeed rows are added (for new parts) or deleted (for redundent ones). What (I think) would absolutely ideal would be a function or something that compares the same parts on the 2 reports by Part Number and reports the following: Changes in any of the other columns corresponding to the part number compared New part part number not present on the previous month's report Part numbers presented on the previous report but deleted on the new one. I hope this all makes sense to someone and I've been told Excel "can do anything" so I'm hopefull!!! I would also really appreciate it if any replies could be copied to my address as I do not have access to this newsgroup at work. Thanks very much in advance to all who want to help!! Roberto R -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Data from two 2 worksheets
Hi Dave,
thanks for the reply. I tried using F8 and all that happens is that a yellow arrow and highlighter go through the text and nothing else on either sheet! I have the "old" data on the first sheet and the "new" data on the second sheet and there is an empty sheet at the end. I don't get any error messages either. I'm sure it's something simple but I don't have any experience with macros so I can't see what the problem is. What should happen if I was doing it right? Regards, "Dave Peterson" wrote in message ... Can you expand on what "nothing happens" means? But before you do that, look at the other sheet. Maybe you weren't looking at the right one. If that doesn't help, go back to the VBE. Put the cursor somewhere in that code and hit F8 and step through the code. Maybe you'll see what's happening. Roberto R wrote: Hi Dave, thanks for the lightning quick answer. I opened up the VB editor and pasted your lines in. I think it created a macro called "testme". I ensured the column headers were in row1 on both sheets and modified the lines you suggested to put exactly the names of the worksheets but nothing happens when I run the macro itself! I must be doing soemthing wrong but I can't figure out what! Any suggestions? I hope to get it working because it sounds like your macro does exactly what I reuire!! Thanks again, Roberto R "Dave Peterson" wrote in message ... So the Part numbers are unique and never duplicated (on each sheet), right? Here's one I saved that kind of does what you want. But just to make sure, create a new workbook and put the two sheets into that new workbook. Put headers in row 1 of each sheet if you don't have them there already. Modify this pair of lines: Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") to point at the worksheet names you use. Mstrwks will become the "report" worksheet. It removes the fill color and marks the differences in red (.colorindex =3). It also adds an indicator in a column with the change type. You can filter on that later???? And if you added more records in Sheet2, it'll add them to the bottom and mark them "added". And if the key was missing from the other sheet, it puts a message there, too. Make sure you try it out against a test copy of your workbook--just in case (or close without saving if it breaks things too much!). Option Explicit Sub testme() Application.ScreenUpdating = False Dim MstrWks As Worksheet Dim NewWks As Worksheet Dim MstrKeyRange As Range Dim NewKeyRange As Range Dim myCell As Range Dim destCell As Range Dim LastCol As Long Dim iCol As Long Dim res As Variant Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") With MstrWks Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone 'remove all fill color! End With With NewWks Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) End With LastCol = 6 'A to F MstrWks.Columns(LastCol + 1).Clear For Each myCell In MstrKeyRange.Cells With myCell res = Application.Match(.Value, NewKeyRange, 0) If IsError(res) Then .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Not on other sheet" Else For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Changed" End If Next iCol End If End With Next myCell 'check for newly added entries For Each myCell In NewKeyRange.Cells With myCell res = Application.Match(.Value, MstrKeyRange, 0) If IsError(res) Then 'missing from new workbook! With MstrWks Set destCell _ = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With .Resize(1, LastCol).Copy _ Destination:=destCell destCell.Parent.Cells(destCell.Row, LastCol + 1).Value _ = "Added" Else 'already in the master 'don't do anything End If End With Next myCell Application.ScreenUpdating = True End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Roberto R wrote: Hi Guys, I'm new to this workgroup and fairly new to Excel so please be payient. I have reports sent to me in excel on a monthly basis which I need to compare. The reports are basically horizontal data tables of parts that our company sells. The worksheets contains approximately 7000 rows of data.The column headings are as follows. Part Number Description Annual Sales Volume Technical features Price Customer What I would like to do is find an automaitc way of comapring say the "November" data to the previous "October" one. Changes normally occurr on the existing data or indeed rows are added (for new parts) or deleted (for redundent ones). What (I think) would absolutely ideal would be a function or something that compares the same parts on the 2 reports by Part Number and reports the following: Changes in any of the other columns corresponding to the part number compared New part part number not present on the previous month's report Part numbers presented on the previous report but deleted on the new one. I hope this all makes sense to someone and I've been told Excel "can do anything" so I'm hopefull!!! I would also really appreciate it if any replies could be copied to my address as I do not have access to this newsgroup at work. Thanks very much in advance to all who want to help!! Roberto R -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Data from two 2 worksheets
Hi again,
IT WORKS! Sorry Dave, it was my security setting that was stopping it from working. I noticed it physically changes the data in the "old" sheet to make it exactly the same as the "new" sheet, colours the cell and adds the words "changed" or "added" next to the row. Is it possible for it to highlight cells that reuire changing or have been added (or deleted) without it actually doing the changes? That would be fantastic! Thanks again "Roberto R" wrote in message ... Hi Dave, thanks for the reply. I tried using F8 and all that happens is that a yellow arrow and highlighter go through the text and nothing else on either sheet! I have the "old" data on the first sheet and the "new" data on the second sheet and there is an empty sheet at the end. I don't get any error messages either. I'm sure it's something simple but I don't have any experience with macros so I can't see what the problem is. What should happen if I was doing it right? Regards, "Dave Peterson" wrote in message ... Can you expand on what "nothing happens" means? But before you do that, look at the other sheet. Maybe you weren't looking at the right one. If that doesn't help, go back to the VBE. Put the cursor somewhere in that code and hit F8 and step through the code. Maybe you'll see what's happening. Roberto R wrote: Hi Dave, thanks for the lightning quick answer. I opened up the VB editor and pasted your lines in. I think it created a macro called "testme". I ensured the column headers were in row1 on both sheets and modified the lines you suggested to put exactly the names of the worksheets but nothing happens when I run the macro itself! I must be doing soemthing wrong but I can't figure out what! Any suggestions? I hope to get it working because it sounds like your macro does exactly what I reuire!! Thanks again, Roberto R "Dave Peterson" wrote in message ... So the Part numbers are unique and never duplicated (on each sheet), right? Here's one I saved that kind of does what you want. But just to make sure, create a new workbook and put the two sheets into that new workbook. Put headers in row 1 of each sheet if you don't have them there already. Modify this pair of lines: Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") to point at the worksheet names you use. Mstrwks will become the "report" worksheet. It removes the fill color and marks the differences in red (.colorindex =3). It also adds an indicator in a column with the change type. You can filter on that later???? And if you added more records in Sheet2, it'll add them to the bottom and mark them "added". And if the key was missing from the other sheet, it puts a message there, too. Make sure you try it out against a test copy of your workbook--just in case (or close without saving if it breaks things too much!). Option Explicit Sub testme() Application.ScreenUpdating = False Dim MstrWks As Worksheet Dim NewWks As Worksheet Dim MstrKeyRange As Range Dim NewKeyRange As Range Dim myCell As Range Dim destCell As Range Dim LastCol As Long Dim iCol As Long Dim res As Variant Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") With MstrWks Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone 'remove all fill color! End With With NewWks Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) End With LastCol = 6 'A to F MstrWks.Columns(LastCol + 1).Clear For Each myCell In MstrKeyRange.Cells With myCell res = Application.Match(.Value, NewKeyRange, 0) If IsError(res) Then .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Not on other sheet" Else For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Changed" End If Next iCol End If End With Next myCell 'check for newly added entries For Each myCell In NewKeyRange.Cells With myCell res = Application.Match(.Value, MstrKeyRange, 0) If IsError(res) Then 'missing from new workbook! With MstrWks Set destCell _ = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With .Resize(1, LastCol).Copy _ Destination:=destCell destCell.Parent.Cells(destCell.Row, LastCol + 1).Value _ = "Added" Else 'already in the master 'don't do anything End If End With Next myCell Application.ScreenUpdating = True End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Roberto R wrote: Hi Guys, I'm new to this workgroup and fairly new to Excel so please be payient. I have reports sent to me in excel on a monthly basis which I need to compare. The reports are basically horizontal data tables of parts that our company sells. The worksheets contains approximately 7000 rows of data.The column headings are as follows. Part Number Description Annual Sales Volume Technical features Price Customer What I would like to do is find an automaitc way of comapring say the "November" data to the previous "October" one. Changes normally occurr on the existing data or indeed rows are added (for new parts) or deleted (for redundent ones). What (I think) would absolutely ideal would be a function or something that compares the same parts on the 2 reports by Part Number and reports the following: Changes in any of the other columns corresponding to the part number compared New part part number not present on the previous month's report Part numbers presented on the previous report but deleted on the new one. I hope this all makes sense to someone and I've been told Excel "can do anything" so I'm hopefull!!! I would also really appreciate it if any replies could be copied to my address as I do not have access to this newsgroup at work. Thanks very much in advance to all who want to help!! Roberto R -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Data from two 2 worksheets
You can comment out the lines (put an apostrophe to the far left of that line)
to make it not do the stuff you don't want it to do. In this case, I think you only want to comment this section: For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else ' .Offset(0, iCol).Value _ ' = NewKeyRange(res).Offset(0, iCol).Value .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Changed" Just add those 2 apostrophes and see if that works. I'm not sure what should happen to the Added stuff. If you really want those gone, you can just comment these lines. 'check for newly added entries ' For Each myCell In NewKeyRange.Cells ' With myCell ' res = Application.Match(.Value, MstrKeyRange, 0) ' If IsError(res) Then ' 'missing from new workbook! ' With MstrWks ' Set destCell _ ' = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) ' End With ' .Resize(1, LastCol).Copy _ ' Destination:=destCell ' destCell.Parent.Cells(destCell.Row, LastCol + 1).Value _ ' = "Added" ' Else ' 'already in the master ' 'don't do anything ' End If ' End With ' Next myCell But I don't think you'll get the information that you want. Roberto R wrote: Hi again, IT WORKS! Sorry Dave, it was my security setting that was stopping it from working. I noticed it physically changes the data in the "old" sheet to make it exactly the same as the "new" sheet, colours the cell and adds the words "changed" or "added" next to the row. Is it possible for it to highlight cells that reuire changing or have been added (or deleted) without it actually doing the changes? That would be fantastic! Thanks again <<snipped |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Data from two 2 worksheets
Thanks Dave, I tried the first suggestion.
What would be ideal would be to have the words "changed" and "added" on each row that has changed or been added on the "new" sheet only instead of on the "old" one whilst having "deleted" on the "old" sheet for any part numbers which are missing on the "new" sheet. Also, is it possible to have the cells which have been "changed", "added" or "deleted" to be coloured in 3 different colors WITHOUT the changes actually being applied? I know I'm perhaps asking for a lot but now that I can "smell" the final goal, I'm inpatient to see it working! Thanks again "Dave Peterson" wrote in message ... You can comment out the lines (put an apostrophe to the far left of that line) to make it not do the stuff you don't want it to do. In this case, I think you only want to comment this section: For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else ' .Offset(0, iCol).Value _ ' = NewKeyRange(res).Offset(0, iCol).Value .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Changed" Just add those 2 apostrophes and see if that works. I'm not sure what should happen to the Added stuff. If you really want those gone, you can just comment these lines. 'check for newly added entries ' For Each myCell In NewKeyRange.Cells ' With myCell ' res = Application.Match(.Value, MstrKeyRange, 0) ' If IsError(res) Then ' 'missing from new workbook! ' With MstrWks ' Set destCell _ ' = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) ' End With ' .Resize(1, LastCol).Copy _ ' Destination:=destCell ' destCell.Parent.Cells(destCell.Row, LastCol + 1).Value _ ' = "Added" ' Else ' 'already in the master ' 'don't do anything ' End If ' End With ' Next myCell But I don't think you'll get the information that you want. Roberto R wrote: Hi again, IT WORKS! Sorry Dave, it was my security setting that was stopping it from working. I noticed it physically changes the data in the "old" sheet to make it exactly the same as the "new" sheet, colours the cell and adds the words "changed" or "added" next to the row. Is it possible for it to highlight cells that reuire changing or have been added (or deleted) without it actually doing the changes? That would be fantastic! Thanks again <<snipped |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Data from two 2 worksheets
Record a macro when you change the fill color for 3 cells. Look at the code to
pick out the colors that you want. Then look for .colorindex (3 times) to change in this: Option Explicit Sub testme() Application.ScreenUpdating = False Dim MstrWks As Worksheet Dim NewWks As Worksheet Dim MstrKeyRange As Range Dim NewKeyRange As Range Dim myCell As Range Dim destCell As Range Dim LastCol As Long Dim iCol As Long Dim res As Variant Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") With MstrWks Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone 'remove all fill color! End With With NewWks Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone End With LastCol = 6 'A to F MstrWks.Columns(LastCol + 1).Clear For Each myCell In MstrKeyRange.Cells With myCell res = Application.Match(.Value, NewKeyRange, 0) If IsError(res) Then .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Not on other sheet" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 5 Else For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else ' .Offset(0, iCol).Value _ ' = NewKeyRange(res).Offset(0, iCol).Value .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Changed" End If Next iCol End If End With Next myCell 'check for newly added entries For Each myCell In NewKeyRange.Cells With myCell res = Application.Match(.Value, MstrKeyRange, 0) If IsError(res) Then 'missing from new workbook! ' With MstrWks ' Set destCell _ ' = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) ' End With ' .Resize(1, LastCol).Copy _ ' Destination:=destCell destCell.Parent.Cells(destCell.Row, LastCol + 1).Value _ = "Added" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 7 Else 'already in the master 'don't do anything End If End With Next myCell Application.ScreenUpdating = True End Sub Roberto R wrote: Thanks Dave, I tried the first suggestion. What would be ideal would be to have the words "changed" and "added" on each row that has changed or been added on the "new" sheet only instead of on the "old" one whilst having "deleted" on the "old" sheet for any part numbers which are missing on the "new" sheet. Also, is it possible to have the cells which have been "changed", "added" or "deleted" to be coloured in 3 different colors WITHOUT the changes actually being applied? I know I'm perhaps asking for a lot but now that I can "smell" the final goal, I'm inpatient to see it working! Thanks again "Dave Peterson" wrote in message ... You can comment out the lines (put an apostrophe to the far left of that line) to make it not do the stuff you don't want it to do. In this case, I think you only want to comment this section: For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else ' .Offset(0, iCol).Value _ ' = NewKeyRange(res).Offset(0, iCol).Value .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Changed" Just add those 2 apostrophes and see if that works. I'm not sure what should happen to the Added stuff. If you really want those gone, you can just comment these lines. 'check for newly added entries ' For Each myCell In NewKeyRange.Cells ' With myCell ' res = Application.Match(.Value, MstrKeyRange, 0) ' If IsError(res) Then ' 'missing from new workbook! ' With MstrWks ' Set destCell _ ' = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) ' End With ' .Resize(1, LastCol).Copy _ ' Destination:=destCell ' destCell.Parent.Cells(destCell.Row, LastCol + 1).Value _ ' = "Added" ' Else ' 'already in the master ' 'don't do anything ' End If ' End With ' Next myCell But I don't think you'll get the information that you want. Roberto R wrote: Hi again, IT WORKS! Sorry Dave, it was my security setting that was stopping it from working. I noticed it physically changes the data in the "old" sheet to make it exactly the same as the "new" sheet, colours the cell and adds the words "changed" or "added" next to the row. Is it possible for it to highlight cells that reuire changing or have been added (or deleted) without it actually doing the changes? That would be fantastic! Thanks again <<snipped -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Data from two 2 worksheets
Hi Dave,
sorry but it doesn't seem to work. I tried using F8 and it gives me a run time error 9 and nothing happens on the sheets. What do you mean by recording a macro when I change the fill colors? All I did was paste your code into the VBE and changed the shhet1 and sheet 2 names. Help!! Thanks again "Dave Peterson" wrote in message ... Record a macro when you change the fill color for 3 cells. Look at the code to pick out the colors that you want. Then look for .colorindex (3 times) to change in this: Option Explicit Sub testme() Application.ScreenUpdating = False Dim MstrWks As Worksheet Dim NewWks As Worksheet Dim MstrKeyRange As Range Dim NewKeyRange As Range Dim myCell As Range Dim destCell As Range Dim LastCol As Long Dim iCol As Long Dim res As Variant Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") With MstrWks Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone 'remove all fill color! End With With NewWks Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone End With LastCol = 6 'A to F MstrWks.Columns(LastCol + 1).Clear For Each myCell In MstrKeyRange.Cells With myCell res = Application.Match(.Value, NewKeyRange, 0) If IsError(res) Then .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Not on other sheet" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 5 Else For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else ' .Offset(0, iCol).Value _ ' = NewKeyRange(res).Offset(0, iCol).Value .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Changed" End If Next iCol End If End With Next myCell 'check for newly added entries For Each myCell In NewKeyRange.Cells With myCell res = Application.Match(.Value, MstrKeyRange, 0) If IsError(res) Then 'missing from new workbook! ' With MstrWks ' Set destCell _ ' = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) ' End With ' .Resize(1, LastCol).Copy _ ' Destination:=destCell destCell.Parent.Cells(destCell.Row, LastCol + 1).Value _ = "Added" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 7 Else 'already in the master 'don't do anything End If End With Next myCell Application.ScreenUpdating = True End Sub Roberto R wrote: Thanks Dave, I tried the first suggestion. What would be ideal would be to have the words "changed" and "added" on each row that has changed or been added on the "new" sheet only instead of on the "old" one whilst having "deleted" on the "old" sheet for any part numbers which are missing on the "new" sheet. Also, is it possible to have the cells which have been "changed", "added" or "deleted" to be coloured in 3 different colors WITHOUT the changes actually being applied? I know I'm perhaps asking for a lot but now that I can "smell" the final goal, I'm inpatient to see it working! Thanks again "Dave Peterson" wrote in message ... You can comment out the lines (put an apostrophe to the far left of that line) to make it not do the stuff you don't want it to do. In this case, I think you only want to comment this section: For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else ' .Offset(0, iCol).Value _ ' = NewKeyRange(res).Offset(0, iCol).Value .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Changed" Just add those 2 apostrophes and see if that works. I'm not sure what should happen to the Added stuff. If you really want those gone, you can just comment these lines. 'check for newly added entries ' For Each myCell In NewKeyRange.Cells ' With myCell ' res = Application.Match(.Value, MstrKeyRange, 0) ' If IsError(res) Then ' 'missing from new workbook! ' With MstrWks ' Set destCell _ ' = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) ' End With ' .Resize(1, LastCol).Copy _ ' Destination:=destCell ' destCell.Parent.Cells(destCell.Row, LastCol + 1).Value _ ' = "Added" ' Else ' 'already in the master ' 'don't do anything ' End If ' End With ' Next myCell But I don't think you'll get the information that you want. Roberto R wrote: Hi again, IT WORKS! Sorry Dave, it was my security setting that was stopping it from working. I noticed it physically changes the data in the "old" sheet to make it exactly the same as the "new" sheet, colours the cell and adds the words "changed" or "added" next to the row. Is it possible for it to highlight cells that reuire changing or have been added (or deleted) without it actually doing the changes? That would be fantastic! Thanks again <<snipped -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Data from two 2 worksheets
Start a new workbook--you'll close without saving later
Tools|Macro|Record macro (into that workbook) Change the fill color of a few cells Stop recording the macro You'll see the numbers for the colors you chose. === And you're going to have to be more specific about the error--what line did it occur on? If it was one of those sheet name lines, try typing the names again. You have a typo. (It could be true. You typed shhet1 instead of sheet1 in your post <bg.) Roberto R wrote: Hi Dave, sorry but it doesn't seem to work. I tried using F8 and it gives me a run time error 9 and nothing happens on the sheets. What do you mean by recording a macro when I change the fill colors? All I did was paste your code into the VBE and changed the shhet1 and sheet 2 names. Help!! Thanks again "Dave Peterson" wrote in message ... Record a macro when you change the fill color for 3 cells. Look at the code to pick out the colors that you want. Then look for .colorindex (3 times) to change in this: Option Explicit Sub testme() Application.ScreenUpdating = False Dim MstrWks As Worksheet Dim NewWks As Worksheet Dim MstrKeyRange As Range Dim NewKeyRange As Range Dim myCell As Range Dim destCell As Range Dim LastCol As Long Dim iCol As Long Dim res As Variant Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") With MstrWks Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone 'remove all fill color! End With With NewWks Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone End With LastCol = 6 'A to F MstrWks.Columns(LastCol + 1).Clear For Each myCell In MstrKeyRange.Cells With myCell res = Application.Match(.Value, NewKeyRange, 0) If IsError(res) Then .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Not on other sheet" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 5 Else For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else ' .Offset(0, iCol).Value _ ' = NewKeyRange(res).Offset(0, iCol).Value .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Changed" End If Next iCol End If End With Next myCell 'check for newly added entries For Each myCell In NewKeyRange.Cells With myCell res = Application.Match(.Value, MstrKeyRange, 0) If IsError(res) Then 'missing from new workbook! ' With MstrWks ' Set destCell _ ' = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) ' End With ' .Resize(1, LastCol).Copy _ ' Destination:=destCell destCell.Parent.Cells(destCell.Row, LastCol + 1).Value _ = "Added" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 7 Else 'already in the master 'don't do anything End If End With Next myCell Application.ScreenUpdating = True End Sub Roberto R wrote: Thanks Dave, I tried the first suggestion. What would be ideal would be to have the words "changed" and "added" on each row that has changed or been added on the "new" sheet only instead of on the "old" one whilst having "deleted" on the "old" sheet for any part numbers which are missing on the "new" sheet. Also, is it possible to have the cells which have been "changed", "added" or "deleted" to be coloured in 3 different colors WITHOUT the changes actually being applied? I know I'm perhaps asking for a lot but now that I can "smell" the final goal, I'm inpatient to see it working! Thanks again "Dave Peterson" wrote in message ... You can comment out the lines (put an apostrophe to the far left of that line) to make it not do the stuff you don't want it to do. In this case, I think you only want to comment this section: For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else ' .Offset(0, iCol).Value _ ' = NewKeyRange(res).Offset(0, iCol).Value .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Changed" Just add those 2 apostrophes and see if that works. I'm not sure what should happen to the Added stuff. If you really want those gone, you can just comment these lines. 'check for newly added entries ' For Each myCell In NewKeyRange.Cells ' With myCell ' res = Application.Match(.Value, MstrKeyRange, 0) ' If IsError(res) Then ' 'missing from new workbook! ' With MstrWks ' Set destCell _ ' = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) ' End With ' .Resize(1, LastCol).Copy _ ' Destination:=destCell ' destCell.Parent.Cells(destCell.Row, LastCol + 1).Value _ ' = "Added" ' Else ' 'already in the master ' 'don't do anything ' End If ' End With ' Next myCell But I don't think you'll get the information that you want. Roberto R wrote: Hi again, IT WORKS! Sorry Dave, it was my security setting that was stopping it from working. I noticed it physically changes the data in the "old" sheet to make it exactly the same as the "new" sheet, colours the cell and adds the words "changed" or "added" next to the row. Is it possible for it to highlight cells that reuire changing or have been added (or deleted) without it actually doing the changes? That would be fantastic! Thanks again <<snipped -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Data from two 2 worksheets
Hi Dave, I noticed that I have to invert the order of the sheets in the
macro. In other words I set the "new" sheet as the MstrWks and the original or "old" sheet as the NewWks to get it report all the changes on the "new" sheet as required. I'm still getting the same error but only if the MstrWks sheet has more rows or part numbers in it than the NewWks. Not sure why(?). The macro works fine though despite the error message except for one thing: if parts are deleted on the "new" sheet, these are not reported as "missing" or "deleted" on the "old" sheet! Any ideas? Apart from that the Macro works fine. I worked out how to change the colors (thanks) also. One more question: If the sheet has more than 6 columns and the "part number" column is not column A but say column F or something, which paramters do I change in the Macro? Is it LastCol = 6 'A to F and Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) Regards "Dave Peterson" wrote in message ... Start a new workbook--you'll close without saving later Tools|Macro|Record macro (into that workbook) Change the fill color of a few cells Stop recording the macro You'll see the numbers for the colors you chose. === And you're going to have to be more specific about the error--what line did it occur on? If it was one of those sheet name lines, try typing the names again. You have a typo. (It could be true. You typed shhet1 instead of sheet1 in your post <bg.) Roberto R wrote: Hi Dave, sorry but it doesn't seem to work. I tried using F8 and it gives me a run time error 9 and nothing happens on the sheets. What do you mean by recording a macro when I change the fill colors? All I did was paste your code into the VBE and changed the shhet1 and sheet 2 names. Help!! Thanks again "Dave Peterson" wrote in message ... Record a macro when you change the fill color for 3 cells. Look at the code to pick out the colors that you want. Then look for .colorindex (3 times) to change in this: Option Explicit Sub testme() Application.ScreenUpdating = False Dim MstrWks As Worksheet Dim NewWks As Worksheet Dim MstrKeyRange As Range Dim NewKeyRange As Range Dim myCell As Range Dim destCell As Range Dim LastCol As Long Dim iCol As Long Dim res As Variant Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") With MstrWks Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone 'remove all fill color! End With With NewWks Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone End With LastCol = 6 'A to F MstrWks.Columns(LastCol + 1).Clear For Each myCell In MstrKeyRange.Cells With myCell res = Application.Match(.Value, NewKeyRange, 0) If IsError(res) Then .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Not on other sheet" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 5 Else For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else ' .Offset(0, iCol).Value _ ' = NewKeyRange(res).Offset(0, iCol).Value .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Changed" End If Next iCol End If End With Next myCell 'check for newly added entries For Each myCell In NewKeyRange.Cells With myCell res = Application.Match(.Value, MstrKeyRange, 0) If IsError(res) Then 'missing from new workbook! ' With MstrWks ' Set destCell _ ' = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) ' End With ' .Resize(1, LastCol).Copy _ ' Destination:=destCell destCell.Parent.Cells(destCell.Row, LastCol + 1).Value _ = "Added" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 7 Else 'already in the master 'don't do anything End If End With Next myCell Application.ScreenUpdating = True End Sub Roberto R wrote: Thanks Dave, I tried the first suggestion. What would be ideal would be to have the words "changed" and "added" on each row that has changed or been added on the "new" sheet only instead of on the "old" one whilst having "deleted" on the "old" sheet for any part numbers which are missing on the "new" sheet. Also, is it possible to have the cells which have been "changed", "added" or "deleted" to be coloured in 3 different colors WITHOUT the changes actually being applied? I know I'm perhaps asking for a lot but now that I can "smell" the final goal, I'm inpatient to see it working! Thanks again "Dave Peterson" wrote in message ... You can comment out the lines (put an apostrophe to the far left of that line) to make it not do the stuff you don't want it to do. In this case, I think you only want to comment this section: For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else ' .Offset(0, iCol).Value _ ' = NewKeyRange(res).Offset(0, iCol).Value .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Changed" Just add those 2 apostrophes and see if that works. I'm not sure what should happen to the Added stuff. If you really want those gone, you can just comment these lines. 'check for newly added entries ' For Each myCell In NewKeyRange.Cells ' With myCell ' res = Application.Match(.Value, MstrKeyRange, 0) ' If IsError(res) Then ' 'missing from new workbook! ' With MstrWks ' Set destCell _ ' = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) ' End With ' .Resize(1, LastCol).Copy _ ' Destination:=destCell ' destCell.Parent.Cells(destCell.Row, LastCol + 1).Value _ ' = "Added" ' Else ' 'already in the master ' 'don't do anything ' End If ' End With ' Next myCell But I don't think you'll get the information that you want. Roberto R wrote: Hi again, IT WORKS! Sorry Dave, it was my security setting that was stopping it from working. I noticed it physically changes the data in the "old" sheet to make it exactly the same as the "new" sheet, colours the cell and adds the words "changed" or "added" next to the row. Is it possible for it to highlight cells that reuire changing or have been added (or deleted) without it actually doing the changes? That would be fantastic! Thanks again <<snipped -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Data from two 2 worksheets
I made the change and pasted into the message, then I made one more minor(!)
change and broke it. This version fixes the first error: Option Explicit Sub testme() Application.ScreenUpdating = False Dim MstrWks As Worksheet Dim NewWks As Worksheet Dim MstrKeyRange As Range Dim NewKeyRange As Range Dim myCell As Range Dim destCell As Range Dim LastCol As Long Dim iCol As Long Dim res As Variant Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") With MstrWks Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone 'remove all fill color! End With With NewWks Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone End With LastCol = 6 'A to F MstrWks.Columns(LastCol + 1).Clear For Each myCell In MstrKeyRange.Cells With myCell res = Application.Match(.Value, NewKeyRange, 0) If IsError(res) Then .Parent.Cells(.Row, LastCol + 1).Value _ = "Not on other sheet" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 5 Else For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(.Row, LastCol + 1).Value _ = "Changed" End If Next iCol End If End With Next myCell 'check for newly added entries For Each myCell In NewKeyRange.Cells With myCell res = Application.Match(.Value, MstrKeyRange, 0) If IsError(res) Then 'missing from new workbook! .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Added" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 7 Else 'already in the master 'don't do anything End If End With Next myCell Application.ScreenUpdating = True End Sub As for the changing the key column, try this one: Option Explicit Sub testme() Application.ScreenUpdating = False Dim MstrWks As Worksheet Dim NewWks As Worksheet Dim MstrKeyRange As Range Dim NewKeyRange As Range Dim myCell As Range Dim destCell As Range Dim KeyCol As Long Dim StartRow As Long Dim LastCol As Long Dim iCol As Long Dim res As Variant Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") StartRow = 2 'headers in row 1 KeyCol = 3 'column C With MstrWks Set MstrKeyRange = .Range(.Cells(StartRow, KeyCol), _ .Cells(.Rows.Count, KeyCol).End(xlUp)) .Cells.Interior.ColorIndex = xlNone 'remove all fill color! End With With NewWks Set NewKeyRange = .Range(.Cells(StartRow, KeyCol), _ .Cells(.Rows.Count, KeyCol).End(xlUp)) .Cells.Interior.ColorIndex = xlNone End With LastCol = 6 'A to F MstrWks.Columns(LastCol + 1).Clear For Each myCell In MstrKeyRange.Cells With myCell res = Application.Match(.Value, NewKeyRange, 0) If IsError(res) Then .Parent.Cells(.Row, LastCol + 1).Value _ = "Not on other sheet" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 5 Else For iCol = 1 To LastCol If iCol = KeyCol Then 'skip it Else If .Parent.Cells(.Row, iCol).Value _ = NewKeyRange.Parent _ .Cells(res + StartRow - 1, iCol).Value Then 'do nothing, they match Else .Parent.Cells(.Row, iCol).Interior.ColorIndex = 3 .Parent.Cells(.Row, LastCol + 1).Value _ = "Changed" End If End If Next iCol End If End With Next myCell 'check for newly added entries For Each myCell In NewKeyRange.Cells With myCell res = Application.Match(.Value, MstrKeyRange, 0) If IsError(res) Then 'missing from new workbook! .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Added" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 7 Else 'already in the master 'don't do anything End If End With Next myCell Application.ScreenUpdating = True End Sub Roberto R wrote: Hi Dave, I noticed that I have to invert the order of the sheets in the macro. In other words I set the "new" sheet as the MstrWks and the original or "old" sheet as the NewWks to get it report all the changes on the "new" sheet as required. I'm still getting the same error but only if the MstrWks sheet has more rows or part numbers in it than the NewWks. Not sure why(?). The macro works fine though despite the error message except for one thing: if parts are deleted on the "new" sheet, these are not reported as "missing" or "deleted" on the "old" sheet! Any ideas? Apart from that the Macro works fine. I worked out how to change the colors (thanks) also. One more question: If the sheet has more than 6 columns and the "part number" column is not column A but say column F or something, which paramters do I change in the Macro? Is it LastCol = 6 'A to F and Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) Regards "Dave Peterson" wrote in message ... Start a new workbook--you'll close without saving later Tools|Macro|Record macro (into that workbook) Change the fill color of a few cells Stop recording the macro You'll see the numbers for the colors you chose. === And you're going to have to be more specific about the error--what line did it occur on? If it was one of those sheet name lines, try typing the names again. You have a typo. (It could be true. You typed shhet1 instead of sheet1 in your post <bg.) Roberto R wrote: Hi Dave, sorry but it doesn't seem to work. I tried using F8 and it gives me a run time error 9 and nothing happens on the sheets. What do you mean by recording a macro when I change the fill colors? All I did was paste your code into the VBE and changed the shhet1 and sheet 2 names. Help!! Thanks again "Dave Peterson" wrote in message ... Record a macro when you change the fill color for 3 cells. Look at the code to pick out the colors that you want. Then look for .colorindex (3 times) to change in this: Option Explicit Sub testme() Application.ScreenUpdating = False Dim MstrWks As Worksheet Dim NewWks As Worksheet Dim MstrKeyRange As Range Dim NewKeyRange As Range Dim myCell As Range Dim destCell As Range Dim LastCol As Long Dim iCol As Long Dim res As Variant Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") With MstrWks Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone 'remove all fill color! End With With NewWks Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone End With LastCol = 6 'A to F MstrWks.Columns(LastCol + 1).Clear For Each myCell In MstrKeyRange.Cells With myCell res = Application.Match(.Value, NewKeyRange, 0) If IsError(res) Then .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Not on other sheet" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 5 Else For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else ' .Offset(0, iCol).Value _ ' = NewKeyRange(res).Offset(0, iCol).Value .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Changed" End If Next iCol End If End With Next myCell 'check for newly added entries For Each myCell In NewKeyRange.Cells With myCell res = Application.Match(.Value, MstrKeyRange, 0) If IsError(res) Then 'missing from new workbook! ' With MstrWks ' Set destCell _ ' = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) ' End With ' .Resize(1, LastCol).Copy _ ' Destination:=destCell destCell.Parent.Cells(destCell.Row, LastCol + 1).Value _ = "Added" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 7 Else 'already in the master 'don't do anything End If End With Next myCell Application.ScreenUpdating = True End Sub Roberto R wrote: Thanks Dave, I tried the first suggestion. What would be ideal would be to have the words "changed" and "added" on each row that has changed or been added on the "new" sheet only instead of on the "old" one whilst having "deleted" on the "old" sheet for any part numbers which are missing on the "new" sheet. Also, is it possible to have the cells which have been "changed", "added" or "deleted" to be coloured in 3 different colors WITHOUT the changes actually being applied? I know I'm perhaps asking for a lot but now that I can "smell" the final goal, I'm inpatient to see it working! Thanks again "Dave Peterson" wrote in message ... You can comment out the lines (put an apostrophe to the far left of that line) to make it not do the stuff you don't want it to do. In this case, I think you only want to comment this section: For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else ' .Offset(0, iCol).Value _ ' = NewKeyRange(res).Offset(0, iCol).Value .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Changed" Just add those 2 apostrophes and see if that works. I'm not sure what should happen to the Added stuff. If you really want those gone, you can just comment these lines. 'check for newly added entries ' For Each myCell In NewKeyRange.Cells ' With myCell ' res = Application.Match(.Value, MstrKeyRange, 0) ' If IsError(res) Then ' 'missing from new workbook! ' With MstrWks ' Set destCell _ ' = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) ' End With ' .Resize(1, LastCol).Copy _ ' Destination:=destCell ' destCell.Parent.Cells(destCell.Row, LastCol + 1).Value _ ' = "Added" ' Else ' 'already in the master ' 'don't do anything ' End If ' End With ' Next myCell But I don't think you'll get the information that you want. Roberto R wrote: Hi again, IT WORKS! Sorry Dave, it was my security setting that was stopping it from working. I noticed it physically changes the data in the "old" sheet to make it exactly the same as the "new" sheet, colours the cell and adds the words "changed" or "added" next to the row. Is it possible for it to highlight cells that reuire changing or have been added (or deleted) without it actually doing the changes? That would be fantastic! Thanks again <<snipped -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Data from two 2 worksheets
Sorry Dave, but I'm completely lost now!! This is obviously way over my
head! Is there any way I can send you a small extract of the kind of sheets I am thinking of, say for example 10 rows and you can advise? Thanks in advance "Dave Peterson" wrote in message ... I made the change and pasted into the message, then I made one more minor(!) change and broke it. This version fixes the first error: Option Explicit Sub testme() Application.ScreenUpdating = False Dim MstrWks As Worksheet Dim NewWks As Worksheet Dim MstrKeyRange As Range Dim NewKeyRange As Range Dim myCell As Range Dim destCell As Range Dim LastCol As Long Dim iCol As Long Dim res As Variant Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") With MstrWks Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone 'remove all fill color! End With With NewWks Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone End With LastCol = 6 'A to F MstrWks.Columns(LastCol + 1).Clear For Each myCell In MstrKeyRange.Cells With myCell res = Application.Match(.Value, NewKeyRange, 0) If IsError(res) Then .Parent.Cells(.Row, LastCol + 1).Value _ = "Not on other sheet" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 5 Else For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(.Row, LastCol + 1).Value _ = "Changed" End If Next iCol End If End With Next myCell 'check for newly added entries For Each myCell In NewKeyRange.Cells With myCell res = Application.Match(.Value, MstrKeyRange, 0) If IsError(res) Then 'missing from new workbook! .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Added" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 7 Else 'already in the master 'don't do anything End If End With Next myCell Application.ScreenUpdating = True End Sub As for the changing the key column, try this one: Option Explicit Sub testme() Application.ScreenUpdating = False Dim MstrWks As Worksheet Dim NewWks As Worksheet Dim MstrKeyRange As Range Dim NewKeyRange As Range Dim myCell As Range Dim destCell As Range Dim KeyCol As Long Dim StartRow As Long Dim LastCol As Long Dim iCol As Long Dim res As Variant Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") StartRow = 2 'headers in row 1 KeyCol = 3 'column C With MstrWks Set MstrKeyRange = .Range(.Cells(StartRow, KeyCol), _ .Cells(.Rows.Count, KeyCol).End(xlUp)) .Cells.Interior.ColorIndex = xlNone 'remove all fill color! End With With NewWks Set NewKeyRange = .Range(.Cells(StartRow, KeyCol), _ .Cells(.Rows.Count, KeyCol).End(xlUp)) .Cells.Interior.ColorIndex = xlNone End With LastCol = 6 'A to F MstrWks.Columns(LastCol + 1).Clear For Each myCell In MstrKeyRange.Cells With myCell res = Application.Match(.Value, NewKeyRange, 0) If IsError(res) Then .Parent.Cells(.Row, LastCol + 1).Value _ = "Not on other sheet" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 5 Else For iCol = 1 To LastCol If iCol = KeyCol Then 'skip it Else If .Parent.Cells(.Row, iCol).Value _ = NewKeyRange.Parent _ .Cells(res + StartRow - 1, iCol).Value Then 'do nothing, they match Else .Parent.Cells(.Row, iCol).Interior.ColorIndex = 3 .Parent.Cells(.Row, LastCol + 1).Value _ = "Changed" End If End If Next iCol End If End With Next myCell 'check for newly added entries For Each myCell In NewKeyRange.Cells With myCell res = Application.Match(.Value, MstrKeyRange, 0) If IsError(res) Then 'missing from new workbook! .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Added" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 7 Else 'already in the master 'don't do anything End If End With Next myCell Application.ScreenUpdating = True End Sub Roberto R wrote: Hi Dave, I noticed that I have to invert the order of the sheets in the macro. In other words I set the "new" sheet as the MstrWks and the original or "old" sheet as the NewWks to get it report all the changes on the "new" sheet as required. I'm still getting the same error but only if the MstrWks sheet has more rows or part numbers in it than the NewWks. Not sure why(?). The macro works fine though despite the error message except for one thing: if parts are deleted on the "new" sheet, these are not reported as "missing" or "deleted" on the "old" sheet! Any ideas? Apart from that the Macro works fine. I worked out how to change the colors (thanks) also. One more question: If the sheet has more than 6 columns and the "part number" column is not column A but say column F or something, which paramters do I change in the Macro? Is it LastCol = 6 'A to F and Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) Regards "Dave Peterson" wrote in message ... Start a new workbook--you'll close without saving later Tools|Macro|Record macro (into that workbook) Change the fill color of a few cells Stop recording the macro You'll see the numbers for the colors you chose. === And you're going to have to be more specific about the error--what line did it occur on? If it was one of those sheet name lines, try typing the names again. You have a typo. (It could be true. You typed shhet1 instead of sheet1 in your post <bg.) Roberto R wrote: Hi Dave, sorry but it doesn't seem to work. I tried using F8 and it gives me a run time error 9 and nothing happens on the sheets. What do you mean by recording a macro when I change the fill colors? All I did was paste your code into the VBE and changed the shhet1 and sheet 2 names. Help!! Thanks again "Dave Peterson" wrote in message ... Record a macro when you change the fill color for 3 cells. Look at the code to pick out the colors that you want. Then look for .colorindex (3 times) to change in this: Option Explicit Sub testme() Application.ScreenUpdating = False Dim MstrWks As Worksheet Dim NewWks As Worksheet Dim MstrKeyRange As Range Dim NewKeyRange As Range Dim myCell As Range Dim destCell As Range Dim LastCol As Long Dim iCol As Long Dim res As Variant Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") With MstrWks Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone 'remove all fill color! End With With NewWks Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone End With LastCol = 6 'A to F MstrWks.Columns(LastCol + 1).Clear For Each myCell In MstrKeyRange.Cells With myCell res = Application.Match(.Value, NewKeyRange, 0) If IsError(res) Then .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Not on other sheet" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 5 Else For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else ' .Offset(0, iCol).Value _ ' = NewKeyRange(res).Offset(0, iCol).Value .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Changed" End If Next iCol End If End With Next myCell 'check for newly added entries For Each myCell In NewKeyRange.Cells With myCell res = Application.Match(.Value, MstrKeyRange, 0) If IsError(res) Then 'missing from new workbook! ' With MstrWks ' Set destCell _ ' = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) ' End With ' .Resize(1, LastCol).Copy _ ' Destination:=destCell destCell.Parent.Cells(destCell.Row, LastCol + 1).Value _ = "Added" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 7 Else 'already in the master 'don't do anything End If End With Next myCell Application.ScreenUpdating = True End Sub Roberto R wrote: Thanks Dave, I tried the first suggestion. What would be ideal would be to have the words "changed" and "added" on each row that has changed or been added on the "new" sheet only instead of on the "old" one whilst having "deleted" on the "old" sheet for any part numbers which are missing on the "new" sheet. Also, is it possible to have the cells which have been "changed", "added" or "deleted" to be coloured in 3 different colors WITHOUT the changes actually being applied? I know I'm perhaps asking for a lot but now that I can "smell" the final goal, I'm inpatient to see it working! Thanks again "Dave Peterson" wrote in message ... You can comment out the lines (put an apostrophe to the far left of that line) to make it not do the stuff you don't want it to do. In this case, I think you only want to comment this section: For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else ' .Offset(0, iCol).Value _ ' = NewKeyRange(res).Offset(0, iCol).Value .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Changed" Just add those 2 apostrophes and see if that works. I'm not sure what should happen to the Added stuff. If you really want those gone, you can just comment these lines. 'check for newly added entries ' For Each myCell In NewKeyRange.Cells ' With myCell ' res = Application.Match(.Value, MstrKeyRange, 0) ' If IsError(res) Then ' 'missing from new workbook! ' With MstrWks ' Set destCell _ ' = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) ' End With ' .Resize(1, LastCol).Copy _ ' Destination:=destCell ' destCell.Parent.Cells(destCell.Row, LastCol + 1).Value _ ' = "Added" ' Else ' 'already in the master ' 'don't do anything ' End If ' End With ' Next myCell But I don't think you'll get the information that you want. Roberto R wrote: Hi again, IT WORKS! Sorry Dave, it was my security setting that was stopping it from working. I noticed it physically changes the data in the "old" sheet to make it exactly the same as the "new" sheet, colours the cell and adds the words "changed" or "added" next to the row. Is it possible for it to highlight cells that reuire changing or have been added (or deleted) without it actually doing the changes? That would be fantastic! Thanks again <<snipped -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Data from two 2 worksheets
try copying that second routine into your workbook's project.
Make the same changes you made before (sheet names and colors). And these two lines will need to be changed: StartRow = 2 'headers in row 1 KeyCol = 3 'column C I assumed that there were headers in row 1, so the data started in row 2. And I assumed that the key column was in column C now--not column A. Then try it out. Roberto R wrote: Sorry Dave, but I'm completely lost now!! This is obviously way over my head! Is there any way I can send you a small extract of the kind of sheets I am thinking of, say for example 10 rows and you can advise? Thanks in advance "Dave Peterson" wrote in message ... I made the change and pasted into the message, then I made one more minor(!) change and broke it. This version fixes the first error: Option Explicit Sub testme() Application.ScreenUpdating = False Dim MstrWks As Worksheet Dim NewWks As Worksheet Dim MstrKeyRange As Range Dim NewKeyRange As Range Dim myCell As Range Dim destCell As Range Dim LastCol As Long Dim iCol As Long Dim res As Variant Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") With MstrWks Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone 'remove all fill color! End With With NewWks Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone End With LastCol = 6 'A to F MstrWks.Columns(LastCol + 1).Clear For Each myCell In MstrKeyRange.Cells With myCell res = Application.Match(.Value, NewKeyRange, 0) If IsError(res) Then .Parent.Cells(.Row, LastCol + 1).Value _ = "Not on other sheet" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 5 Else For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(.Row, LastCol + 1).Value _ = "Changed" End If Next iCol End If End With Next myCell 'check for newly added entries For Each myCell In NewKeyRange.Cells With myCell res = Application.Match(.Value, MstrKeyRange, 0) If IsError(res) Then 'missing from new workbook! .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Added" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 7 Else 'already in the master 'don't do anything End If End With Next myCell Application.ScreenUpdating = True End Sub As for the changing the key column, try this one: Option Explicit Sub testme() Application.ScreenUpdating = False Dim MstrWks As Worksheet Dim NewWks As Worksheet Dim MstrKeyRange As Range Dim NewKeyRange As Range Dim myCell As Range Dim destCell As Range Dim KeyCol As Long Dim StartRow As Long Dim LastCol As Long Dim iCol As Long Dim res As Variant Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") StartRow = 2 'headers in row 1 KeyCol = 3 'column C With MstrWks Set MstrKeyRange = .Range(.Cells(StartRow, KeyCol), _ .Cells(.Rows.Count, KeyCol).End(xlUp)) .Cells.Interior.ColorIndex = xlNone 'remove all fill color! End With With NewWks Set NewKeyRange = .Range(.Cells(StartRow, KeyCol), _ .Cells(.Rows.Count, KeyCol).End(xlUp)) .Cells.Interior.ColorIndex = xlNone End With LastCol = 6 'A to F MstrWks.Columns(LastCol + 1).Clear For Each myCell In MstrKeyRange.Cells With myCell res = Application.Match(.Value, NewKeyRange, 0) If IsError(res) Then .Parent.Cells(.Row, LastCol + 1).Value _ = "Not on other sheet" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 5 Else For iCol = 1 To LastCol If iCol = KeyCol Then 'skip it Else If .Parent.Cells(.Row, iCol).Value _ = NewKeyRange.Parent _ .Cells(res + StartRow - 1, iCol).Value Then 'do nothing, they match Else .Parent.Cells(.Row, iCol).Interior.ColorIndex = 3 .Parent.Cells(.Row, LastCol + 1).Value _ = "Changed" End If End If Next iCol End If End With Next myCell 'check for newly added entries For Each myCell In NewKeyRange.Cells With myCell res = Application.Match(.Value, MstrKeyRange, 0) If IsError(res) Then 'missing from new workbook! .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Added" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 7 Else 'already in the master 'don't do anything End If End With Next myCell Application.ScreenUpdating = True End Sub Roberto R wrote: Hi Dave, I noticed that I have to invert the order of the sheets in the macro. In other words I set the "new" sheet as the MstrWks and the original or "old" sheet as the NewWks to get it report all the changes on the "new" sheet as required. I'm still getting the same error but only if the MstrWks sheet has more rows or part numbers in it than the NewWks. Not sure why(?). The macro works fine though despite the error message except for one thing: if parts are deleted on the "new" sheet, these are not reported as "missing" or "deleted" on the "old" sheet! Any ideas? Apart from that the Macro works fine. I worked out how to change the colors (thanks) also. One more question: If the sheet has more than 6 columns and the "part number" column is not column A but say column F or something, which paramters do I change in the Macro? Is it LastCol = 6 'A to F and Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) Regards "Dave Peterson" wrote in message ... Start a new workbook--you'll close without saving later Tools|Macro|Record macro (into that workbook) Change the fill color of a few cells Stop recording the macro You'll see the numbers for the colors you chose. === And you're going to have to be more specific about the error--what line did it occur on? If it was one of those sheet name lines, try typing the names again. You have a typo. (It could be true. You typed shhet1 instead of sheet1 in your post <bg.) Roberto R wrote: Hi Dave, sorry but it doesn't seem to work. I tried using F8 and it gives me a run time error 9 and nothing happens on the sheets. What do you mean by recording a macro when I change the fill colors? All I did was paste your code into the VBE and changed the shhet1 and sheet 2 names. Help!! Thanks again "Dave Peterson" wrote in message ... Record a macro when you change the fill color for 3 cells. Look at the code to pick out the colors that you want. Then look for .colorindex (3 times) to change in this: Option Explicit Sub testme() Application.ScreenUpdating = False Dim MstrWks As Worksheet Dim NewWks As Worksheet Dim MstrKeyRange As Range Dim NewKeyRange As Range Dim myCell As Range Dim destCell As Range Dim LastCol As Long Dim iCol As Long Dim res As Variant Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") With MstrWks Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone 'remove all fill color! End With With NewWks Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone End With LastCol = 6 'A to F MstrWks.Columns(LastCol + 1).Clear For Each myCell In MstrKeyRange.Cells With myCell res = Application.Match(.Value, NewKeyRange, 0) If IsError(res) Then .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Not on other sheet" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 5 Else For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else ' .Offset(0, iCol).Value _ ' = NewKeyRange(res).Offset(0, iCol).Value .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Changed" End If Next iCol End If End With Next myCell 'check for newly added entries For Each myCell In NewKeyRange.Cells With myCell res = Application.Match(.Value, MstrKeyRange, 0) If IsError(res) Then 'missing from new workbook! ' With MstrWks ' Set destCell _ ' = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) ' End With ' .Resize(1, LastCol).Copy _ ' Destination:=destCell destCell.Parent.Cells(destCell.Row, LastCol + 1).Value _ = "Added" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 7 Else 'already in the master 'don't do anything End If End With Next myCell Application.ScreenUpdating = True End Sub Roberto R wrote: Thanks Dave, I tried the first suggestion. What would be ideal would be to have the words "changed" and "added" on each row that has changed or been added on the "new" sheet only instead of on the "old" one whilst having "deleted" on the "old" sheet for any part numbers which are missing on the "new" sheet. Also, is it possible to have the cells which have been "changed", "added" or "deleted" to be coloured in 3 different colors WITHOUT the changes actually being applied? I know I'm perhaps asking for a lot but now that I can "smell" the final goal, I'm inpatient to see it working! Thanks again "Dave Peterson" wrote in message ... You can comment out the lines (put an apostrophe to the far left of that line) to make it not do the stuff you don't want it to do. In this case, I think you only want to comment this section: For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else ' .Offset(0, iCol).Value _ ' = NewKeyRange(res).Offset(0, iCol).Value .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Changed" Just add those 2 apostrophes and see if that works. I'm not sure what should happen to the Added stuff. If you really want those gone, you can just comment these lines. 'check for newly added entries ' For Each myCell In NewKeyRange.Cells ' With myCell ' res = Application.Match(.Value, MstrKeyRange, 0) ' If IsError(res) Then ' 'missing from new workbook! ' With MstrWks ' Set destCell _ ' = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) ' End With ' .Resize(1, LastCol).Copy _ ' Destination:=destCell ' destCell.Parent.Cells(destCell.Row, LastCol + 1).Value _ ' = "Added" ' Else ' 'already in the master ' 'don't do anything ' End If ' End With ' Next myCell But I don't think you'll get the information that you want. Roberto R wrote: Hi again, IT WORKS! Sorry Dave, it was my security setting that was stopping it from working. I noticed it physically changes the data in the "old" sheet to make it exactly the same as the "new" sheet, colours the cell and adds the words "changed" or "added" next to the row. Is it possible for it to highlight cells that reuire changing or have been added (or deleted) without it actually doing the changes? That would be fantastic! Thanks again <<snipped -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Data from two 2 worksheets
Hi Dave,
sorry it's taken me so long to reply but I was trying out your macro on the "live" spreadsheet. 7600+ rows and 40 columns of data and it works perfectly!!! THANK YOU VERY MUCH!!! There's only one more question, I have noticed that on the spreadsheet, the "part number" can actually appear twice on the same sheet! One of the columns on the spreadsheet indicates the supplier of that particular part. On rare occasions, the same part number is supplied by 2 different suppliers or the supplier can change from month to month as business is awarded or resourced. When running the macro, the second time it finds the same part numbers, it highlights cells refering the differences to the first time instance of the same part number (i.e. sales volumes, prices, supplier name, etc.) and them as "changed" although both rows containing the same part number are actually both correct and no changes have been made. Any ideas? Thanks again "Dave Peterson" wrote in message ... try copying that second routine into your workbook's project. Make the same changes you made before (sheet names and colors). And these two lines will need to be changed: StartRow = 2 'headers in row 1 KeyCol = 3 'column C I assumed that there were headers in row 1, so the data started in row 2. And I assumed that the key column was in column C now--not column A. Then try it out. Roberto R wrote: Sorry Dave, but I'm completely lost now!! This is obviously way over my head! Is there any way I can send you a small extract of the kind of sheets I am thinking of, say for example 10 rows and you can advise? Thanks in advance "Dave Peterson" wrote in message ... I made the change and pasted into the message, then I made one more minor(!) change and broke it. This version fixes the first error: Option Explicit Sub testme() Application.ScreenUpdating = False Dim MstrWks As Worksheet Dim NewWks As Worksheet Dim MstrKeyRange As Range Dim NewKeyRange As Range Dim myCell As Range Dim destCell As Range Dim LastCol As Long Dim iCol As Long Dim res As Variant Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") With MstrWks Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone 'remove all fill color! End With With NewWks Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone End With LastCol = 6 'A to F MstrWks.Columns(LastCol + 1).Clear For Each myCell In MstrKeyRange.Cells With myCell res = Application.Match(.Value, NewKeyRange, 0) If IsError(res) Then .Parent.Cells(.Row, LastCol + 1).Value _ = "Not on other sheet" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 5 Else For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(.Row, LastCol + 1).Value _ = "Changed" End If Next iCol End If End With Next myCell 'check for newly added entries For Each myCell In NewKeyRange.Cells With myCell res = Application.Match(.Value, MstrKeyRange, 0) If IsError(res) Then 'missing from new workbook! .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Added" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 7 Else 'already in the master 'don't do anything End If End With Next myCell Application.ScreenUpdating = True End Sub As for the changing the key column, try this one: Option Explicit Sub testme() Application.ScreenUpdating = False Dim MstrWks As Worksheet Dim NewWks As Worksheet Dim MstrKeyRange As Range Dim NewKeyRange As Range Dim myCell As Range Dim destCell As Range Dim KeyCol As Long Dim StartRow As Long Dim LastCol As Long Dim iCol As Long Dim res As Variant Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") StartRow = 2 'headers in row 1 KeyCol = 3 'column C With MstrWks Set MstrKeyRange = .Range(.Cells(StartRow, KeyCol), _ .Cells(.Rows.Count, KeyCol).End(xlUp)) .Cells.Interior.ColorIndex = xlNone 'remove all fill color! End With With NewWks Set NewKeyRange = .Range(.Cells(StartRow, KeyCol), _ .Cells(.Rows.Count, KeyCol).End(xlUp)) .Cells.Interior.ColorIndex = xlNone End With LastCol = 6 'A to F MstrWks.Columns(LastCol + 1).Clear For Each myCell In MstrKeyRange.Cells With myCell res = Application.Match(.Value, NewKeyRange, 0) If IsError(res) Then .Parent.Cells(.Row, LastCol + 1).Value _ = "Not on other sheet" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 5 Else For iCol = 1 To LastCol If iCol = KeyCol Then 'skip it Else If .Parent.Cells(.Row, iCol).Value _ = NewKeyRange.Parent _ .Cells(res + StartRow - 1, iCol).Value Then 'do nothing, they match Else .Parent.Cells(.Row, iCol).Interior.ColorIndex = 3 .Parent.Cells(.Row, LastCol + 1).Value _ = "Changed" End If End If Next iCol End If End With Next myCell 'check for newly added entries For Each myCell In NewKeyRange.Cells With myCell res = Application.Match(.Value, MstrKeyRange, 0) If IsError(res) Then 'missing from new workbook! .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Added" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 7 Else 'already in the master 'don't do anything End If End With Next myCell Application.ScreenUpdating = True End Sub Roberto R wrote: Hi Dave, I noticed that I have to invert the order of the sheets in the macro. In other words I set the "new" sheet as the MstrWks and the original or "old" sheet as the NewWks to get it report all the changes on the "new" sheet as required. I'm still getting the same error but only if the MstrWks sheet has more rows or part numbers in it than the NewWks. Not sure why(?). The macro works fine though despite the error message except for one thing: if parts are deleted on the "new" sheet, these are not reported as "missing" or "deleted" on the "old" sheet! Any ideas? Apart from that the Macro works fine. I worked out how to change the colors (thanks) also. One more question: If the sheet has more than 6 columns and the "part number" column is not column A but say column F or something, which paramters do I change in the Macro? Is it LastCol = 6 'A to F and Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) Regards "Dave Peterson" wrote in message ... Start a new workbook--you'll close without saving later Tools|Macro|Record macro (into that workbook) Change the fill color of a few cells Stop recording the macro You'll see the numbers for the colors you chose. === And you're going to have to be more specific about the error--what line did it occur on? If it was one of those sheet name lines, try typing the names again. You have a typo. (It could be true. You typed shhet1 instead of sheet1 in your post <bg.) Roberto R wrote: Hi Dave, sorry but it doesn't seem to work. I tried using F8 and it gives me a run time error 9 and nothing happens on the sheets. What do you mean by recording a macro when I change the fill colors? All I did was paste your code into the VBE and changed the shhet1 and sheet 2 names. Help!! Thanks again "Dave Peterson" wrote in message ... Record a macro when you change the fill color for 3 cells. Look at the code to pick out the colors that you want. Then look for .colorindex (3 times) to change in this: Option Explicit Sub testme() Application.ScreenUpdating = False Dim MstrWks As Worksheet Dim NewWks As Worksheet Dim MstrKeyRange As Range Dim NewKeyRange As Range Dim myCell As Range Dim destCell As Range Dim LastCol As Long Dim iCol As Long Dim res As Variant Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") With MstrWks Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone 'remove all fill color! End With With NewWks Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone End With LastCol = 6 'A to F MstrWks.Columns(LastCol + 1).Clear For Each myCell In MstrKeyRange.Cells With myCell res = Application.Match(.Value, NewKeyRange, 0) If IsError(res) Then .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Not on other sheet" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 5 Else For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else ' .Offset(0, iCol).Value _ ' = NewKeyRange(res).Offset(0, iCol).Value .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Changed" End If Next iCol End If End With Next myCell 'check for newly added entries For Each myCell In NewKeyRange.Cells With myCell res = Application.Match(.Value, MstrKeyRange, 0) If IsError(res) Then 'missing from new workbook! ' With MstrWks ' Set destCell _ ' = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) ' End With ' .Resize(1, LastCol).Copy _ ' Destination:=destCell destCell.Parent.Cells(destCell.Row, LastCol + 1).Value _ = "Added" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 7 Else 'already in the master 'don't do anything End If End With Next myCell Application.ScreenUpdating = True End Sub Roberto R wrote: Thanks Dave, I tried the first suggestion. What would be ideal would be to have the words "changed" and "added" on each row that has changed or been added on the "new" sheet only instead of on the "old" one whilst having "deleted" on the "old" sheet for any part numbers which are missing on the "new" sheet. Also, is it possible to have the cells which have been "changed", "added" or "deleted" to be coloured in 3 different colors WITHOUT the changes actually being applied? I know I'm perhaps asking for a lot but now that I can "smell" the final goal, I'm inpatient to see it working! Thanks again "Dave Peterson" wrote in message ... You can comment out the lines (put an apostrophe to the far left of that line) to make it not do the stuff you don't want it to do. In this case, I think you only want to comment this section: For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else ' .Offset(0, iCol).Value _ ' = NewKeyRange(res).Offset(0, iCol).Value .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Changed" Just add those 2 apostrophes and see if that works. I'm not sure what should happen to the Added stuff. If you really want those gone, you can just comment these lines. 'check for newly added entries ' For Each myCell In NewKeyRange.Cells ' With myCell ' res = Application.Match(.Value, MstrKeyRange, 0) ' If IsError(res) Then ' 'missing from new workbook! ' With MstrWks ' Set destCell _ ' = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) ' End With ' .Resize(1, LastCol).Copy _ ' Destination:=destCell ' destCell.Parent.Cells(destCell.Row, LastCol + 1).Value _ ' = "Added" ' Else ' 'already in the master ' 'don't do anything ' End If ' End With ' Next myCell But I don't think you'll get the information that you want. Roberto R wrote: Hi again, IT WORKS! Sorry Dave, it was my security setting that was stopping it from working. I noticed it physically changes the data in the "old" sheet to make it exactly the same as the "new" sheet, colours the cell and adds the words "changed" or "added" next to the row. Is it possible for it to highlight cells that reuire changing or have been added (or deleted) without it actually doing the changes? That would be fantastic! Thanks again <<snipped -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Data from two 2 worksheets
Maybe you can make some unique identifier column by concatenating the supplier
and the part number. Then use that for the key column. Roberto R wrote: Hi Dave, sorry it's taken me so long to reply but I was trying out your macro on the "live" spreadsheet. 7600+ rows and 40 columns of data and it works perfectly!!! THANK YOU VERY MUCH!!! There's only one more question, I have noticed that on the spreadsheet, the "part number" can actually appear twice on the same sheet! One of the columns on the spreadsheet indicates the supplier of that particular part. On rare occasions, the same part number is supplied by 2 different suppliers or the supplier can change from month to month as business is awarded or resourced. When running the macro, the second time it finds the same part numbers, it highlights cells refering the differences to the first time instance of the same part number (i.e. sales volumes, prices, supplier name, etc.) and them as "changed" although both rows containing the same part number are actually both correct and no changes have been made. Any ideas? Thanks again "Dave Peterson" wrote in message ... try copying that second routine into your workbook's project. Make the same changes you made before (sheet names and colors). And these two lines will need to be changed: StartRow = 2 'headers in row 1 KeyCol = 3 'column C I assumed that there were headers in row 1, so the data started in row 2. And I assumed that the key column was in column C now--not column A. Then try it out. Roberto R wrote: Sorry Dave, but I'm completely lost now!! This is obviously way over my head! Is there any way I can send you a small extract of the kind of sheets I am thinking of, say for example 10 rows and you can advise? Thanks in advance "Dave Peterson" wrote in message ... I made the change and pasted into the message, then I made one more minor(!) change and broke it. This version fixes the first error: Option Explicit Sub testme() Application.ScreenUpdating = False Dim MstrWks As Worksheet Dim NewWks As Worksheet Dim MstrKeyRange As Range Dim NewKeyRange As Range Dim myCell As Range Dim destCell As Range Dim LastCol As Long Dim iCol As Long Dim res As Variant Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") With MstrWks Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone 'remove all fill color! End With With NewWks Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone End With LastCol = 6 'A to F MstrWks.Columns(LastCol + 1).Clear For Each myCell In MstrKeyRange.Cells With myCell res = Application.Match(.Value, NewKeyRange, 0) If IsError(res) Then .Parent.Cells(.Row, LastCol + 1).Value _ = "Not on other sheet" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 5 Else For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(.Row, LastCol + 1).Value _ = "Changed" End If Next iCol End If End With Next myCell 'check for newly added entries For Each myCell In NewKeyRange.Cells With myCell res = Application.Match(.Value, MstrKeyRange, 0) If IsError(res) Then 'missing from new workbook! .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Added" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 7 Else 'already in the master 'don't do anything End If End With Next myCell Application.ScreenUpdating = True End Sub As for the changing the key column, try this one: Option Explicit Sub testme() Application.ScreenUpdating = False Dim MstrWks As Worksheet Dim NewWks As Worksheet Dim MstrKeyRange As Range Dim NewKeyRange As Range Dim myCell As Range Dim destCell As Range Dim KeyCol As Long Dim StartRow As Long Dim LastCol As Long Dim iCol As Long Dim res As Variant Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") StartRow = 2 'headers in row 1 KeyCol = 3 'column C With MstrWks Set MstrKeyRange = .Range(.Cells(StartRow, KeyCol), _ .Cells(.Rows.Count, KeyCol).End(xlUp)) .Cells.Interior.ColorIndex = xlNone 'remove all fill color! End With With NewWks Set NewKeyRange = .Range(.Cells(StartRow, KeyCol), _ .Cells(.Rows.Count, KeyCol).End(xlUp)) .Cells.Interior.ColorIndex = xlNone End With LastCol = 6 'A to F MstrWks.Columns(LastCol + 1).Clear For Each myCell In MstrKeyRange.Cells With myCell res = Application.Match(.Value, NewKeyRange, 0) If IsError(res) Then .Parent.Cells(.Row, LastCol + 1).Value _ = "Not on other sheet" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 5 Else For iCol = 1 To LastCol If iCol = KeyCol Then 'skip it Else If .Parent.Cells(.Row, iCol).Value _ = NewKeyRange.Parent _ .Cells(res + StartRow - 1, iCol).Value Then 'do nothing, they match Else .Parent.Cells(.Row, iCol).Interior.ColorIndex = 3 .Parent.Cells(.Row, LastCol + 1).Value _ = "Changed" End If End If Next iCol End If End With Next myCell 'check for newly added entries For Each myCell In NewKeyRange.Cells With myCell res = Application.Match(.Value, MstrKeyRange, 0) If IsError(res) Then 'missing from new workbook! .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Added" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 7 Else 'already in the master 'don't do anything End If End With Next myCell Application.ScreenUpdating = True End Sub Roberto R wrote: Hi Dave, I noticed that I have to invert the order of the sheets in the macro. In other words I set the "new" sheet as the MstrWks and the original or "old" sheet as the NewWks to get it report all the changes on the "new" sheet as required. I'm still getting the same error but only if the MstrWks sheet has more rows or part numbers in it than the NewWks. Not sure why(?). The macro works fine though despite the error message except for one thing: if parts are deleted on the "new" sheet, these are not reported as "missing" or "deleted" on the "old" sheet! Any ideas? Apart from that the Macro works fine. I worked out how to change the colors (thanks) also. One more question: If the sheet has more than 6 columns and the "part number" column is not column A but say column F or something, which paramters do I change in the Macro? Is it LastCol = 6 'A to F and Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) Regards "Dave Peterson" wrote in message ... Start a new workbook--you'll close without saving later Tools|Macro|Record macro (into that workbook) Change the fill color of a few cells Stop recording the macro You'll see the numbers for the colors you chose. === And you're going to have to be more specific about the error--what line did it occur on? If it was one of those sheet name lines, try typing the names again. You have a typo. (It could be true. You typed shhet1 instead of sheet1 in your post <bg.) Roberto R wrote: Hi Dave, sorry but it doesn't seem to work. I tried using F8 and it gives me a run time error 9 and nothing happens on the sheets. What do you mean by recording a macro when I change the fill colors? All I did was paste your code into the VBE and changed the shhet1 and sheet 2 names. Help!! Thanks again "Dave Peterson" wrote in message ... Record a macro when you change the fill color for 3 cells. Look at the code to pick out the colors that you want. Then look for .colorindex (3 times) to change in this: Option Explicit Sub testme() Application.ScreenUpdating = False Dim MstrWks As Worksheet Dim NewWks As Worksheet Dim MstrKeyRange As Range Dim NewKeyRange As Range Dim myCell As Range Dim destCell As Range Dim LastCol As Long Dim iCol As Long Dim res As Variant Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") With MstrWks Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone 'remove all fill color! End With With NewWks Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone End With LastCol = 6 'A to F MstrWks.Columns(LastCol + 1).Clear For Each myCell In MstrKeyRange.Cells With myCell res = Application.Match(.Value, NewKeyRange, 0) If IsError(res) Then .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Not on other sheet" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 5 Else For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else ' .Offset(0, iCol).Value _ ' = NewKeyRange(res).Offset(0, iCol).Value .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Changed" End If Next iCol End If End With Next myCell 'check for newly added entries For Each myCell In NewKeyRange.Cells With myCell res = Application.Match(.Value, MstrKeyRange, 0) If IsError(res) Then 'missing from new workbook! ' With MstrWks ' Set destCell _ ' = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) ' End With ' .Resize(1, LastCol).Copy _ ' Destination:=destCell destCell.Parent.Cells(destCell.Row, LastCol + 1).Value _ = "Added" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 7 Else 'already in the master 'don't do anything End If End With Next myCell Application.ScreenUpdating = True End Sub Roberto R wrote: Thanks Dave, I tried the first suggestion. What would be ideal would be to have the words "changed" and "added" on each row that has changed or been added on the "new" sheet only instead of on the "old" one whilst having "deleted" on the "old" sheet for any part numbers which are missing on the "new" sheet. Also, is it possible to have the cells which have been "changed", "added" or "deleted" to be coloured in 3 different colors WITHOUT the changes actually being applied? I know I'm perhaps asking for a lot but now that I can "smell" the final goal, I'm inpatient to see it working! Thanks again "Dave Peterson" wrote in message ... You can comment out the lines (put an apostrophe to the far left of that line) to make it not do the stuff you don't want it to do. In this case, I think you only want to comment this section: For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else ' .Offset(0, iCol).Value _ ' = NewKeyRange(res).Offset(0, iCol).Value .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Changed" Just add those 2 apostrophes and see if that works. I'm not sure what should happen to the Added stuff. If you really want those gone, you can just comment these lines. 'check for newly added entries ' For Each myCell In NewKeyRange.Cells ' With myCell ' res = Application.Match(.Value, MstrKeyRange, 0) ' If IsError(res) Then ' 'missing from new workbook! ' With MstrWks ' Set destCell _ ' = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) ' End With ' .Resize(1, LastCol).Copy _ ' Destination:=destCell ' destCell.Parent.Cells(destCell.Row, LastCol + 1).Value _ ' = "Added" ' Else ' 'already in the master ' 'don't do anything ' End If ' End With ' Next myCell But I don't think you'll get the information that you want. Roberto R wrote: Hi again, IT WORKS! Sorry Dave, it was my security setting that was stopping it from working. I noticed it physically changes the data in the "old" sheet to make it exactly the same as the "new" sheet, colours the cell and adds the words "changed" or "added" next to the row. Is it possible for it to highlight cells that reuire changing or have been added (or deleted) without it actually doing the changes? That would be fantastic! Thanks again <<snipped -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Data from two 2 worksheets
Tried, works fine although I need to ensure that the columns I use don't
change at otherwise it replicates the part as if it were a new one. Thanks very much for all your help!! Regards "Roberto R" wrote in message ... Thanks Dave, I'll try it and let you know. "Dave Peterson" wrote in message ... Maybe you can make some unique identifier column by concatenating the supplier and the part number. Then use that for the key column. Roberto R wrote: Hi Dave, sorry it's taken me so long to reply but I was trying out your macro on the "live" spreadsheet. 7600+ rows and 40 columns of data and it works perfectly!!! THANK YOU VERY MUCH!!! There's only one more question, I have noticed that on the spreadsheet, the "part number" can actually appear twice on the same sheet! One of the columns on the spreadsheet indicates the supplier of that particular part. On rare occasions, the same part number is supplied by 2 different suppliers or the supplier can change from month to month as business is awarded or resourced. When running the macro, the second time it finds the same part numbers, it highlights cells refering the differences to the first time instance of the same part number (i.e. sales volumes, prices, supplier name, etc.) and them as "changed" although both rows containing the same part number are actually both correct and no changes have been made. Any ideas? Thanks again "Dave Peterson" wrote in message ... try copying that second routine into your workbook's project. Make the same changes you made before (sheet names and colors). And these two lines will need to be changed: StartRow = 2 'headers in row 1 KeyCol = 3 'column C I assumed that there were headers in row 1, so the data started in row 2. And I assumed that the key column was in column C now--not column A. Then try it out. Roberto R wrote: Sorry Dave, but I'm completely lost now!! This is obviously way over my head! Is there any way I can send you a small extract of the kind of sheets I am thinking of, say for example 10 rows and you can advise? Thanks in advance "Dave Peterson" wrote in message ... I made the change and pasted into the message, then I made one more minor(!) change and broke it. This version fixes the first error: Option Explicit Sub testme() Application.ScreenUpdating = False Dim MstrWks As Worksheet Dim NewWks As Worksheet Dim MstrKeyRange As Range Dim NewKeyRange As Range Dim myCell As Range Dim destCell As Range Dim LastCol As Long Dim iCol As Long Dim res As Variant Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") With MstrWks Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone 'remove all fill color! End With With NewWks Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone End With LastCol = 6 'A to F MstrWks.Columns(LastCol + 1).Clear For Each myCell In MstrKeyRange.Cells With myCell res = Application.Match(.Value, NewKeyRange, 0) If IsError(res) Then .Parent.Cells(.Row, LastCol + 1).Value _ = "Not on other sheet" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 5 Else For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(.Row, LastCol + 1).Value _ = "Changed" End If Next iCol End If End With Next myCell 'check for newly added entries For Each myCell In NewKeyRange.Cells With myCell res = Application.Match(.Value, MstrKeyRange, 0) If IsError(res) Then 'missing from new workbook! .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Added" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 7 Else 'already in the master 'don't do anything End If End With Next myCell Application.ScreenUpdating = True End Sub As for the changing the key column, try this one: Option Explicit Sub testme() Application.ScreenUpdating = False Dim MstrWks As Worksheet Dim NewWks As Worksheet Dim MstrKeyRange As Range Dim NewKeyRange As Range Dim myCell As Range Dim destCell As Range Dim KeyCol As Long Dim StartRow As Long Dim LastCol As Long Dim iCol As Long Dim res As Variant Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") StartRow = 2 'headers in row 1 KeyCol = 3 'column C With MstrWks Set MstrKeyRange = .Range(.Cells(StartRow, KeyCol), _ .Cells(.Rows.Count, KeyCol).End(xlUp)) .Cells.Interior.ColorIndex = xlNone 'remove all fill color! End With With NewWks Set NewKeyRange = .Range(.Cells(StartRow, KeyCol), _ .Cells(.Rows.Count, KeyCol).End(xlUp)) .Cells.Interior.ColorIndex = xlNone End With LastCol = 6 'A to F MstrWks.Columns(LastCol + 1).Clear For Each myCell In MstrKeyRange.Cells With myCell res = Application.Match(.Value, NewKeyRange, 0) If IsError(res) Then .Parent.Cells(.Row, LastCol + 1).Value _ = "Not on other sheet" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 5 Else For iCol = 1 To LastCol If iCol = KeyCol Then 'skip it Else If .Parent.Cells(.Row, iCol).Value _ = NewKeyRange.Parent _ .Cells(res + StartRow - 1, iCol).Value Then 'do nothing, they match Else .Parent.Cells(.Row, iCol).Interior.ColorIndex = 3 .Parent.Cells(.Row, LastCol + 1).Value _ = "Changed" End If End If Next iCol End If End With Next myCell 'check for newly added entries For Each myCell In NewKeyRange.Cells With myCell res = Application.Match(.Value, MstrKeyRange, 0) If IsError(res) Then 'missing from new workbook! .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Added" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 7 Else 'already in the master 'don't do anything End If End With Next myCell Application.ScreenUpdating = True End Sub Roberto R wrote: Hi Dave, I noticed that I have to invert the order of the sheets in the macro. In other words I set the "new" sheet as the MstrWks and the original or "old" sheet as the NewWks to get it report all the changes on the "new" sheet as required. I'm still getting the same error but only if the MstrWks sheet has more rows or part numbers in it than the NewWks. Not sure why(?). The macro works fine though despite the error message except for one thing: if parts are deleted on the "new" sheet, these are not reported as "missing" or "deleted" on the "old" sheet! Any ideas? Apart from that the Macro works fine. I worked out how to change the colors (thanks) also. One more question: If the sheet has more than 6 columns and the "part number" column is not column A but say column F or something, which paramters do I change in the Macro? Is it LastCol = 6 'A to F and Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) Regards "Dave Peterson" wrote in message ... Start a new workbook--you'll close without saving later Tools|Macro|Record macro (into that workbook) Change the fill color of a few cells Stop recording the macro You'll see the numbers for the colors you chose. === And you're going to have to be more specific about the error--what line did it occur on? If it was one of those sheet name lines, try typing the names again. You have a typo. (It could be true. You typed shhet1 instead of sheet1 in your post <bg.) Roberto R wrote: Hi Dave, sorry but it doesn't seem to work. I tried using F8 and it gives me a run time error 9 and nothing happens on the sheets. What do you mean by recording a macro when I change the fill colors? All I did was paste your code into the VBE and changed the shhet1 and sheet 2 names. Help!! Thanks again "Dave Peterson" wrote in message ... Record a macro when you change the fill color for 3 cells. Look at the code to pick out the colors that you want. Then look for .colorindex (3 times) to change in this: Option Explicit Sub testme() Application.ScreenUpdating = False Dim MstrWks As Worksheet Dim NewWks As Worksheet Dim MstrKeyRange As Range Dim NewKeyRange As Range Dim myCell As Range Dim destCell As Range Dim LastCol As Long Dim iCol As Long Dim res As Variant Set MstrWks = ActiveWorkbook.Worksheets("sheet1") Set NewWks = ActiveWorkbook.Worksheets("sheet2") With MstrWks Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone 'remove all fill color! End With With NewWks Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)) .Cells.Interior.ColorIndex = xlNone End With LastCol = 6 'A to F MstrWks.Columns(LastCol + 1).Clear For Each myCell In MstrKeyRange.Cells With myCell res = Application.Match(.Value, NewKeyRange, 0) If IsError(res) Then .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Not on other sheet" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 5 Else For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else ' .Offset(0, iCol).Value _ ' = NewKeyRange(res).Offset(0, iCol).Value .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Changed" End If Next iCol End If End With Next myCell 'check for newly added entries For Each myCell In NewKeyRange.Cells With myCell res = Application.Match(.Value, MstrKeyRange, 0) If IsError(res) Then 'missing from new workbook! ' With MstrWks ' Set destCell _ ' = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) ' End With ' .Resize(1, LastCol).Copy _ ' Destination:=destCell destCell.Parent.Cells(destCell.Row, LastCol + 1).Value _ = "Added" myCell.EntireRow.Resize(1, LastCol).Interior.ColorIndex = 7 Else 'already in the master 'don't do anything End If End With Next myCell Application.ScreenUpdating = True End Sub Roberto R wrote: Thanks Dave, I tried the first suggestion. What would be ideal would be to have the words "changed" and "added" on each row that has changed or been added on the "new" sheet only instead of on the "old" one whilst having "deleted" on the "old" sheet for any part numbers which are missing on the "new" sheet. Also, is it possible to have the cells which have been "changed", "added" or "deleted" to be coloured in 3 different colors WITHOUT the changes actually being applied? I know I'm perhaps asking for a lot but now that I can "smell" the final goal, I'm inpatient to see it working! Thanks again "Dave Peterson" wrote in message ... You can comment out the lines (put an apostrophe to the far left of that line) to make it not do the stuff you don't want it to do. In this case, I think you only want to comment this section: For iCol = 1 To LastCol - 1 If .Offset(0, iCol).Value _ = NewKeyRange(res).Offset(0, iCol).Value Then 'do nothing, they match Else ' .Offset(0, iCol).Value _ ' = NewKeyRange(res).Offset(0, iCol).Value .Offset(0, iCol).Interior.ColorIndex = 3 .Parent.Cells(myCell.Row, LastCol + 1).Value _ = "Changed" Just add those 2 apostrophes and see if that works. I'm not sure what should happen to the Added stuff. If you really want those gone, you can just comment these lines. 'check for newly added entries ' For Each myCell In NewKeyRange.Cells ' With myCell ' res = Application.Match(.Value, MstrKeyRange, 0) ' If IsError(res) Then ' 'missing from new workbook! ' With MstrWks ' Set destCell _ ' = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) ' End With ' .Resize(1, LastCol).Copy _ ' Destination:=destCell ' destCell.Parent.Cells(destCell.Row, LastCol + 1).Value _ ' = "Added" ' Else ' 'already in the master ' 'don't do anything ' End If ' End With ' Next myCell But I don't think you'll get the information that you want. Roberto R wrote: Hi again, IT WORKS! Sorry Dave, it was my security setting that was stopping it from working. I noticed it physically changes the data in the "old" sheet to make it exactly the same as the "new" sheet, colours the cell and adds the words "changed" or "added" next to the row. Is it possible for it to highlight cells that reuire changing or have been added (or deleted) without it actually doing the changes? That would be fantastic! Thanks again <<snipped -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro | Excel Discussion (Misc queries) | |||
Match and Sort for two range of data on different worksheets? | New Users to Excel | |||
ranking query | Excel Discussion (Misc queries) | |||
Sharing data across worksheets within a workbook based on identifi | Excel Discussion (Misc queries) | |||
sharing rows of data across multiple worksheets within a workbook | Excel Worksheet Functions |