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!

 

[Fazza]

Welcome, Tiffany, and thank you for a clear description.

This sounds like it is suited to query tables - database like functionality. It can readily handle your three cases. In the first, it would return records that match on all fields except rate, which must differ. The other cases are of accounts being in one list and not the other. This is a sophisticated approach. It is great if you have a large number of records. The coding of this would be not simple, so I'll suggest an easier approach.

Another way would be do add another two fields to your data. One field concatenating the first three fields - such as =A1&B1&C1 - and the other determining if that field exists in the OTHER data set. Such as,
=ISNUMBER(MATCH(concatenated field this table,concatenated field other table,0))

This will return either TRUE or FALSE if the account is in the other data set.

This TRUE/FALSE field identifies the second and third questions. Autofiltering would be one way to grab just the records you want.

To do the comparison between the rate codes can be done with another field. For example a MATCH/INDEX (similar to VLOOKUP which you could use alternatively) to return the rate from the other table for comparison with the table you are in. Either they will be the same, or the rate codes will differ.

(Maybe less simple, you could also put all the data into one data set however multiplying all the 1Q rate codes by -1. You could have a field Q being either 1Q or 2Q. Then a pivot table on this would show the sum by rate code, and where 0 the rate codes are unchanged, and where not zero you can identify the ones that have changed.)

If you really need a VBA solution, this second approach (without the pivot table) might be easiest. If you get stuck on any code steps, please post again with details of your code.

HTH, Fazza

 

[TiffanyG]

Thanks for your help. I have a file I could attach to help explain, if I can figure out how......This is making me loose sleep.

I don't understand concatenating. I will have to look it up. I hate to admit it but I usually need to look at an example. If I know the end product I can usually understand the code but it's hard for me to begin to write it. I haven't had any formal training, just trial and error.

Sheet 1Q
Account # Name Risk Grade Balance
1280603881 John Smith 5 $706,140.39
1004139781 jane doe 5 $690,265.00
1004168381 john Doe 5 $689,847.57
1004110081 sam 6 $689,324.80
1004100182 sue 5 $674,173.07
1001212680 sis 6 $665,674.01
1004129880 joe 5 $581,149.89
1002372085 paul 6 $727,842.49
5307929980 sue 5 $919,038.61


Sheet 2Q
Account # Name Risk Grade Balance
1280603881 John Smith 5 $300,000.00
1004139781 jane doe 5 $650,000.00
1004168381 john Doe 5 $230,000.00
1004110081 sam 4 $500,000.00
1004100182 sue 3 $100,000.00
1001212680 sis 6 $6,900,000.00
1004129880 joe 5 $540,000.00
1002372085 paul 6 $32,000.00

Sheet Recap
Accounts removed 2 quarter
Accounts Added 2Q
Accounts with risk change 2Q

I need the entire row copied to the sheet recap.
Couldn't figure out how to attach the file so I typed the info.

Could you explain how to get started and I'll see if I can manage from there?? Thanks!

 

[Fazza]

Concatenating I tried to explain above. I haven't had any training either, BTW. Just picked things up through use. You'll be right.

for your data
a2=account number
b2=name
c2=risk
in another cell enter the formula

=A2&B2&C2

This creates a key that in one cell combines A2, B2 & C2.

BTW, instead of the ampersand construction, there is a function CONCATENATE that you can use to do the same thing.

=CONCATENATE(A2,B2,C2)

Does that help enough?

Meanwhile, I'll see if I can write some VBA to do what you want.

 

[TiffanyG]

thank you so much. I am trying to write the VB code. If you could help I would really be grateful!

 

[jindon]

Hi
try
Sheet1 for 1stQ
Sheet2 for 2ndQ
result in Consolidate sheet

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.Resize(,4).Value
End With
For i = 1 To UBound(a,1)
    If Not dic.exists(a(i,1)) Then
        ReDim w(1 To 5)
        For ii = 1 To 4 : w(ii) = a(i,ii) : Next
        dic.add a(i,1), w
    End If
Next
With Sheets("Sheet2")
    a = .Range("a1").CurrentRegion.Resize(,4).Value
End With
For i = 1 To UBound(a,1)
    If Not dic.exists(a(i,1)) Then
        ReDim w(1 To 5)
        For ii = 1 To 4 : w(ii) = a(i,ii) : Next
        w(5) = "New" : dic.add a(i,1) , w
    Else
        w = dic(a(i,1))
        If w(5) <> "New" Then
            If w(3) <> a(i,3) Then
                w(3) = a(i,3) : w(5) = "Changed"
            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
    .Resize(,5) = [{"Acc","Name","Risk","Amnt","Status"}]
    For i = 1 To UBound(y)
        .Offset(i).Resize(,5).Value = y(i)
        If IsEmpty(y(i)(5)) Then .Offset(i,4).Value = "Dropped"
    Next
End With
End Sub

 

[Fazza]

great, mate

 

[jindon]

Fazza,

I really like the query and your solutions, too.

 

[Fazza]

See if I can code a query table approach

 

[TiffanyG]

jindon,
YOU ARE THE GREATEST!!!! and yes I was shouting!!! It works great.

But help me understand,
.Resize(,5) = [{"Acc","Name","Risk","Amnt","Status"}]
if I add fields to the row that I need to pick up in the future is this where I tell the code to pick up more fields. I am afraid more and more data will be added.

I am just amazed! I have worked on this for days! Thanks so much for your help!