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!

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

Need Help with Macro/Vlookup

Status
Not open for further replies.
Joined
Jul 13, 2007
Messages
47
Location
US
I have a complex problem.

I have a datasheet with number in one work sheet (sheet 1).

EG:

Account#
12345
45678
52314

In another worksheet (sheet 2) I have have the same account numbers recurring multiple times like this:

12345
12345
12345
45678
45678

What I need to do is write a macro that will look Vlookup the account numbers in sheet 1 from the table in Sheet 2. and if there are duplicates I need the macro to insert additional rows automatically into sheet 1 and bring in the duplicate information as well.

Please Help.
Thanks
 
Hi floridagunner:

How about explaining the intent (purpose) here? are you trying to merge the data from two sheets into one?

The reason I ask is if you are trying to merge the data from two sheets into one, you may not need to LOOKUP or VLOOKUP for anything. But let me not jump the gun and wait to have you clarify the intent of your operation.

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

I think the easiest solution for your problem is using a query.

go to Data --> Import External Data --> New Database Query

Then select 'Excel Files' and the workbook your data is in.

From sheet 1 the column that holds Account# and from sheet 2, also the Account# and whatever other column you want.

Then link the Account# from both sheets to one and other and go to File --> Return Data to MicroSoft Excel.

You can then decide on some options and put the query where you want it.

Cheers,

Roel
 
Hello Yogia and Rofeu,
This is what I am trying to do:

Account # Contact Information
12345 John Smith@hotmail.com
67898 Mary Smith@hotmaail.com

In (Spreadsheet 1) I have account numbers in (column A) with their corresponding contact information such as e-mail address and phone numbers in Column B and so on.
The thing is this contact information was bought in from (Spreadsheet 2) using a VLOOKUP.

Spreadsheet 2 looks like this:

Account# Contact Information

12345 John Smith@hotmail.com
12345 Jay Smith@hotmail.com
12345 etc@hotmail.com

The problem with the VLOOKUP is that it only brings in information from the 1st entry of Account # 12345 ie.John Smith@hotmail.com

What I need to do is bring in all the duplicate account numbers as well as the corresponding contact information. This means that I think I need a macro that will automatically insert new rows in (Spreadsheet 1) whenever a duplicate account number occurs in (Spreadsheet 2)and also bring in the corresponding contact information.

Hope I explained it better this time.

Rofeu I am trying your solution now.

Thanks
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top