Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need aMacro to perform lookup 1

Status
Not open for further replies.
floridagunner

floridagunner

MIS
Jul 13, 2007
47
US
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
 
Sort by date Sort by votes




Hey, I just spent a few seconds glancing at your SQL.

You're joining 2 tables from sheet1 and sheet2.

How are you joining them? ie what key value is common to both?

What data is in sheet1 and what data is in sheet2?

This has problems!

Skip,
[sub]
[glasses] 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] [tongue][/sub]
 
Upvote 0 Downvote
When you ask:

"What key value is common to both"?

Do you mean what criteria I used?
 
Upvote 0 Downvote



OK, I reviewed the post

Sheet1 is SOURCE

Sheet2 WAS your lookup results. This could be where you put your AccountList.

Sheet3 is where your query is (you have to change the VBA CODE from Sheet4 to Sheet3

So then the code becomes...
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 & ", `Sheet2$`.`FA Split Master Lookup`"
    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[b]
    sSQL = sSQL & "WHERE (`Sheet1$`.`Pool Unique FA`=`Sheet2$`.`AccountList`"
[/b]
       
'[b]
    With Sheets("Sheet3").QueryTables(1)[/b]
        .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


Skip,
[sub]
[glasses] 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] [tongue][/sub]
 
Upvote 0 Downvote


What sheet did YOU insert the querytable on?

also the SQL is still incorrect...
Code: 
    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$`.`AccountList`"
assuming that [Pool Unique FA] is the column containing the Account Number.

Skip,
[sub]
[glasses] 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] [tongue][/sub]
 
Upvote 0 Downvote
Ok I ran this updated code in Sheet 4 and I got an error message saying SQL syntax error
When I pressed Debug the following code was highlighted:

Code: 
.Refresh BackgroundQuery:=False
 
Upvote 0 Downvote
Replace this:
sSQL = sSQL & "WHERE (`Sheet1$`.`Pool Unique FA`=`Sheet2$`.`AccountList`"
with this:
sSQL = sSQL & "WHERE `Sheet1$`.`Pool Unique FA`=`Sheet2$`.`AccountList`"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote



Tha means that your QUERY SQL code has an error.

Select in your query table, Data > Refresh Data and see what error is reported.

Skip,
[sub]
[glasses] 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] [tongue][/sub]
 
Upvote 0 Downvote
Yes

The Column in Sheet 1 that contains the account number is Pool Unique FA.

 
Upvote 0 Downvote



PHV has spotted that there is a stray open parentheses in your SQL. My bad!

Skip,
[sub]
[glasses] 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] [tongue][/sub]
 
Upvote 0 Downvote
An error message came up saying

[Microsoft][ODBC Excel Driver] Missing),],oritem in queer expression`9sheet 1$`.Pool Unique FA`='sheet2$`,`AccountList`',
 
Upvote 0 Downvote
\


try this
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$`.`AccountList`"
    [b]   
    Debug.Print sSQL[/b]
    
'
    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
then if you get an sql error, do View > Immediate and COPY the SQL string and paste back the code.

Skip,
[sub]
[glasses] 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] [tongue][/sub]
 
Upvote 0 Downvote
[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`
 
Upvote 0 Downvote



On Sheet2 in A1, do you have the heading, AccountList, followed by the account numbers in A2 and following?

Skip,
[sub]
[glasses] 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] [tongue][/sub]
 
Upvote 0 Downvote




Thsn
Code: 
WHERE `Sheet1$`.`Pool Unique FA`=`Sheet2$`.`FA Split Master Lookup`

Skip,
[sub]
[glasses] 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] [tongue][/sub]
 
Upvote 0 Downvote

Code: 
[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`
 
Upvote 0 Downvote




You did not change the WHERE clause.

Sheet2 heading is not AccountList.

Skip,
[sub]
[glasses] 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] [tongue][/sub]
 
Upvote 0 Downvote
This is the Code that I have:

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 the immediate window shows after I try to run the code:

Code: 
[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`
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

K
  • Locked
  • Question
How to resolve define worksheets array
Replies
1
Views
304
combo
combo
rf92d
  • Locked
  • Question
Hi referring to thread707-107529
Replies
0
Views
197
rf92d
rf92d
rf92d
  • Locked
  • Question
hi referring to thread707-103495
Replies
9
Views
285
Andrzejek
Andrzejek
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
548
remeng
remeng
xwb
  • Locked
  • Helpful tip
Performing VBA operations in other languages
Replies
1
Views
223
strongm
strongm

Part and Inventory Search

Sponsor

Back
Top