Need aMacro to perform lookup 1

[floridagunner]

I need to do a complicated lookup on very big Excel file and was hoping someone could provide either with the VBA Code or even a formula.

My problem is this:

Sheet 1 has this information:

Account # Contact Name

1234 Mike
1234 Dave
1234 John
5678 Amy
5678 Joe
3215 Mike

Sheet 2 has this information:

Account # Contact Name

1234
1234
1234
2365
5678
5678
3215

When I do a VLookup to populate the contact names I only get back one name per account number when infact a lot of account numbers have more than one contact name assigned to them.

Can any body help me with a formula or a macro. These are pretty huge files and thats the reason I am thinking of doing inserting a Macro.

Any help/suggestions will be greatly appreciated.

Thanks

 

[SkipVought]

there's more. I just need to see the LAST SQL that was run.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[PHV]

Some discrepancy here !
Your code:
sSQL = sSQL & "WHERE `Sheet1$`.`Pool Unique FA`=`Sheet2$`.`FA Split Master Lookup`"
The debug window:
WHERE `Sheet1$`.`Pool Unique FA`=`Sheet2$`.`AccountList`

How do you explain that ?

 

[SkipVought]

Have you hit the RESET button in the Debug Toolbar?

Then RUN.

Then, if there is an error, check the IMMEDIATE for the LAST SQL.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[floridagunner]

Is it possible that this is happening because that was how the original Database queery was run?

Just a guess right now I dont even remeber what the criteria was that I used.

Thanks

 

[PHV]

Do a google search for cargo cult programmer

 

[SkipVought]

It HAPPENED. It's LOG.

If you CHANGED the query and RAN it, the LOG will APPEND the code to the BOTTOM of the IMMEDIATE window.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[floridagunner]

Ok this time it ran the Macro and here is the code:

Sub GetData()


'
' Macro1 Macro
' Macro recorded 07/17/2007 by Adeel Khan
'
    Dim sSQL As String
    
    sSQL = "SELECT"
    sSQL = sSQL & "  `Sheet1$`.`Pool Unique FA`"
    sSQL = sSQL & ", `Sheet1$`.`FC Email`"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "FROM "
    sSQL = sSQL & "  `H:\My Documents\Book1`.`Sheet1$` `Sheet1$`"
    sSQL = sSQL & ", `H:\My Documents\Book1`.`Sheet2$` `Sheet2$`"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "WHERE `Sheet1$`.`Pool Unique FA`=`Sheet2$`.`FA Split Master Lookup`"
       
    Debug.Print sSQL
    
'
    With Sheets("Sheet3").QueryTables(1)
        .Connection = _
        "ODBC;DSN=Excel Files;DBQ=H:\My Documents\Book1.xls;DefaultDir=H:\My Documents;DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
    End With
End Sub

This is what appears in the immediate window:

SELECT  `Sheet1$`.`Pool Unique FA`, `Sheet1$`.`FC Email`
FROM   `H:\My Documents\Book1`.`Sheet1$` `Sheet1$`, `H:\My Documents\Book1`.`Sheet2$` `Sheet2$`
WHERE `Sheet1$`.`Pool Unique FA`=`Sheet2$`.`FA Split Master Lookup`
SELECT  `Sheet1$`.`Pool Unique FA`, `Sheet1$`.`FC Email`
FROM   `H:\My Documents\Book1`.`Sheet1$` `Sheet1$`, `H:\My Documents\Book1`.`Sheet2$` `Sheet2$`
WHERE `Sheet1$`.`Pool Unique FA`=`Sheet2$`.`FA Split Master Lookup`
SELECT  `Sheet1$`.`Pool Unique FA`, `Sheet1$`.`FC Email`
FROM   `H:\My Documents\Book1`.`Sheet1$` `Sheet1$`, `H:\My Documents\Book1`.`Sheet2$` `Sheet2$`
WHERE `Sheet1$`.`Pool Unique FA`=`Sheet2$`.`FA Split Master Lookup`
SELECT  `Sheet1$`.`Pool Unique FA`, `Sheet1$`.`FC Email`
FROM   `H:\My Documents\Book1`.`Sheet1$` `Sheet1$`, `H:\My Documents\Book1`.`Sheet2$` `Sheet2$`
WHERE `Sheet1$`.`Pool Unique FA`=`Sheet2$`.`FA Split Master Lookup`
SELECT  `Sheet1$`.`Pool Unique FA`, `Sheet1$`.`FC Email`
FROM   `H:\My Documents\Book1`.`Sheet1$` `Sheet1$`, `H:\My Documents\Book1`.`Sheet2$` `Sheet2$`
WHERE `Sheet1$`.`Pool Unique FA`=`Sheet2$`.`FA Split Master Lookup`
[auto_open] <
[SetupFunctionIDs] <
[SetupFunctionIDs] >
[PickPlatform] <
[PickPlatform] >
[VerifyOpen] <
[VerifyOpen] > 1
[RegisterFunctionIDs] <
[RegisterFunctionIDs] >
[auto_open] >
SELECT  `Sheet1$`.`Pool Unique FA`, `Sheet1$`.`FC Email`
FROM   `H:\My Documents\Book1`.`Sheet1$` `Sheet1$`, `H:\My Documents\Book1`.`Sheet2$` `Sheet2$`
WHERE `Sheet1$`.`Pool Unique FA`=`Sheet2$`.`AccountList`

 

