vlookup and macro

[didilika]

Hi... i am looking for a macro that can do the following:

have 2 xls files one called January.xls the other databaseJanuary.xls

i have in january.xls in column a and b -selected serial numbers and names of customers
i have all customers in databaseJanuary.xls with 35columns of information: column A serial numbers , column b-age, column c-favorite food etc....

with macro I wish to copy all data from all 35 columns from datsbasejanuary.xls to my other xls matched by serial number in column A.

is it possible?

i know i could vlookup tha data 1 by 1 but i want a quicker solution.

appreciate the help-code. Di

 

[jbeaucaire]

You can use the VLOOKUP() formula to bring the data from one workbook into the other matching on the serial number. No need for a macro. Put in the VLOOKUP formulas across the first row of the January.xls file until all the values appear properly. Then copy those cells down for the rest of the data.

You could create a macro, but it would take way more time than time saved.

 

[didilika]

jbeaucaire;2835086] how do i vlookup all 35 at the same time.... i just dont want to do it 1 by 1.. thats why i wish to find quicker way.....

 

[jbeaucaire]

You can construct a single VLOOKUP in column B that when copied down and across will adjust itself to pull from other columns in the target workbook.

So, if the workbook is called databaseJanuary.xls sheet1 and the first formula goes in january.xls sheet1 at cell B2 (to grab the value from column B in the other sheet), the formula would be something like this...

=VLOOKUP($A2, '[databaseJanuary.xls]Sheet1'!$E:$AK, 2, 0)

The C2 formula would be:
=VLOOKUP($A2, '[databaseJanuary.xls]Sheet1'!$E:$AK, 3, 0)

These are easy changes to make, but you can use the COLUMN() function to change it for you.

B2: =VLOOKUP($A2, '[databaseJanuary.xls]Sheet1'!$E:$AK, COLUMN(B$1), 0)


Now you can copy that cell to the right and each value should appear. Correct the formatting of the cells as needed, then copy down.