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

 

[Bong]

I don't understand the underlying logic. On the one hand, you seem to be creating a copy of sheet-1 on sheet-2. You could do that with copy-paste. On the other hand, maybe you're restricting the entries on sheet-2 to the first (?) n occurrences of any given contract number from sheet-1? It will be easy enough to do if you can elaborate a little.

_________________
Bob Rashkin

 

[Chance1234]

How about this,

Declare two arrays

Populate array one with data from sheet1
Populate array two with data from sheet2


Loop through array one, and then loop through array two looking for matches and creating a string where there is a match,

Return Array one back to sheet1

If you do a search on this group with keywords arrays and ranges, there are quite a few examples of this.


Chance,

Filmmaker, gentleman and ROMAN!

 

[SkipVought]

I don't understand the underlying logic...

That's why I asked the question in your other post in Forum68.

Please describe clearly what you are trying to accomplish. Don't say, "I'm trying to lookup values." Tell us the functionality you are trying to achieve, NOT how you are tyring to get there.

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]

Hello Bong thanks for your reply.

The logic is tricky but the reason I cant do a copy and paste is that I have to put in a only a particular selection of Account numbers.

You see Sheet 1 has almost 58000 account numbers (some unique, some duplicate).

Sheet 2 has the account numbers that my boss wants information on (the contact names for the numbers). These are about 2000 altogether.

So ina nutshell the contact names that I need are all over the place in sheet 1 and thats why I cant do a copy and paste.

Hi Skipvought, how do you create that box in which you show your examples?

Thanks

 

[SkipVought]

You could use a QUERY faq68-5829

I do this often, having 30,000 - 50,000 rows of data to return several hundred.

Once you understand it, it's quite simple.

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]

Hi Skip,

Are you saying then that this cant be done with a formula or VBA Code?

Thanks

 

[SkipVought]

Start with a list of account numbers.

On a new sheet, query the source sheet set a criteria in the QBE grid for Account Number Is One Of and then enter ONE of your account numbers, just as a placeholder. File > Return data to Excel.

Now turn on your macro recorder and record Data > Get External Data > Edit Query... and get back into the QBE Grid and just File > Return data to Excel

This code will be the basis for your data acquisition (instead of lookups)

When you get this far, post back with your recorded code for more help and for making an IN LIST of your Account Numbers.

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 thanks Skip I will try this right now.

Hey is something wrong with the sites e-mail notification because I dont seem to be getting e-mail notices when someone posts a reply

Thanks

 

[SkipVought]

Not to my knowledge, but then I rarely use eMail notification.

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]

 

[SkipVought]

FG,

It appears that Tek-Tips has experienced a backup condition this morning with eMail notofications.

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]

Hi Skip,
I tried the method that you listed above but it does not seem to be working.

I know you said to go into the field (Edit Query) but that field is not active on my screen.

I tried it a different way but I keep getting the message

"This database contains no visible tables"

Thanks

 

[SkipVought]

Did you read the FAQ and actually try it?

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]

Skip I tried it at least for a couple of hours.

At first when I tried to import the external data for some reason it would not connect to anything. Then I restarted my computer and I was able to get a connection but I keep getting the same message

"This file contains no visible tables"

Whatever that means.

By the way what FAQ are you actually refering to?

Thanks

 

[SkipVought]

There is an OPTIONS button. Check ALL the selections.

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]

what FAQ are you actually refering to?
FAQ68-5829

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Yes!

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]

Oh man Skip you are right. Its bringing up the data

Ok I guess I can go the next stage. I dont know if this is going to work as I have never used this queery function before.

Thanks

 

[SkipVought]

Each one of us has had a "First Time" at everything!

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]

Hey Skip are you still here?