ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Help needed with Macro designed to compare data... (https://www.excelbanter.com/excel-programming/413372-help-needed-macro-designed-compare-data.html)

Monomeeth
Help needed with Macro designed to compare data...
 
Hello All

Some time ago I worked on a macro to compare data on one worksheet with that
in two other worksheets.

This macro was designed to check whether any records located in the
€śFiltered Data€ť worksheet already exist in either the €śOutstanding€ť or
€śComplete€ť worksheets. This was done by comparing a unique identifier (i.e.
the €śAGS Number€ť in Column A).

If the record doesnt already exist, then a copy of the record is placed in
the €śAdditions€ť worksheet.

If the record already exists in the €śOutstanding€ť worksheet, then a
comparison of the €śend date€ť field is made. If the end date is different, a
copy of the record is placed in the €śChanges€ť worksheet. If it isnt
different, then a copy is placed in the €śIgnored€ť worksheet.

Likewise, if the record already exists in the €śComplete€ť worksheet, then a
copy of it is placed in the €śIgnored€ť worksheet.

The same record cannot appear in both the €śOutstanding€ť and €śComplete€ť
worksheets.

In theory, then, by adding the total number of records located in the
€śIgnored€ť, €śAdditions€ť and €śChanges€ť worksheets, we should get the same
number of records located in the €śFiltered Data€ť worksheet.

This isnt happening and thats why I need your help! The following code was
written about six months ago with the help of other people and now Im at a
bit of a loss to find where the problem is:



Sub CompareData()

Sheets("Ignored").Select
Columns("A:Z").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Sheets("Additions").Select
Columns("A:Z").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Sheets("Changes").Select
Columns("A:Z").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select

Sh1RowCount = 1
Sh3RowCount = 1
Sh4RowCount = 1
Sh5RowCount = 1
With Sheets("Filtered Data")

Do While .Range("A" & Sh1RowCount) < ""

SearchItem = .Range("A" & Sh1RowCount)

With Sheets("Complete")
Set c = .Columns("A:A").Find(What:=SearchItem, _
LookIn:=xlValues)
End With

If c Is Nothing Then

With Sheets("Outstanding")
Set c = .Columns("A:A").Find(What:=SearchItem, _
LookIn:=xlValues)
End With

If c Is Nothing Then
.Rows(Sh1RowCount).Copy _
Destination:=Sheets("Additions").Rows(Sh3RowCount)
Sh3RowCount = Sh3RowCount + 1
Else
'compare end dates

If IsDate(.Range("K" & Sh1RowCount)) = True And
IsDate(c.Offset(0, 10)) = True Then
If CDate(.Range("K" & Sh1RowCount)) _
CDate(c.Offset(0, 10)) Then

.Rows(Sh1RowCount).Copy _
Destination:=Sheets("Changes").Rows(Sh4RowCount)
Sh4RowCount = Sh4RowCount + 1

End If
End If

End If

Else
.Rows(Sh1RowCount).Copy _
Destination:=Sheets("Ignored").Rows(Sh5RowCount)
Sh5RowCount = Sh5RowCount + 1

End If

Sh1RowCount = Sh1RowCount + 1
Loop

End With

MsgBox ("New data has been successfully compared to existing data.")

End Sub



If it helps, below are the headings associated with each column:

A AGS Number
B First Name
C Last Name
D User ID
E BSL
F Location
G Acting Grade
H Base Grade
I Reason
J Start Date
K End Date


I hope this makes sense€¦

Thanks for your help!

Joe.

--
If you can measure it, you can improve it!

joel
Help needed with Macro designed to compare data...
 
No. If the dates are not matched then nothing happens. I modified code
below to added additional items into the ignore sheet so you get the correct
count

Sub CompareData()

Sheets("Ignored").Columns("A:Z").Delete
Sheets("Additions").Columns("A:Z").Delete
Sheets("Changes").Columns("A:Z").Delete

FilteredRowCount = 1
OutstandingRowCount = 1
ChangesRowCount = 1
IgnoredRowCount = 1

With Sheets("Filtered Data")

Do While .Range("A" & FilteredRowCount) < ""

Ignore = False
SearchItem = .Range("A" & FilteredRowCount)

With Sheets("Complete")
Set c = .Columns("A:A").Find(What:=SearchItem, _
LookIn:=xlValues)
End With

If c Is Nothing Then

With Sheets("Outstanding")
Set c = .Columns("A:A").Find(What:=SearchItem, _
LookIn:=xlValues)
End With

If c Is Nothing Then
.Rows(FilteredRowCount).Copy _
Destination:=Sheets("Additions").Rows(OutstandingR owCount)
OutstandingRowCount = OutstandingRowCount + 1
Else
'compare end dates

If IsDate(.Range("K" & FilteredRowCount)) = True And _
IsDate(c.Offset(0, 10)) = True Then

If CDate(.Range("K" & FilteredRowCount)) _
CDate(c.Offset(0, 10)) Then

.Rows(FilteredRowCount).Copy _
Destination:=Sheets("Changes").Rows(ChangesRowCoun t)
ChangesRowCount = ChangesRowCount + 1

else
Ignore = True
End If
else
Ignore = true
End If
End If

Else
ignore = true
End If

if ignore = true then
.Rows(FilteredRowCount).Copy _
Destination:=Sheets("Ignored").Rows(IgnoredRowCoun t)
IgnoredRowCount = IgnoredRowCount + 1
end if
FilteredRowCount = FilteredRowCount + 1
Loop

End With

MsgBox ("New data has been successfully compared to existing data.")

End Sub


"Monomeeth" wrote:

Hello All

Some time ago I worked on a macro to compare data on one worksheet with that
in two other worksheets.

This macro was designed to check whether any records located in the
€śFiltered Data€ť worksheet already exist in either the €śOutstanding€ť or
€śComplete€ť worksheets. This was done by comparing a unique identifier (i.e.
the €śAGS Number€ť in Column A).

If the record doesnt already exist, then a copy of the record is placed in
the €śAdditions€ť worksheet.

If the record already exists in the €śOutstanding€ť worksheet, then a
comparison of the €śend date€ť field is made. If the end date is different, a
copy of the record is placed in the €śChanges€ť worksheet. If it isnt
different, then a copy is placed in the €śIgnored€ť worksheet.

Likewise, if the record already exists in the €śComplete€ť worksheet, then a
copy of it is placed in the €śIgnored€ť worksheet.

The same record cannot appear in both the €śOutstanding€ť and €śComplete€ť
worksheets.

In theory, then, by adding the total number of records located in the
€śIgnored€ť, €śAdditions€ť and €śChanges€ť worksheets, we should get the same
number of records located in the €śFiltered Data€ť worksheet.

This isnt happening and thats why I need your help! The following code was
written about six months ago with the help of other people and now Im at a
bit of a loss to find where the problem is:



Sub CompareData()

Sheets("Ignored").Select
Columns("A:Z").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Sheets("Additions").Select
Columns("A:Z").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Sheets("Changes").Select
Columns("A:Z").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select

Sh1RowCount = 1
Sh3RowCount = 1
Sh4RowCount = 1
Sh5RowCount = 1
With Sheets("Filtered Data")

Do While .Range("A" & Sh1RowCount) < ""

SearchItem = .Range("A" & Sh1RowCount)

With Sheets("Complete")
Set c = .Columns("A:A").Find(What:=SearchItem, _
LookIn:=xlValues)
End With

If c Is Nothing Then

With Sheets("Outstanding")
Set c = .Columns("A:A").Find(What:=SearchItem, _
LookIn:=xlValues)
End With

If c Is Nothing Then
.Rows(Sh1RowCount).Copy _
Destination:=Sheets("Additions").Rows(Sh3RowCount)
Sh3RowCount = Sh3RowCount + 1
Else
'compare end dates

If IsDate(.Range("K" & Sh1RowCount)) = True And
IsDate(c.Offset(0, 10)) = True Then
If CDate(.Range("K" & Sh1RowCount)) _
CDate(c.Offset(0, 10)) Then

.Rows(Sh1RowCount).Copy _
Destination:=Sheets("Changes").Rows(Sh4RowCount)
Sh4RowCount = Sh4RowCount + 1

End If
End If

End If

Else
.Rows(Sh1RowCount).Copy _
Destination:=Sheets("Ignored").Rows(Sh5RowCount)
Sh5RowCount = Sh5RowCount + 1

End If

Sh1RowCount = Sh1RowCount + 1
Loop

End With

MsgBox ("New data has been successfully compared to existing data.")

End Sub



If it helps, below are the headings associated with each column:

A AGS Number
B First Name
C Last Name
D User ID
E BSL
F Location
G Acting Grade
H Base Grade
I Reason
J Start Date
K End Date


I hope this makes sense€¦

Thanks for your help!

Joe.

--
If you can measure it, you can improve it!

Monomeeth
Help needed with Macro designed to compare data...
 
Thanks very much Joel. That's PERFECT!!

:)

