Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare 2 worksheets all 6 columns in each worksheet
Hello
Each week we produce a report of peoples time and bill this. It has 6 column fields. The person entering their time can go back and change their time(Hours) up to 2 weeks back. We can re-obtain the data and would like to run a compare against both worksheets and where there are differences copy that line in another worksheet and show the difference for that whole row. There are many resources but we expect very few changes...just trying to identify them.... Ex. Worksheet 1 Column A B C D E F Pete Smith 8/29/2008 4 TaskA PRJ840 EN Worksheet 2 Column A B C D E F Pete Smith 8/29/2008 12 TaskA PRJ840 EN What we would like it to do..... Worksheet 3 Difference... Column A B C D E F Pete Smith 8/29/2008 8 TaskA PRJ840 EN Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare 2 worksheets all 6 columns in each worksheet
Is Column C the only column that can vary? I mean, what if the only change
is the date... the task... the project? How would those be reported (can't show a subtraction of TaskE instead of TaskA)? -- Rick (MVP - Excel) "Bud" wrote in message ... Hello Each week we produce a report of peoples time and bill this. It has 6 column fields. The person entering their time can go back and change their time(Hours) up to 2 weeks back. We can re-obtain the data and would like to run a compare against both worksheets and where there are differences copy that line in another worksheet and show the difference for that whole row. There are many resources but we expect very few changes...just trying to identify them.... Ex. Worksheet 1 Column A B C D E F Pete Smith 8/29/2008 4 TaskA PRJ840 EN Worksheet 2 Column A B C D E F Pete Smith 8/29/2008 12 TaskA PRJ840 EN What we would like it to do..... Worksheet 3 Difference... Column A B C D E F Pete Smith 8/29/2008 8 TaskA PRJ840 EN Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare 2 worksheets all 6 columns in each worksheet
Nothing can change except the hours....This is a Timekeeping system called
SAP. The people can reselect the past two weeks transactions and the only thing they can update on the screen itself is the hours. All other fields are non-changeable on the screen including the date. The data can than be reselected by going to the system and downloading it into excel. Just looking for a way to compare what changed and place the difference in another worksheet. We already saved what they originally did. We can re-obtain the data for that week which would have the updated hours. We need to have a way to show the difference automtically in another worksheet. "Rick Rothstein" wrote: Is Column C the only column that can vary? I mean, what if the only change is the date... the task... the project? How would those be reported (can't show a subtraction of TaskE instead of TaskA)? -- Rick (MVP - Excel) "Bud" wrote in message ... Hello Each week we produce a report of peoples time and bill this. It has 6 column fields. The person entering their time can go back and change their time(Hours) up to 2 weeks back. We can re-obtain the data and would like to run a compare against both worksheets and where there are differences copy that line in another worksheet and show the difference for that whole row. There are many resources but we expect very few changes...just trying to identify them.... Ex. Worksheet 1 Column A B C D E F Pete Smith 8/29/2008 4 TaskA PRJ840 EN Worksheet 2 Column A B C D E F Pete Smith 8/29/2008 12 TaskA PRJ840 EN What we would like it to do..... Worksheet 3 Difference... Column A B C D E F Pete Smith 8/29/2008 8 TaskA PRJ840 EN Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare 2 worksheets all 6 columns in each worksheet
One other thing can happen and that is they may have forgotton to put in
their time for a Sunday and the m anager may have reminded them. That employee would than select that task and enter hours. This transaction than would not have been there before. This is why the matching would have to occur on columns A,B,C, and D "Bud" wrote: Nothing can change except the hours....This is a Timekeeping system called SAP. The people can reselect the past two weeks transactions and the only thing they can update on the screen itself is the hours. All other fields are non-changeable on the screen including the date. The data can than be reselected by going to the system and downloading it into excel. Just looking for a way to compare what changed and place the difference in another worksheet. We already saved what they originally did. We can re-obtain the data for that week which would have the updated hours. We need to have a way to show the difference automtically in another worksheet. "Rick Rothstein" wrote: Is Column C the only column that can vary? I mean, what if the only change is the date... the task... the project? How would those be reported (can't show a subtraction of TaskE instead of TaskA)? -- Rick (MVP - Excel) "Bud" wrote in message ... Hello Each week we produce a report of peoples time and bill this. It has 6 column fields. The person entering their time can go back and change their time(Hours) up to 2 weeks back. We can re-obtain the data and would like to run a compare against both worksheets and where there are differences copy that line in another worksheet and show the difference for that whole row. There are many resources but we expect very few changes...just trying to identify them.... Ex. Worksheet 1 Column A B C D E F Pete Smith 8/29/2008 4 TaskA PRJ840 EN Worksheet 2 Column A B C D E F Pete Smith 8/29/2008 12 TaskA PRJ840 EN What we would like it to do..... Worksheet 3 Difference... Column A B C D E F Pete Smith 8/29/2008 8 TaskA PRJ840 EN Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare 2 worksheets all 6 columns in each worksheet
I think this macro will do what you want...
Sub UpdatedReport() Dim X1 As Long Dim X2 As Long Dim X3 As Long Dim RowVals As String Dim WS(1 To 3) As Worksheet Dim LastRow(1 To 3) As Long For X1 = 1 To 3 Set WS(X1) = Worksheets(Split("Worksheet 1,Worksheet 2,Worksheet 3", _ ",")(X1 - 1)) LastRow(X1) = WS(X1).Cells(WS(X1).Rows.Count, "A").End(xlUp).Row Next For X2 = 2 To LastRow(2) With WS(2) RowVals = .Cells(X2, "A").Value & .Cells(X2, "B").Value & _ .Cells(X2, "D").Value & .Cells(X2, "E").Value & _ .Cells(X2, "F").Value For X1 = 2 To LastRow(1) With WS(1) If RowVals = .Cells(X1, "A").Value & .Cells(X1, "B").Value & _ .Cells(X1, "D").Value & .Cells(X1, "E").Value & _ .Cells(X1, "F").Value Then .Rows(X1).Copy WS(3).Cells(LastRow(3) + 1, "A") WS(3).Cells(LastRow(3) + 1, "C").Value = _ WS(2).Cells(X2, "C").Value - WS(1).Cells(X1, "C").Value LastRow(3) = LastRow(3) + 1 Exit For End If If X1 = LastRow(1) Then WS(2).Rows(X2).Copy WS(3).Cells(LastRow(3) + 1, "A") End If End With Next End With Next End Sub -- Rick (MVP - Excel) "Bud" wrote in message ... One other thing can happen and that is they may have forgotton to put in their time for a Sunday and the m anager may have reminded them. That employee would than select that task and enter hours. This transaction than would not have been there before. This is why the matching would have to occur on columns A,B,C, and D "Bud" wrote: Nothing can change except the hours....This is a Timekeeping system called SAP. The people can reselect the past two weeks transactions and the only thing they can update on the screen itself is the hours. All other fields are non-changeable on the screen including the date. The data can than be reselected by going to the system and downloading it into excel. Just looking for a way to compare what changed and place the difference in another worksheet. We already saved what they originally did. We can re-obtain the data for that week which would have the updated hours. We need to have a way to show the difference automtically in another worksheet. "Rick Rothstein" wrote: Is Column C the only column that can vary? I mean, what if the only change is the date... the task... the project? How would those be reported (can't show a subtraction of TaskE instead of TaskA)? -- Rick (MVP - Excel) "Bud" wrote in message ... Hello Each week we produce a report of peoples time and bill this. It has 6 column fields. The person entering their time can go back and change their time(Hours) up to 2 weeks back. We can re-obtain the data and would like to run a compare against both worksheets and where there are differences copy that line in another worksheet and show the difference for that whole row. There are many resources but we expect very few changes...just trying to identify them.... Ex. Worksheet 1 Column A B C D E F Pete Smith 8/29/2008 4 TaskA PRJ840 EN Worksheet 2 Column A B C D E F Pete Smith 8/29/2008 12 TaskA PRJ840 EN What we would like it to do..... Worksheet 3 Difference... Column A B C D E F Pete Smith 8/29/2008 8 TaskA PRJ840 EN Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare 2 worksheets all 6 columns in each worksheet
I am getting a subscript out of range message on this
Set WS(X1) = Worksheets(Split("Worksheet 1,Worksheet 2,Worksheet 3", _ ",")(X1 - 1)) I actually have data from old timesheet in a worksheet called SAP-OLD and than the current timesheet data from that same time period as what is in the SAP-OLD in a worksheet called SAP-NEW. I am hoping to compare the two since they are different placing the differences in a worksheet called Diff I have written some code in excel macro before but can't understand fully what your code says. Thanks "Rick Rothstein" wrote: I think this macro will do what you want... Sub UpdatedReport() Dim X1 As Long Dim X2 As Long Dim X3 As Long Dim RowVals As String Dim WS(1 To 3) As Worksheet Dim LastRow(1 To 3) As Long For X1 = 1 To 3 Set WS(X1) = Worksheets(Split("Worksheet 1,Worksheet 2,Worksheet 3", _ ",")(X1 - 1)) LastRow(X1) = WS(X1).Cells(WS(X1).Rows.Count, "A").End(xlUp).Row Next For X2 = 2 To LastRow(2) With WS(2) RowVals = .Cells(X2, "A").Value & .Cells(X2, "B").Value & _ .Cells(X2, "D").Value & .Cells(X2, "E").Value & _ .Cells(X2, "F").Value For X1 = 2 To LastRow(1) With WS(1) If RowVals = .Cells(X1, "A").Value & .Cells(X1, "B").Value & _ .Cells(X1, "D").Value & .Cells(X1, "E").Value & _ .Cells(X1, "F").Value Then .Rows(X1).Copy WS(3).Cells(LastRow(3) + 1, "A") WS(3).Cells(LastRow(3) + 1, "C").Value = _ WS(2).Cells(X2, "C").Value - WS(1).Cells(X1, "C").Value LastRow(3) = LastRow(3) + 1 Exit For End If If X1 = LastRow(1) Then WS(2).Rows(X2).Copy WS(3).Cells(LastRow(3) + 1, "A") End If End With Next End With Next End Sub -- Rick (MVP - Excel) "Bud" wrote in message ... One other thing can happen and that is they may have forgotton to put in their time for a Sunday and the m anager may have reminded them. That employee would than select that task and enter hours. This transaction than would not have been there before. This is why the matching would have to occur on columns A,B,C, and D "Bud" wrote: Nothing can change except the hours....This is a Timekeeping system called SAP. The people can reselect the past two weeks transactions and the only thing they can update on the screen itself is the hours. All other fields are non-changeable on the screen including the date. The data can than be reselected by going to the system and downloading it into excel. Just looking for a way to compare what changed and place the difference in another worksheet. We already saved what they originally did. We can re-obtain the data for that week which would have the updated hours. We need to have a way to show the difference automtically in another worksheet. "Rick Rothstein" wrote: Is Column C the only column that can vary? I mean, what if the only change is the date... the task... the project? How would those be reported (can't show a subtraction of TaskE instead of TaskA)? -- Rick (MVP - Excel) "Bud" wrote in message ... Hello Each week we produce a report of peoples time and bill this. It has 6 column fields. The person entering their time can go back and change their time(Hours) up to 2 weeks back. We can re-obtain the data and would like to run a compare against both worksheets and where there are differences copy that line in another worksheet and show the difference for that whole row. There are many resources but we expect very few changes...just trying to identify them.... Ex. Worksheet 1 Column A B C D E F Pete Smith 8/29/2008 4 TaskA PRJ840 EN Worksheet 2 Column A B C D E F Pete Smith 8/29/2008 12 TaskA PRJ840 EN What we would like it to do..... Worksheet 3 Difference... Column A B C D E F Pete Smith 8/29/2008 8 TaskA PRJ840 EN Thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare 2 worksheets all 6 columns in each worksheet
Are your worksheets in fact named "Worksheet 1", "Worksheet 2" and
"Worksheet 3" as your original post seemed to indicate? If not, change those pieces of text to your actual sheet names. -- Rick (MVP - Excel) "Bud" wrote in message ... I am getting a subscript out of range message on this Set WS(X1) = Worksheets(Split("Worksheet 1,Worksheet 2,Worksheet 3", _ ",")(X1 - 1)) I actually have data from old timesheet in a worksheet called SAP-OLD and than the current timesheet data from that same time period as what is in the SAP-OLD in a worksheet called SAP-NEW. I am hoping to compare the two since they are different placing the differences in a worksheet called Diff I have written some code in excel macro before but can't understand fully what your code says. Thanks "Rick Rothstein" wrote: I think this macro will do what you want... Sub UpdatedReport() Dim X1 As Long Dim X2 As Long Dim X3 As Long Dim RowVals As String Dim WS(1 To 3) As Worksheet Dim LastRow(1 To 3) As Long For X1 = 1 To 3 Set WS(X1) = Worksheets(Split("Worksheet 1,Worksheet 2,Worksheet 3", _ ",")(X1 - 1)) LastRow(X1) = WS(X1).Cells(WS(X1).Rows.Count, "A").End(xlUp).Row Next For X2 = 2 To LastRow(2) With WS(2) RowVals = .Cells(X2, "A").Value & .Cells(X2, "B").Value & _ .Cells(X2, "D").Value & .Cells(X2, "E").Value & _ .Cells(X2, "F").Value For X1 = 2 To LastRow(1) With WS(1) If RowVals = .Cells(X1, "A").Value & .Cells(X1, "B").Value & _ .Cells(X1, "D").Value & .Cells(X1, "E").Value & _ .Cells(X1, "F").Value Then .Rows(X1).Copy WS(3).Cells(LastRow(3) + 1, "A") WS(3).Cells(LastRow(3) + 1, "C").Value = _ WS(2).Cells(X2, "C").Value - WS(1).Cells(X1, "C").Value LastRow(3) = LastRow(3) + 1 Exit For End If If X1 = LastRow(1) Then WS(2).Rows(X2).Copy WS(3).Cells(LastRow(3) + 1, "A") End If End With Next End With Next End Sub -- Rick (MVP - Excel) "Bud" wrote in message ... One other thing can happen and that is they may have forgotton to put in their time for a Sunday and the m anager may have reminded them. That employee would than select that task and enter hours. This transaction than would not have been there before. This is why the matching would have to occur on columns A,B,C, and D "Bud" wrote: Nothing can change except the hours....This is a Timekeeping system called SAP. The people can reselect the past two weeks transactions and the only thing they can update on the screen itself is the hours. All other fields are non-changeable on the screen including the date. The data can than be reselected by going to the system and downloading it into excel. Just looking for a way to compare what changed and place the difference in another worksheet. We already saved what they originally did. We can re-obtain the data for that week which would have the updated hours. We need to have a way to show the difference automtically in another worksheet. "Rick Rothstein" wrote: Is Column C the only column that can vary? I mean, what if the only change is the date... the task... the project? How would those be reported (can't show a subtraction of TaskE instead of TaskA)? -- Rick (MVP - Excel) "Bud" wrote in message ... Hello Each week we produce a report of peoples time and bill this. It has 6 column fields. The person entering their time can go back and change their time(Hours) up to 2 weeks back. We can re-obtain the data and would like to run a compare against both worksheets and where there are differences copy that line in another worksheet and show the difference for that whole row. There are many resources but we expect very few changes...just trying to identify them.... Ex. Worksheet 1 Column A B C D E F Pete Smith 8/29/2008 4 TaskA PRJ840 EN Worksheet 2 Column A B C D E F Pete Smith 8/29/2008 12 TaskA PRJ840 EN What we would like it to do..... Worksheet 3 Difference... Column A B C D E F Pete Smith 8/29/2008 8 TaskA PRJ840 EN Thanks |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Compare 2 worksheets all 6 columns in each worksheet
Hi Rick
This mainly works but it doesn't subtract accurately in all cases. There were only 2 differences in both files but it showed more than that. Where lines were identical it placed a difference in it. I am not totally sure what each specific line of code is doing otherwise I may be able to correct it. Would it be possible for you to describe the code? ....or perhpas understand why it would be doing that Thanks "Rick Rothstein" wrote: Are your worksheets in fact named "Worksheet 1", "Worksheet 2" and "Worksheet 3" as your original post seemed to indicate? If not, change those pieces of text to your actual sheet names. -- Rick (MVP - Excel) "Bud" wrote in message ... I am getting a subscript out of range message on this Set WS(X1) = Worksheets(Split("Worksheet 1,Worksheet 2,Worksheet 3", _ ",")(X1 - 1)) I actually have data from old timesheet in a worksheet called SAP-OLD and than the current timesheet data from that same time period as what is in the SAP-OLD in a worksheet called SAP-NEW. I am hoping to compare the two since they are different placing the differences in a worksheet called Diff I have written some code in excel macro before but can't understand fully what your code says. Thanks "Rick Rothstein" wrote: I think this macro will do what you want... Sub UpdatedReport() Dim X1 As Long Dim X2 As Long Dim X3 As Long Dim RowVals As String Dim WS(1 To 3) As Worksheet Dim LastRow(1 To 3) As Long For X1 = 1 To 3 Set WS(X1) = Worksheets(Split("Worksheet 1,Worksheet 2,Worksheet 3", _ ",")(X1 - 1)) LastRow(X1) = WS(X1).Cells(WS(X1).Rows.Count, "A").End(xlUp).Row Next For X2 = 2 To LastRow(2) With WS(2) RowVals = .Cells(X2, "A").Value & .Cells(X2, "B").Value & _ .Cells(X2, "D").Value & .Cells(X2, "E").Value & _ .Cells(X2, "F").Value For X1 = 2 To LastRow(1) With WS(1) If RowVals = .Cells(X1, "A").Value & .Cells(X1, "B").Value & _ .Cells(X1, "D").Value & .Cells(X1, "E").Value & _ .Cells(X1, "F").Value Then .Rows(X1).Copy WS(3).Cells(LastRow(3) + 1, "A") WS(3).Cells(LastRow(3) + 1, "C").Value = _ WS(2).Cells(X2, "C").Value - WS(1).Cells(X1, "C").Value LastRow(3) = LastRow(3) + 1 Exit For End If If X1 = LastRow(1) Then WS(2).Rows(X2).Copy WS(3).Cells(LastRow(3) + 1, "A") End If End With Next End With Next End Sub -- Rick (MVP - Excel) "Bud" wrote in message ... One other thing can happen and that is they may have forgotton to put in their time for a Sunday and the m anager may have reminded them. That employee would than select that task and enter hours. This transaction than would not have been there before. This is why the matching would have to occur on columns A,B,C, and D "Bud" wrote: Nothing can change except the hours....This is a Timekeeping system called SAP. The people can reselect the past two weeks transactions and the only thing they can update on the screen itself is the hours. All other fields are non-changeable on the screen including the date. The data can than be reselected by going to the system and downloading it into excel. Just looking for a way to compare what changed and place the difference in another worksheet. We already saved what they originally did. We can re-obtain the data for that week which would have the updated hours. We need to have a way to show the difference automtically in another worksheet. "Rick Rothstein" wrote: Is Column C the only column that can vary? I mean, what if the only change is the date... the task... the project? How would those be reported (can't show a subtraction of TaskE instead of TaskA)? -- Rick (MVP - Excel) "Bud" wrote in message ... Hello Each week we produce a report of peoples time and bill this. It has 6 column fields. The person entering their time can go back and change their time(Hours) up to 2 weeks back. We can re-obtain the data and would like to run a compare against both worksheets and where there are differences copy that line in another worksheet and show the difference for that whole row. There are many resources but we expect very few changes...just trying to identify them.... Ex. Worksheet 1 Column A B C D E F Pete Smith 8/29/2008 4 TaskA PRJ840 EN Worksheet 2 Column A B C D E F Pete Smith 8/29/2008 12 TaskA PRJ840 EN What we would like it to do..... Worksheet 3 Difference... Column A B C D E F Pete Smith 8/29/2008 8 TaskA PRJ840 EN Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to compare two worksheets and create a new worksheet | Excel Discussion (Misc queries) | |||
Compare 2 columns - new worksheets | Excel Worksheet Functions | |||
compare two columns with different ranges in two worksheets | Excel Discussion (Misc queries) | |||
how to compare columns in 4 worksheets | Excel Discussion (Misc queries) | |||
compare columns of different worksheets | Excel Discussion (Misc queries) |