Compare 2 sheets, then copy/paste results if Different Values in C

[ryguy7272]

I posted this question a few days back. The post has been pushed down the
list a bit, and probably overlooked by now. I am still struggling with the
same issue, and am hoping to find a solution soon.

As I stated in the other post, I want to Compare two sheets, then copy/paste
results from an entire row on a sheet named ‘Analysis of Interest Current’,
if the values are different in Column A. Basically, I am creating a new
sheet, named ‘Analysis-Sheet’ and I am trying to compare the values in Column
A of ‘Analysis-Sheet’ (these values came from ‘Analysis of Interest Prior’)
with the values in Column A of ‘Analysis of Interest Current’. Then, I want
to copy and past the entire row from ‘Analysis of Interest Current’ if the
value in Column A is not in Column A of ‘Analysis-Sheet’. The purpose of
this, is that some new accounts are added to ‘Analysis of Interest Current’
from time to time, and I want to be able to pick up these new accounts in my
analysis. For instance, I have account number 7250-0000 in both
‘Analysis-Sheet’ (this data comes from 'Analysis of Interest Prior') and
'Analysis of Interest Current', also, I have account number 7252-0000 in both
'Analysis of Interest Prior' and 'Analysis of Interest Current'. However, I
don’t have account number 7254-0000 and I don’t have account number
7255-0000; both only appear in 'Analysis of Interest Current', so I'd like to
take copy all contents from both rows from 'Analysis of Interest Current' and
place it into the appropriate place in ‘Analysis-Sheet’, in ascending order.
How can I do this?

This is what I have so far:
Sub CompareSheets1()

'Delete the sheet "Analysis" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("Analysis-Sheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True


Dim ws1 As Worksheet, ws2 As Worksheet
Dim lngRow As Long, lngFRow As Long

Set ws1 = Worksheets("Analysis of Interest Prior")
Set ws2 = Worksheets("Analysis of Interest Current")

ws1.Copy After:=ws2
Set ws1 = ActiveSheet
ActiveSheet.Name = "Analysis-Sheet"

ws1.Columns("F:J").Clear
ws2.Range("E1:E9").Copy ws1.Range("F1:F9")

Dim wks1 As Worksheet, wks2 As Worksheet
Set wks1 = Sheets("Analysis of Interest Current")
Set wks2 = Sheets("Analysis-Sheet")

For i = iLastRow To 2 Step -1
For Each j In wks1.Range("A2:A170")
If IsError(Application.Match(Cells(i, "A").Value,
wks2.Range("A2:A170"), 0)) Then
wks1.Cells(i, "A").EntireRow.Insert
wks2.Range.EntireRow.Copy
ActiveCell.Value = wks2.Range("A2:A170").Value
End If
Next j
Next i
End Sub

However, I don't know how to tell Excel to copy from the appropriate row in
wks2.
I know this is not right:
wks2.Range.EntireRow.Copy

There's no reference for the row! How do I tell Excel which row to copy?
Also, the loop is not working right. I think I'm close to a solution. What
do I need to do to make this work?

Thanks,
Ryan--

 

[Otto Moehrbach]

Ryan
I redid your code and added a sort command. I assumed that the Analysis
sheet exists and is blank. Modify this as needed. I also assumed that your
data is all in Columns A:J. Post back if this doesn't work for you. HTH
Otto

Sub CompareSheets1()
Dim rColAC As Range 'Column A of the Current sheet
Dim rColAP As Range 'Column A of the Prior sheet
Dim Dest As Range 'First empty cell in Column A of the Analysis sheet
Dim i As Range, RngToSort As Range
With Sheets("Analysis of Interest Current")
Set rColAC = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
End With
With Sheets("Analysis of Interest Prior")
Set rColAP = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
.Cells.Copy Sheets("Analysis-Sheet").Range("A1") 'Copy all in Prior
to Analysis
End With
With Sheets("Analysis-Sheet")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
For Each i In rColAC
If rColAP.Find(What:=i.Value, LookAt:=xlWhole) Is Nothing Then
i.EntireRow.Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
With Sheets("Analysis-Sheet")
Set RngToSort = .Range("A1", .Range("A" &
Rows.Count).End(xlUp)).Resize(, 10)
RngToSort.Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
End Sub

 

[ryguy7272]

Thanks for the code Otto! Sorry for this delayed response, but I’m in China
now, with intermittent Internet connectivity, and when I finally got a look
at this, it took me a little while to figure out what was going on here.

