Search code examples
google-sheetsinventorygs-vlookup

Update inventory from a partial list to a master list in Google Spreadsheets

I have two google spreadsheets.

Sheet 1. A master list of products. This master list contains many columns of data that need to remain intact, plus 80% of the rows have a unique SKU identifier and an inventory level to match it.

Sheet 2. A partial list of products that contains a SKU and a Inventory level

I would like to be able take the inventory level from Sheet 2 and update the inventory level in Sheet 1.

So for a simplified visual example:

Sheet 1
handle       SKU          inventory        
item1        IT1-1        5
item1        IT1-2        5
item1        IT1-3        3
item1        IT1-4        -2
item2        IT2-1        4
item2        IT2-2        5
item2        IT2-3        5
item3        IT3          NA
item3        IT3          NA
item3        IT3          NA



Sheet 2   
SKU          inventory
IT1-2        10
IT1-3        10
IT2-3        5

How can I easily update the inventory values from Sheet 2, into Sheet 1, based on the unique SKU data?

I've tried to look into using VLOOKUP but can't find an easily explanation of how to use it properly for this purpose.

Even if I could filter Sheet 1 with the Sheet 2 data, and then sort the columns the same, and then just copy and paste in the data with them matching up that would be cool too!

Or if I could basically delete all the rows from Sheet 1 that do not contain a SKU from Sheet 2, then the number of rows in each sheet would match, I could sort them both A-Z and again copy and paste in the inventory row.

I'd prefer a solution that works with Google Spreadsheets

Solution

  • In short, you can use this formula to get the new inventory numbers

    =IF(COUNTIF($B$2:B,B2)=1,IF(ISNA(VLOOKUP(B2,Sheet2!$A$2:B,2,0)),C2,VLOOKUP(B2,Sheet2!$A$2:B,2,0)),C2)

    Refer to new inventory column in this sheet.

    Detailed Explanation

    1. First of all we check if the occurance of the SKU is unique in Sheet 1 or not. This is the first IF condition of our formula.
    2. If it unique, and the SKU from Sheet 1 exists in Sheet 2, then we do a VLOOKUP for that SKU and use the inventory value
    3. For other cases, the older inventory is substituted.

    Once the new values are created, the older values can be overridden.