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



Fire away

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
Hi Skip sorry for taking so long but I got called away by my boss.

Okhere is the recorded code that came up.

I did exactly as you said


[Sub Macro1()
'
' Macro1 Macro
' Macro recorded 07/17/2007 by Adeel Khan
'

'
With Selection.QueryTable
.Connection = _
"ODBC;DSN=Excel Files;DBQ=H:\My Documents\Book1.xls;DefaultDir=H:\My Documents;DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
.CommandText = Array( _
"SELECT `Sheet1$`.`Pool Unique FA`, `Sheet1$`.`FC Email`, `Sheet2$`.`FA Split Master Lookup`" & Chr(13) & "" & Chr(10) & "FROM `H:\My Documents\Book1`.`Sheet1$` `Sheet1$`, `H:\My Documents\Book1`.`Sheet2$` `Sheet2$`" & Chr(13) & "" & Chr(10) & "WHERE (`Shee" _
, "t2$`.`FA Split Master Lookup` In (1041009))")
.Refresh BackgroundQuery:=False
End With
End Sub]

I guess I need to make an inlist like you said

Thanks
 
Upvote 0 Downvote


Code: 
Sub GetData()

End Sub
'
' 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]
'assuming that you have a named range name AccountList with each account number listed once...
'[/b]
    sSQL = sSQL & "WHERE (`Sheet2$`.`FA Split Master Lookup` In (" & MakeList([AccountList]) & "))"
       
'
    With Sheets("[b]YourSheetName[/b]").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
Function MakeList(rng As Range)
'SkipVought 2005 Jan 10
'this function returns strings within single quotes, delimited by comma
'for use in an SQL IN () statement
    Dim r As Range
    Const TK = "'"
    Const CM = ","
    For Each r In rng
        With r
            MakeList = MakeList & TK & .Value & TK & CM
        End With
    Next
    MakeList = Left(MakeList, Len(MakeList) - 1)
End Function

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



FYI on your AccountNumbers -- faq68-6659

If you indeed have them ALL stored as numbers (not a good thing), remove the TK s

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
Hi Skip, thanks for posting back. I have a couple of questions.

1)The code that you wrote above I am assuming that I should REPLACE the code generated by the Macro Recording and input the one you wrote with the only change being the actual sheet names. Is that Correct?

2) I am a little confused by the information in the FAQ, about text and numbers. I actually do have some account numbers that are Alpha-Numeric. Most of them are numeric though.

It seems that it would screw up the the data if I changed the Alpha-Numeric account numbers back to text.

By the way how did you know I have Alpha-Numeric account numbers?

Really Appreciate all the help you are giving me on this Skip.

Thanks
 
Upvote 0 Downvote




1. replace the code. As a matter of practice, whenever I record a macro, I ALWAYS customize it in order to make it useable.

2. "...Most of them are numeric though..." Mixing NUMBERS and TEXT in the same column will be DISASTEROUS! You must convert all your numbers to TEXT as that, in reality, is what an IDENTIFIER data type ought to be.

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, You are saying I should convert account numbers back to text. Should I do that in both sheets?

Remember I told you yesterday that I used a COUNTIF formula to find the number of occurences each account number in sheet 2. Then I used the macro you gave me to insert rows to accomadate the new information.

I am worried that when I repeat this process for next month it might screw up the data.

Any thoughts?
 
Upvote 0 Downvote




This approch does not require COUNTIF or any other lookup.

When you REFRESH the query, it returns a fresh resultset to the sheet. If Account xyz had 4 occurrences the last time it ran, and this time there are 7, it will return 7.

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
Hi Skip sorry for the delay in posting back.

I am trying this code but there seem to be some things I am still doing wrong.