This code would work if I strip out a few things in my two sheets.
Basically, I am doing two sums in both sheets. This causes a problem such as
the following: I don't have account No. 7200-0147 or Account No. 7200-0148 in
'Analysis of Interest Prior', but I do have both accounts in 'Analysis of
Interest Current'. When the code copies the data to the ‘Analysis-Sheet’, it
copies everything from 'Analysis of Interest Prior' and then copies data from
'Analysis of Interest Current'. Finally, then the loop runs and anything
that’s in the 'Analysis of Interest Current', but not in the
‘Analysis-Sheet’, gets pulled into the ‘Analysis-Sheet’. This is what I
want, but I wanted to insert a row where there is a discrepancy because of
the two sums in both sheets. Now, the code pulls in the new account no. but
puts all of them at the bottom of the second group of accounts (the loop is
doing this). Basically, I am missing some accounts from the first group and
the second group has too many accounts (because it has some accounts that
belong in the first group).

Is there an easy way to add some EntireRow.Insert logic in the code? This
is what makes the thing tricky. I’ve been working on this, a little each day
for a couple of weeks now, and I’m still without a solution. Do you think
this is an easy fix Otto? Do you want me to send the workbook to you?


Thanks for everything so far!
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Ryan
I redid your code and added a sort command. I assumed that the Analysis
sheet exists and is blank. Modify this as needed. I also assumed that your
data is all in Columns A:J. Post back if this doesn't work for you. HTH
Otto

Sub CompareSheets1()
Dim rColAC As Range 'Column A of the Current sheet
Dim rColAP As Range 'Column A of the Prior sheet
Dim Dest As Range 'First empty cell in Column A of the Analysis sheet
Dim i As Range, RngToSort As Range
With Sheets("Analysis of Interest Current")
Set rColAC = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
End With
With Sheets("Analysis of Interest Prior")
Set rColAP = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
.Cells.Copy Sheets("Analysis-Sheet").Range("A1") 'Copy all in Prior
to Analysis
End With
With Sheets("Analysis-Sheet")
Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1)
End With
For Each i In rColAC
If rColAP.Find(What:=i.Value, LookAt:=xlWhole) Is Nothing Then
i.EntireRow.Copy Dest
Set Dest = Dest.Offset(1)
End If
Next i
With Sheets("Analysis-Sheet")
Set RngToSort = .Range("A1", .Range("A" &
Rows.Count).End(xlUp)).Resize(, 10)
RngToSort.Sort Key1:=Range("A2"), Order1:=xlAscending,
Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
End Sub

 

[Otto Moehrbach]

Ryan
Yes, send me your workbook. Include a step-by-step example of what you
want to happen. As I wrote it, the code puts everything it copies at the
bottom of what's in the Analysis sheet, then sorts it all by Column A. Is
that not what you want? My email is (e-mail address removed). Remove
the "extra" from this address. Otto

Thanks for the code Otto! Sorry for this delayed response, but I'm in
China
now, with intermittent Internet connectivity, and when I finally got a
look
at this, it took me a little while to figure out what was going on here.

This code would work if I strip out a few things in my two sheets.
Basically, I am doing two sums in both sheets. This causes a problem such
as
the following: I don't have account No. 7200-0147 or Account No. 7200-0148
in
'Analysis of Interest Prior', but I do have both accounts in 'Analysis of
Interest Current'. When the code copies the data to the 'Analysis-Sheet',
it
copies everything from 'Analysis of Interest Prior' and then copies data
from
'Analysis of Interest Current'. Finally, then the loop runs and anything
that's in the 'Analysis of Interest Current', but not in the
'Analysis-Sheet', gets pulled into the 'Analysis-Sheet'. This is what I
want, but I wanted to insert a row where there is a discrepancy because of
the two sums in both sheets. Now, the code pulls in the new account no.
but
puts all of them at the bottom of the second group of accounts (the loop
is
doing this). Basically, I am missing some accounts from the first group
and
the second group has too many accounts (because it has some accounts that
belong in the first group).

Is there an easy way to add some EntireRow.Insert logic in the code? This
is what makes the thing tricky. I've been working on this, a little each
day
for a couple of weeks now, and I'm still without a solution. Do you think
this is an easy fix Otto? Do you want me to send the workbook to you?


Thanks for everything so far!
Ryan---