Match a Named Range

[TiffanyG]

I have been trying to figure out how to solve an issue. Each month I have a group of customers with data, about 7 columns, and the number of rows varies each month, in the thousands. I want to compare the group from one month to the next. I have been trying to use a named range and have something in VB that compares or matches the ranges. Is this possible? I am having a terrible time getting it to work.

I need to know what has changed, remained the same or completely dropped off. I need the entire row of information carried to a new sheet named "results". The information will be in a workbook with the prior month sheet named prior month data and the new month as current month data. There are headers to row 7. Any suggestions?

 

[TiffanyG]

The code pulls the balance from the 1Q. I need the sheet that shows the changes to pull the current balance which would be the balance from the 2Q. ie, they may have made payments or added to their account.

Does this make sense. If you look at the pm I sent you can see some of the balances on the 2Q are different.

TiffanyG

 

[Fazza]

.....the accounts that have been added or removed, can the $ amounts be carried over on the consolidated sheet. The totals would come from the most recent data or the 2Q sheet.

It seems we are talking about different things here. I was reading this as applying to accounts that have been added or removed (& this has always worked OK). I think though it is not these accounts, rather the 'unchanged' accounts (same account number and same risk category). To have the account balances for these unchanged accounts from the later data only one small change is required. At the end of the first element of the SQL string, change from 1Q to 2Q: in the line sSQL=Join$(Array(SELECT 'No Change' as [Account Status], tb_1Q.*

I've re-written the whole code with new names for the data, instead of 1Q and 2Q I've changed to previous & current to better suit the use on monthly data as well as quarterly data.

Is this step OK now? Fazza

Sub Report()

Dim sConn As String
Dim sSQL As String

Dim wks As Worksheet

Worksheets("1Q").Range("A1").CurrentRegion.Name = "previous"
Worksheets("2Q").Range("A1").CurrentRegion.Name = "current"

sConn = Join$(Array("ODBC;DSN=Excel Files;DBQ=", ActiveWorkbook.FullName, ";DefaultDir=", _
    ActiveWorkbook.Path, ";DriverID=790;MaxBufferSize=2048;PageTimeout=5;"), vbNullString)

sSQL = Join$(Array( _
        "SELECT 'No Change' as [Account Status], c.*", _
        "FROM previous p, current c", _
        "WHERE p.`Account #` = c.`Account #` AND p.`Risk Grade` = c.`Risk Grade`", _
        "UNION", _
        "SELECT 'Removed' AS [Account Status], p.*", _
        "FROM {oj previous p LEFT OUTER JOIN current c ON p.`Account #` = c.`Account #`}", _
        "WHERE (c.`Account #` Is Null)", _
        "UNION", _
        "SELECT 'Added' AS [Account Status], c.*", _
        "FROM {oj current c LEFT OUTER JOIN previous p ON c.`Account #` = p.`Account #`}", _
        "WHERE (p.`Account #` Is Null)", _
        "UNION", _
        "SELECT 'Risk Change' AS [Account Status], c.*", _
        "FROM previous p, current c", _
        "WHERE p.`Account #` = c.`Account #` AND p.`Risk Grade` <> c.`Risk Grade`"), vbCr)

Set wks = Worksheets.Add

With wks
    .Name = "Report " & Format$(Now, "h.mm am/pm d mmm yy")
    With .QueryTables.Add(Connection:=sConn, Destination:=.Range("A1"), Sql:=sSQL)
        .Refresh BackgroundQuery:=False
    End With
    .Move
End With
Set wks = Nothing

End Sub

 

[TiffanyG]

I can't express how grateful I am! It is great.

You are absolutely the best!!! I really mean it.

Thanks so much for your help!

TifanyG

 

[Fazza]

You're welcome, Tiffany.

It is a good example of how 'simply' this task can be done with a little bit of SQL. Without this approach, the task would require a few formulas or VBA and a bit of copying/pasting/moving of data. The query table can in fact be set up just once - without VBA - and then just refreshed each time. So, this job can be done without formulas and without VBA. Even with thousands of records it is reasonably quick. And the very nature of the database-style functionality ensures its accuracy.