[/'assuming that you have a named range name AccountList with each account number listed once...]

I do not have a named range is this something I need to do?

Sub GetData()

End Sub
'
' 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
'
'assuming that you have a named range name AccountList with each account number listed once...
'
sSQL = sSQL & "WHERE (`Sheet2$`.`FA Split Master Lookup` In (" & MakeList([AccountList]) & "))"

'
With Sheets("YourSheetName").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
Function MakeList(rng As Range)
'SkipVought 2005 Jan 10
'this function returns strings within single quotes, delimited by comma
'for use in an SQL IN () statement
Dim r As Range
Const TK = "'"
Const CM = ","
For Each r In rng
With r
MakeList = MakeList & TK & .Value & TK & CM
End With
Next
MakeList = Left(MakeList, Len(MakeList) - 1)
End Function

It is returning the statement
Code: 
Only comments may appear after End Sub End Function or end Property

Thanks
 
Upvote 0 Downvote
By the way one thing that I also cant figure out is that I name the range of account numbers do I name the ones in Sheet 1 or Sheet 2
 
Upvote 0 Downvote


You don't absolutely need to use Named Ranges, but it make life alot easier.

My fault. I gave you some extraneous stuff...
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
'
'assuming that you have a named range name AccountList with each account number listed once...
'
    sSQL = sSQL & "WHERE (`Sheet2$`.`FA Split Master Lookup` In (" & MakeList([AccountList]) & "))"
       
'
    With Sheets("YourSheetName").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
Function MakeList(rng As Range)
'SkipVought 2005 Jan 10
'this function returns strings within single quotes, delimited by comma
'for use in an SQL IN () statement
    Dim r As Range
    Const TK = "'"
    Const CM = ","
    For Each r In rng
        With r
            MakeList = MakeList & TK & .Value & TK & CM
        End With
    Next
    MakeList = Left(MakeList, Len(MakeList) - 1)
End Function


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
So which range of Account numbers do I name as Accountlist.

Sheet 1 or Sheet 2

Thanks
Adeel
 
Upvote 0 Downvote
Is this also part of the code:

Code: 
'assuming that you have a named range name AccountList with each account number listed once...
 
Upvote 0 Downvote



Comments lines begin with a TIC
Code: 
'this is a comment
Your source table has multiple occurrences of account nbr for the whole world.

Your boss wants to a report for only FIVE account nbrs

You make a LIST of those FIVE account nbrs. Thats your AccountList. You can NAME the range or use a range reference wher you referr to it. I prefer to use a Named range. You tell me what's more descriptive...
Code: 
sSQL = sSQL & "WHERE (`Sheet2$`.`FA Split Master Lookup` In (" & MakeList([AccountList]) & "))"
or
Code: 
sSQL = sSQL & "WHERE (`Sheet2$`.`FA Split Master Lookup` In (" & MakeList(sheets("Sheet4").Range("A1:A5"))) & "))"


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
Oh Shiiiit. Do you mean I was supposed to do this only for 5 Account numbers???

Like an initial test???

I just ran the Macro on the whole file and it looks like it may crash my computer. The CPU usage has shot up to 90%.


I think I prefer this:

Code: 
sSQL = sSQL & "WHERE (`Sheet2$`.`FA Split Master Lookup` In (" & MakeList(sheets("Sheet4").Range("A1:A5"))) & "))"

To give you an exact idea of whats going on:

Sheet 1 has this informaation:

Column A: Pool Unique Fa (This is the list of the almost 58000 account numbers)

Column C: FC E-Mail (This has the contact names and e-mail)


Sheet 2 has this information:

Column A: FA Split master lookup (These are the account numbers that need to be looked up against Pool Unique FA in Column A of Sheet 1)

Sheet 2 also has a lot of other columns that I am ignoring right now.


Sheet 3 has this information in 3 adjacent columns:

Pool Unique FA FC Email FA Split Master Lookup

Sheet 3 is where I ran the Database queery.

I inserted a new sheet (sheet 4) that is where I ran the Macro.

Hope this makes sense to you
 
Upvote 0 Downvote
Ok I guess my computer did not crash.

I got back an error:

A box popped up saying

(Runtime error '9')
Subscript out of range.

I then pressed the debug button and the following code was highlighted:

Code: 
With Sheets("Sheet4").QueryTables(1)[code]
 
Upvote 0 Downvote


"Do you mean I was supposed to do this only for 5 Account numbers???"

"my boss wants information on (the contact names for the numbers). These are about 2000 altogether."

I guess there will be about 2000.

I use LISTS like I suggested, when I am querying OTHER databases. In your case, I'd probably put the 2000 on a separate sheet (table) and JOIN it to the source table in the query, rather than using then MakeList() Function. It may choke on 2000.


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



Is the QueryTable on Sheet4? IF not, change the name in the Sheets object.

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