Compare Sheets

[TiffanyG]

I prepare quarterly reports and need to pick up any new accounts but also need to report accounts that were lost. Each account has a unique account number and all info about the account is in one row. For instance:

Sheet - 1Q
Branch Act # Name Rate Code
1 321 John Doe 6

Sheet - 2Q
1 321 Joh Doe 5

I want a VB Code that will compare 1Q and 2Q pick up this row and show it is now rated a 5 instead of 6 in a new sheet named consolidation. BUT if any accounts are new 2Q I need to pick them up OR if any have dropped off and are not on the 2Q I need pick them up.

Is this possible? I use VB but can only do simple procedures.
Any help is greatly appreciated!

 

[TiffanyG]

Fazza,
I replied earlier but I didn't see it posted. If this is a duplictate, I am sorry but I am just so excited about this code!

You are the greatest! It works fantastic! Thank you so much for all your hlep.

You guys are the greatest!!

Thanks!

 

[jindon]

How about?

Sub test()
Dim a, i As Long, ii As Integer, w(), dic As Object
Set dic = CreateObject("Scripting.Dictionary")
With Sheets("Sheet1") '<- alter to suite
    a = .Range("a1").CurrentRegion.Value
End With
For i = 1 To UBound(a,1)
    If Not dic.exists(a(i,1)) Then
        ReDim w(1 To UBound(a,2) + 1)
        For ii = 1 To UBound(a,2) : w(ii) = a(i,ii) : Next
        dic.add a(i,1), w
    End If
Next
With Sheets("Sheet2")
    a = .Range("a1").CurrentRegion.Value
End With
For i = 1 To UBound(a,1)
    If Not dic.exists(a(i,1)) Then
        ReDim w(1 To UBound(a,2) + 1)
        For ii = 1 To UBound(a,2) : w(ii) = a(i,ii) : Next
        w(UBound(w)) = "New" : dic.add a(i,1) , w
    Else
        w = dic(a(i,1))
        If w(UBound(w)) <> "New" Then
            If w(3) <> a(i,3) Then
                w(3) = a(i,3) : w(UBound(w)) = "Changed"
            Else
                w(UBound(w)) = "Remain"
            End If
        End If
        dic(a(i,1)) = w
    End If
Next
y = dic.items : Set dic = Nothing : Erase a
With Sheets("Consolidate").Range("a1")
    .CurrentRegion.ClearContents
    .EntireRow.Value = Sheets("Sheet1").Rows(1).Value ' Alter sheet name if needed
    .End(xlToRight).Offset(,1).Value = "Status"
    For i = 1 To UBound(y)
        .Offset(i).Resize(,UBound(y(i))).Value = y(i)
        If IsEmpty(y(i)(UBound(y(i)))) Then
            .Offset(i,UBound(y(i)(UBound(y(i)))-1).Value = "Droped"
        End If
    Next
End With
End Sub

 

[TiffanyG]

Get an error on the line

Next
y = dic.items: Set dic = Nothing: Erase a[code]

compile error
next without for[/code]

 

[jindon]

OOps!

Missing an "End IF" after the line of

    Else
        UBound(UBound(w)) = "Remain"
    End If  '<- This one
End If

will fix previous code

 

[TiffanyG]

Get another compile error
for control variable already in use on this line

below the word 'status' at the bottom (this code is at the top also)

For i = 1 To UBound

 

[jindon]

Get another compile error
for control variable already in use on this line

below the word 'status' at the bottom (this code is at the top also)

For i = 1 To UBound

I don't understand why you get such an error.

It doesn't make sense to me. "In use"

Can you copy the previous code and try again?

 

[Fazza]

I'm glad it worked for you, Tiffany. Your thanks is appreciated. Fazza

 

[TiffanyG]

jindon,

I am getting a syntax error here. If you don't want to look at it I can use Fazza's. It just drives me crazy trying to figure it out. As I said I am learning and trying to figure out what these errors mean. Only way to learn!



.Offset(i,UBound(y(i)(UBound(y(i)))-1).Value = "Droped"

Syntax error on this line.

 

[PA HS Teacher]

I've only looked over the post a few seconds, but try adding a comma.

 .Offset(i,UBound(y(i)(UBound(y(i)))-1).Value = "Droped"

to

 .Offset(i,UBound(y(i),(UBound(y(i)))-1).Value = "Droped"

??

UBound(y(i),(UBound(y(i)))-1 )

should be the number of collumns you want to offset by.

 

[TiffanyG]

Thanks for the response.

Now I am getting the error on that same line:

run time error '9'"
subscript out of range

the code really doesn't like that line..