Forum Discussion

cliftonmurphyjr's avatar
cliftonmurphyjr
Copper Contributor
Mar 01, 2020

migrating data from one sheet to another

Ive got two Excel files. One we will call file-A and one we will call file-B.

 

I have a column in both sheets called "SKU" and a column called "PRICE". I need to know how to get Excel to search through file-B for any SKU matching a SKU from file-A and import the data for the "PRICE" of that skew from file-B into the "PRICE" for that SKU in file-A.

 

The problem is that file-B has all the prices, and file-A doesnt have any. At the same time there are 4000 rows in file-B and only 1200 in file-A...basically i am needing to import the prices for those 1200 products in file-A from file-B.

 

Can anyone give me a tutorial step by step on how to do this, or point me to a step by step on how to do this?

excel
Formulas and Functions

3 Replies

  • mathetes's avatar
    mathetes
    Silver Contributor
    Mar 02, 2020

    cliftonmurphyjr 

     

    1. Have both files open. (I'm assuming the data in both files are organized in columns, and adjacent columns in File-B)
    2. In File-A start writing a VLOOKUP formula, by entering in a cell next to the SKU column of File-A, the following =VLOOKUP(A2,   [this is assuming that the first SKU is in cell A2; we'll end up copying the formula down all 1200 rows]
    3. Then keeping that formula open, move to File-B and highlight and select the columns containing SKU and Price, such that in File-A, the formula now reads something like
    4. =VLOOKUP(A2,[File-B.xls]worksheet1!A2:B2000,
    5. And then add to the formula so that it ends looking something like:
    6. =VLOOKUP(A2,[File-B.xls]worksheet1!A2:B2000,2,0)
    7. And copy that formula down all 1,200 rows in File-A

     

    If that doesn't work (I've created those from memory, not actually testing them in Excel, but do have formulas like that in some of my own multi-workbook files)....if that doesn't work, please upload your actual spreadsheets so we can work with the actual files. Or some representative samples (50 rows and 200 rows in A and B respectively)

    • MisterE's avatar
      MisterE
      Copper Contributor
      Mar 02, 2020

      @cliftonmurphyjr

      I agree with mathetes procedure, in addition to the formula at step 6 which is

      =VLOOKUP(A2,[File-B.xls]worksheet1!A2:B2000,2,0), before you copy this formula down to the remaining 1199 rows you need to fix the formula so the range values are fixed and will not transpose which is =VLOOKUP(A2,[File-B.xls]worksheet1!$A$2:$B$2000,2,0). 

      By adding a $ sign with the range value, this ensures that the range is fixed for all 1199 rows.

Resources