[floridagunner]

What do you mean by that: "Its Log

 

[SkipVought]

Its a LOG of what you put in Debug.Print.

What error are you getting when you run?

Did you try Data > Refresh in the QueryTable? If so, what happened?

Please post

Sheet1 headings and one row of data and

Sheet2 heading and one row of data.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[floridagunner]

When I tried to refresh the data

An error message came up saying

[Microsoft][ODBC Excel Driver] Missing),],or item in queer expression`sheet 1$`.Pool Unique FA`='sheet2$`,`AccountList`

 

[PHV]

Do you even know how many QueryTables you have now in your workbook ?

 

[SkipVought]

Then the CODE is not running the query.

Edit the query, get into the QBE Editor. delete Sheet2 table and then re add Sheet2 and LINK the FA fields and RUN the query.

I'm not understanding why the VBA CODE is not changing the query code. You are missing something.

How are you running the VBA code, anyhow?



Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[floridagunner]

Sheet 1 Headings:

A B C
1 (Pool Unique FA) (FC Name) (FC Email)
2 1234567 Smith, John jsmith@hotmail.com


Sheet 2 Headings

A B
FA Split Master Lookup
6582461


Did you read my post from before.

That it actually ran the Macro this time. The only thing is that the data that its bring back in sheet 3 is not what I wanted.

Thanks

 

[floridagunner]

Should I just go ahead and delete Sheet 3 and Start the Database queery again?

This is what it did in Sheet 3 by the way:

Pool Unique FA FC Email

It seems to have deleted the Column (FA Split Master Lookup) and bought back data for the first 2 columns

 

[SkipVought]

It JOINED the values in sheet2 FA Split Master Lookup
to Sheet1 Pool Unique FA and where they MATCH it returns Pool Unique FA and FC Email.

What did you get that you did not want?

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[jakecam]

Sub lookForRecords()
    
    Application.ScreenUpdating = False
    
    Dim i As Integer, j As Integer
    Dime sourceSheet As String, lookUpSheet As String
    
    sourceSheet = "Sheet1" 'change Sheet1 accordingly
    lookUpSheet = "Sheet2" 'change sheet2 accordingly

    accountCol = "A" 'Where your account #
    criteriaCol = "A" 'where your account #-lookUpSheet
    
    sourceSheetRecords = Application.CountA(Sheets(sourceSheet).Range(accountCol & ":" & accountCol))
    lookUpRecords = Application.CountA(Sheets(lookUpSheet).Range(criteriaCol & ":" & criteriaCol))
    
    For i = 2 To sourceSheetRecords 'row 1 has headers
        
        accountValue = Sheets(sourceSheet).Range(accountCol & i).Value
        contactName = Sheets(sourceSheet).Range(accountCol & i).Offset(0, 1).Value
        contactEmail = Sheets(sourceSheet).Range(accountCol & i).Offset(0, 2).Value
        
        For j = 2 To lookUpRecords 'row 1 has headers
        
            lookUpValue = Sheets(lookUpSheet).Range(criteriaCol & j).Value

            lookUpContactName = Sheets(lookUpSheet).Range(criteriaCol & j).Offset(0, 1).Value

            lookUpContactEmail = Sheets(lookUpSheet).Range(criteriaCol & j).Offset(0, 2).Value
            
            
            If accountValue = lookUpValue And lookUpContactName = "" Then

                Sheets(lookUpSheet).Range(criteriaCol & j).Offset(0, 1).Value = contactName

                Sheets(lookUpSheet).Range(criteriaCol & j).Offset(0, 2).Value = contactEmail

                Exit For

            End If
            
        Next j
        
    Next i
    
End Sub