--
If you can measure it, you can improve it!




"Joel" wrote:

No. If the dates are not matched then nothing happens. I modified code
below to added additional items into the ignore sheet so you get the correct
count

Sub CompareData()

Sheets("Ignored").Columns("A:Z").Delete
Sheets("Additions").Columns("A:Z").Delete
Sheets("Changes").Columns("A:Z").Delete

FilteredRowCount = 1
OutstandingRowCount = 1
ChangesRowCount = 1
IgnoredRowCount = 1

With Sheets("Filtered Data")

Do While .Range("A" & FilteredRowCount) < ""

Ignore = False
SearchItem = .Range("A" & FilteredRowCount)

With Sheets("Complete")
Set c = .Columns("A:A").Find(What:=SearchItem, _
LookIn:=xlValues)
End With

If c Is Nothing Then

With Sheets("Outstanding")
Set c = .Columns("A:A").Find(What:=SearchItem, _
LookIn:=xlValues)
End With

If c Is Nothing Then
.Rows(FilteredRowCount).Copy _
Destination:=Sheets("Additions").Rows(OutstandingR owCount)
OutstandingRowCount = OutstandingRowCount + 1
Else
'compare end dates

If IsDate(.Range("K" & FilteredRowCount)) = True And _
IsDate(c.Offset(0, 10)) = True Then

