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]

Hi Tiffany,

Maybe something like below, from when you posted something like this previously?

Tiffany,

I've modified the SQL in the query so that now it will return all columns. You can have as many columns as you like and any names and in any order - so long as both lists have matching headers and you have headers "Account #" and "Risk Grade".

HTH, 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 = "ODBC;DSN=Excel Files;DBQ=" & _ 
            ActiveWorkbook.FullName & _ 
            ";DefaultDir=" & ActiveWorkbook.Path & _ 
            ";DriverID=790;MaxBufferSize=2048;PageTimeout=5;" 
    
    sSQL = "SELECT 'Removed 2Q' AS [Account Change], tbl_1Q.*" & vbCr & _ 
            "FROM {oj tbl_1Q tbl_1Q LEFT OUTER JOIN tbl_2Q tbl_2Q ON tbl_1Q.`Account #` = tbl_2Q.`Account #`}" & vbCr & _ 
            "WHERE (tbl_2Q.`Account #` Is Null)" & vbCr & _ 
            "UNION" & vbCr & _ 
            "SELECT 'Added 2Q' AS [Account Change], tbl_2Q.*" & vbCr & _ 
            "FROM {oj tbl_2Q tbl_2Q LEFT OUTER JOIN tbl_1Q tbl_1Q ON tbl_2Q.`Account #` = tbl_1Q.`Account #`}" & vbCr & _ 
            "WHERE (tbl_1Q.`Account #` Is Null)" & vbCr & _ 
            "UNION" & vbCr & _ 
            "SELECT 'Risk Change 2Q' AS [Account Change], tbl_2Q.*" & vbCr & _ 
            "FROM tbl_1Q tbl_1Q, tbl_2Q tbl_2Q" & vbCr & _ 
            "WHERE tbl_2Q.`Account #` = tbl_1Q.`Account #` AND tbl_2Q.`Risk Grade` <> tbl_1Q.`Risk Grade`" 
    
    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

from http://www.mrexcel.com/board2/viewtopic.php?t=248415

 

[TiffanyG]

Thanks for the reply.

I have been using this code but sometimes it stops at this line of the code.

.Refresh BackgroundQuery:=False

Then, if I close out of the file and try again it works. Any suggestions as to why? When it works it's great, but I have to go in and out of the file to get it to work.

TiffanyG

 

[Fazza]

Good question, Tiffany. I don't know. Maybe you would get an error (or stoppage?) if the file had never been saved. Other than that, I can't think of a reason.

The code is one way to implement a query table - it creates a new query on a new worksheet and then makes it a new workbook. Normally one might simply create the query table once (permanently within the original file) and for all subsequent reports, just refresh the table. So, the implementation in the code is likely not how I would use a query table, but I am familiar with them. I have found it easiest for others to implement (for example when posting to this forum) by code like I posted 'cause then they don't have to deal with other idiosyncracies of query tables - because people are not familiar with query tables. Hope this is making sense! If you learn about query tables you might be able to implement the solution better.

regards, Fazza

 

[TiffanyG]

Thanks for your help! I'll keep trying and I'll figure it out!

Tiffany

 

[Fazza]

Another thought. Every column must have a header.

Do you receive an error message?

 

[TiffanyG]

Fazza,
The code has been working. Now I also need to carry over any that were on the previous quarter and also on the new quarter with a line that says 'no change'. This would happen if the risk grade is the same. The code now indicates, added, removed or change. Could you please help me add the additional code.

I love this code but I am at a total loss when I need a change. It is way over my level of VB.
I would be extremely grateful!

TiffanyG

 

[Fazza]

How about this, then? Untested.

Note near the top of the code references to worksheets 1Q and 2Q. Modify to suit. This step could be coded smarter but first lets see if it works. (I'm thinking you could input the sheet names, or one of them anyway, and the VBA could then workout which sheets to refer to.)

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`", _
            "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

 

[Fazza]

Tiffany,

Re-reading your original post, I see that this was to for monthly reporting with worksheets "prior" and "current", and with headers in row 7.

What I've posted is from the earlier thread that I referenced in my first reply, and hence it refers to worksheets "1Q" and "2Q" for quarterly reporting, and, is based on the data starting in row 1.

Can you edit what I've just posted to suit whatever is required? Fazza

 

[TiffanyG]

Thank you so much!
I will try it at work tomorrow.
You are the best!!

I actually use this for a quarterly report and a monthly report. I have been changing my sheet names to correspond with the code and just changing them back when I am finished. However, the information does start in row 7. To compensate for this I have also been cutting the header and pasting it back when I am done. To change it to start in row 7 do I just change any reference of A1 to A7?


TiffanyG