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
>
>
Similar Threads
