Need an Excel Macro/Formula for Lookup

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

Any help will be greatly appreciated.

Thanks

 

[SkipVought]

Hi,

How do you know there are three 1234 in sheet 2?

Have you checked out PivotTables?

Skip,

_{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]}

 

[SkipVought]

...a better way to ask is...

how did you GET three 1234 on sheet 2?

Skip,

_{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]}

 

[floridagunner]

Hi Skip,

What happened is this. I was given account number 1234. What I did was write a COUNTIF formula to see how many times this account number came up in Sheet 1.


Then from the VBA code I got from you yesterday, (The one which automatically inserts rows below the existing row) I populated those blank cells with the account numbers.

So now I have an exact match. By this I mean:

Sheet 1

Account# Contact Name

1234 Mike
1234 Dave
1234 John

Sheet 2

Account# Contact Name

1234
1234
1234

So now if account # 1234 comes up 3 times in Sheet 1 it is also coming up 3 times in sheet 2.

Now all I need is to populate the column with the contact names.

The reason I cant do a copy and paste is because the file is just too huge.

I hope I explained myself better this time.

Thanks

 

[Corgano]

If you have all of the data setup the correct way in sheet 1, why do you need to use formulas to get it onto sheet 2?

A better way to ask: What are you changing from sheet 1 to sheet 2?

 

[yogia]

Hi floridagunner:

Notwithstanding the meaningful questionds from Skip and Corgano about the wisdom of doing it this way, following is a formula based approach ...

For my Sheet1, I have used the same data as in your post.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[floridagunner]

Thanks Yogia I am going to try out your approach right now

Thanks

 

[SkipVought]

The problem with this approch is tha you have to start out with the correct number of occurrences of each account.

Seems to me that that takes alot of laborious up front work, and extra steps to MODIFY.

Since this comes from a custom select list, why not drive directly from that list?

Skip,

_{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]}

 

[floridagunner]

Hello, Yogia and Skip,

I am not having any luck with this formula based approach either.

When I put it into my worksheet what it is doing is bringing back 2 values for a range of Identical account numbers.

Example:

Sheet 1

Account# Contact Name

1234 Mike
1234 Dave
1234 John
1234 Mark
1234 Roger
1234 Sally
1234 Cristopher

what its doing is thhis:

Sheet 2

Account# Contact Name

1234 Mike
1234 Mike
1234 Mike
1234 Mike
1234 Mike
1234 Mike
1234 Cristopher

So what it seems to be doing is filling the last occurence of 1234 with the last matching name in sheet 1. But all the rest its putting in Mike.

By in my actual file, the contact name column in sheet1 is column c.

Thanks