Hello

Someone gave me this code to use to do a compare concatenating fields for
the compare but it doesn't correctly work. I can't figure out the code and
haven't received any further responses.

Can someone look at this and figure what needs to change and/or describe
what each part is doing? The code is below

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
rows were identical it placed a difference in it.


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



"Bud" wrote:

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

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.
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

This add in works quite nicely to do what you want.

http://www.cpearson.com/Zips/Compare.zip
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Bud" wrote:

Hello

Someone gave me this code to use to do a compare concatenating fields for
the compare but it doesn't correctly work. I can't figure out the code and
haven't received any further responses.

Can someone look at this and figure what needs to change and/or describe
what each part is doing? The code is below

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
rows were identical it placed a difference in it.


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



"Bud" wrote:

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

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.
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