If CDate(.Range("K" & FilteredRowCount)) _
CDate(c.Offset(0, 10)) Then

.Rows(FilteredRowCount).Copy _
Destination:=Sheets("Changes").Rows(ChangesRowCoun t)
ChangesRowCount = ChangesRowCount + 1

else
Ignore = True
End If
else
Ignore = true
End If
End If

Else
ignore = true
End If

if ignore = true then
.Rows(FilteredRowCount).Copy _
Destination:=Sheets("Ignored").Rows(IgnoredRowCoun t)
IgnoredRowCount = IgnoredRowCount + 1
end if
FilteredRowCount = FilteredRowCount + 1
Loop

End With

MsgBox ("New data has been successfully compared to existing data.")

End Sub


"Monomeeth" wrote:

Hello All

Some time ago I worked on a macro to compare data on one worksheet with that
in two other worksheets.

This macro was designed to check whether any records located in the
€śFiltered Data€ť worksheet already exist in either the €śOutstanding€ť or
€śComplete€ť worksheets. This was done by comparing a unique identifier (i.e.
the €śAGS Number€ť in Column A).

If the record doesnt already exist, then a copy of the record is placed in
the €śAdditions€ť worksheet.

If the record already exists in the €śOutstanding€ť worksheet, then a
comparison of the €śend date€ť field is made. If the end date is different, a
copy of the record is placed in the €śChanges€ť worksheet. If it isnt
different, then a copy is placed in the €śIgnored€ť worksheet.

Likewise, if the record already exists in the €śComplete€ť worksheet, then a
copy of it is placed in the €śIgnored€ť worksheet.

The same record cannot appear in both the €śOutstanding€ť and €śComplete€ť
worksheets.

In theory, then, by adding the total number of records located in the
€śIgnored€ť, €śAdditions€ť and €śChanges€ť worksheets, we should get the same
number of records located in the €śFiltered Data€ť worksheet.

This isnt happening and thats why I need your help! The following code was
written about six months ago with the help of other people and now Im at a
bit of a loss to find where the problem is:



Sub CompareData()

Sheets("Ignored").Select
Columns("A:Z").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Sheets("Additions").Select
Columns("A:Z").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Sheets("Changes").Select
Columns("A:Z").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select

Sh1RowCount = 1
Sh3RowCount = 1
Sh4RowCount = 1
Sh5RowCount = 1
With Sheets("Filtered Data")

Do While .Range("A" & Sh1RowCount) < ""

SearchItem = .Range("A" & Sh1RowCount)

With Sheets("Complete")
Set c = .Columns("A:A").Find(What:=SearchItem, _
LookIn:=xlValues)
End With

If c Is Nothing Then

With Sheets("Outstanding")
Set c = .Columns("A:A").Find(What:=SearchItem, _
LookIn:=xlValues)
End With

If c Is Nothing Then
.Rows(Sh1RowCount).Copy _
Destination:=Sheets("Additions").Rows(Sh3RowCount)
Sh3RowCount = Sh3RowCount + 1
Else
'compare end dates

If IsDate(.Range("K" & Sh1RowCount)) = True And
IsDate(c.Offset(0, 10)) = True Then
If CDate(.Range("K" & Sh1RowCount)) _
CDate(c.Offset(0, 10)) Then

.Rows(Sh1RowCount).Copy _
Destination:=Sheets("Changes").Rows(Sh4RowCount)
Sh4RowCount = Sh4RowCount + 1

End If
End If

End If

Else
.Rows(Sh1RowCount).Copy _
Destination:=Sheets("Ignored").Rows(Sh5RowCount)
Sh5RowCount = Sh5RowCount + 1

End If

Sh1RowCount = Sh1RowCount + 1
Loop

End With

MsgBox ("New data has been successfully compared to existing data.")

End Sub



If it helps, below are the headings associated with each column:

A AGS Number
B First Name
C Last Name
D User ID
E BSL
F Location
G Acting Grade
H Base Grade
I Reason
J Start Date
K End Date


I hope this makes sense€¦

Thanks for your help!

Joe.

--
If you can measure it, you can improve it!


All times are GMT +1. The time now is 09:29 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com