Excel Question Macro

Closed
Report
Posts
1
Registration date
Thursday December 23, 2010
Status
Member
Last seen
December 23, 2010
-
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
-
Hello,

I am needing help with writing a macro for Excel. Here is my issue. I have two workbooks. each has over 10k rows this is what I would like to see happen

Sample:

Item # | Description List | Price
12345 | bicycle pump | $25

I would like it to look at the Item # then find the Item in the second spread sheet and paste that price in so that it looks like this:

Item # | Description List | Price | Vendor 1
12345 | bicycle pump | $25 | $18

Then I can do this for all my vendors so I can make a master price list. can anyone help Please

The two spread sheets are not mirror images so it would need to find the item in the list.

1 reply

Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi maxmouse24,

The function Vlookup is what you are looking for. I think the syntaxis speaks for it self. If you need more help, post your workbook using a site like www.speedyshare.com, for more detailed help.

Best regards,
Trowa
Trowa,
Thank you for your reply. I am still lost. it is actually 2 workbooks that I need it to look at one and copy data out of one and put in the proper place on the other workbook.
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi maxmouse24,

The procedure is basically the same.

Workbook1:
Item # | Description List | Price
12345 | bicycle pump | $25

Workbook2
Item # | Description List | Price | Vendor 1
12345 | bicycle pump | $25 | $18

Open both workbooks.
Workbook1, cell D2, click on Fx left of the formulabar and select Vlookup.
Searchvalue: select A2 of Workbook1.
Matrix: select A2:D10000 of workbook2.
Column Index number:4
Hit OK.
Your formula should look something like:
=VLOOKUP(A1,'C:\SomeDirectory\AnotherDirectory\[Workbook2.xls]Sheet1'!$A$2:$D$10000,4)

Now your formula is ready to be dragged down.

Whenever you open Workbook1, excel will ask you to update the formula results or not.

Has this made you find your way again?

Best regards,
Trowa