Need adjust quantity in master spreadsheet with supplied supplier spreadsheet..

**CyclingExpress** · 07-09-2011, 11:41 PM

Say you have a Master spread sheet with two columns and one row containing "code" and a "quantity" like below -

(Master spreadsheet)

CODE | QUANTITY
987 | 5
988 | 22
989 | 0

Now I have the same spreadsheet given to me with a updated quantity from the supplier and would like to somehow combine the new spreadsheet updating the Master spreadsheet above BUT this supplier spreadsheet will have codes for stock that I don't want so do not want these added to the master spreadsheet, see below -

(Supplier spreadsheet)

CODE | QUANTITY
987 | 15
001 | 50
988 | 2
989 | 6
273 | 1

I will have thousands of codes with updated quantity's from suppliers and will need to update the quantity each day.
I am using excel 2007, if anyone knows if this can be done in Excel or any idea of what I'll need to do to get this done it would be much appreciated!

Thanks.

**JRockdown** · 07-10-2011, 05:17 PM

Cycling,

I think what you are looking for is VLOOKUP()

Sample:
=VLOOKUP(code_from_master,array_codeandquant_supplier,2,false)

I would recommend you just lock the array_codeandquant_supplier to the other workbook, then you should be able to select the first code_from_master cell for the formula and drag the formula down to autofill the values.

HTH,

RC

**CyclingExpress** · 07-11-2011, 02:27 AM

Hi, thanks for the info it worked but when I drag the VLOOKUP function on the master spreadsheet it will not work if the Master code is in a different order to the suppliers spreadsheet if the code is in the same order (same row) on both of the sheets it works fine but if not I get #N/A instead of a number (qty).

Here is a copy of the function on the master -
=VLOOKUP(A2,supplier!A2:B7,2,FALSE)

Thanks !

**vipul.halbe** · 07-11-2011, 02:39 AM

HI CYCLING INSEAD OF A2:B7
try A:B

**CyclingExpress** · 07-11-2011, 07:27 PM

^^^^^^^^^^^^^^^^^^^^^
Thanks man that worked great !

Need adjust quantity in master spreadsheet with supplied supplier spreadsheet..

LinkBack

Thread Tools

Rate This Thread

Display

Need adjust quantity in master spreadsheet with supplied supplier spreadsheet..

Re: Need adjust quantity in master spreadsheet with supplied supplier spreadsheet..

Re: Need adjust quantity in master spreadsheet with supplied supplier spreadsheet..

Re: Need adjust quantity in master spreadsheet with supplied supplier spreadsheet..

Re: Need adjust quantity in master spreadsheet with supplied supplier spreadsheet..

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions