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?

 

[Fazza]

To change it to start in row 7 do I just change any reference of A1 to A7?

It depends....

The A1 cell is referenced to pick up the 'current region' around the cell. Just like hitting CTRL-SHIFT-* on the keyboard. This should be the entire source data.

If doing the same thing from cell A7 picks up only the block of data, and no extra rows, then yes.

If however there is, for example, something in row 6 so that extra unwanted cells are included, this will not be correct. (The first row has to be the headers.)

 

[TiffanyG]

Could I possibly email you a sample file I have been trying to run the code on? I am getting an error on the line below. The original code is working.
I don't know what to do???

[cocode.Refresh BackgroundQuery:=Falsede]

Thanks
TiffanyG

[/code]

 

[Fazza]

If you need to email, send a pm.

The original code is working. A sample file is not. Note, the sample file has to be saved before running the query.

Do you have headers on all fields and header names matching those referred to from the SQL? It should be pretty straightforward if the file has been saved, the fields all have headers, the current region property of cell A1 picks up all the source data, the SQL is correct.

Can you post ALL the code that you are using (the one line posted is not helpful by itself) and confirm details of anything relevant - worksheet names, field names, etc.

 

[TiffanyG]

Just sent it by pm.

Thanks
TiffanyG

 

[Fazza]

Tiffany,

There was an error in the SQL - so I now understand about your sample file & the reason for the error. My apologies: I omitted the "UNION" between the newly added SQL for the unchanged items and the SQL that was already there. I had posted it as untested and, unfortunately, with an error. It is only a matter of adding one extra line into the SQL - "UNION". I will repost below the full corrected & tested code. Regards, Fazza

Sub Report()

Dim sConn As String
Dim sSQL As String

Dim wks As Worksheet

Worksheets("1Q").Range("A1").CurrentRegion.Name = "tbl_1Q"
Worksheets("2Q").Range("A1").CurrentRegion.Name = "tbl_2Q"

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], tbl_1Q.*", _
        "FROM tbl_1Q tbl_1Q, tbl_2Q tbl_2Q", _
        "WHERE tbl_1Q.`Account #` = tbl_2Q.`Account #` AND tbl_1Q.`Risk Grade` = tbl_2Q.`Risk Grade`", _
        "UNION", _
        "SELECT 'Removed' AS [Account Status], tbl_1Q.*", _
        "FROM {oj tbl_1Q tbl_1Q LEFT OUTER JOIN tbl_2Q tbl_2Q ON tbl_1Q.`Account #` = tbl_2Q.`Account #`}", _
        "WHERE (tbl_2Q.`Account #` Is Null)", _
        "UNION", _
        "SELECT 'Added' AS [Account Status], tbl_2Q.*", _
        "FROM {oj tbl_2Q tbl_2Q LEFT OUTER JOIN tbl_1Q tbl_1Q ON tbl_2Q.`Account #` = tbl_1Q.`Account #`}", _
        "WHERE (tbl_1Q.`Account #` Is Null)", _
        "UNION", _
        "SELECT 'Risk Change' AS [Account Status], tbl_2Q.*", _
        "FROM tbl_1Q tbl_1Q, tbl_2Q tbl_2Q", _
        "WHERE tbl_2Q.`Account #` = tbl_1Q.`Account #` AND tbl_2Q.`Risk Grade` <> tbl_1Q.`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]

Fazza,

Thank you so very much. It is working great! You have saved me so much time! You are the best!

TiffanyG

 

[Fazza]

 

[TiffanyG]

Thanks, I needed that!

 

[TiffanyG]

I hate to ask this, but I was asked at work today if it would be possible to get the code to do one more thing.....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.

We need to have a balance of what was lost and what was added. Is this possible?

Hope you understand what I mean.
TiffanyG

 

[Fazza]

I can only go one step at a time, Tiffany.

.....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.

I thought this was already done? So, I don't understand. Can you refer to some sample data, please?