CODEWITHSUNDEEP
excelexcel-2007excel-2010vba
Mike Howe
Mike Howe

Reputation: 263

Need to copy a column from on Sheet to another based on a value

I have 2 Excel Sheets. I want to copy the values from the "CategoryIds" column to another sheet that has a matching column on the SKU field of that sheet.

I need the "CategoryIds" column in this sheet : NewDepotProdsDB.xls,

to be copied into this sheet : DEPOT_products.xlsx,

based on a match with the "SKU" column.

NewDepotProdsDB.xls  :
SKU  CategoryIds
5845 8 
8545 58
...


DEPOT_products.xlsx :
SKU  CategoryIds
5845 > need to copy the value from NewDepotProdsDB.xls here >
8545 > need to copy the value from NewDepotProdsDB.xls here >
....

Is this possible to do?

I could temporarily copy those 2 columns (SKU CategoryIds) to the DEPOT_products.xlsx sheet and then use a formula or something for this. I'm not good at Excel and dont' really know what I'm doing.

Note : I have the 97-2003 version of Excel.

Thanks

Upvotes: 0

Views: 2336

Answers (2)

K V
K V

Reputation: 95

You can simply use the vlookup formula.

Example:

if the SKU values are in B col, starting from 5th row in depot_product xlsx and the category ids and sku values are in sheet2 of NewDepotProdsDB (in C14 to D15) cells, then you can use the following formula.

=VLOOKUP(B5,[NewDepotProdsDB.xls]Sheet2!$C$14:$D$15,2,0)

the number 2 tells the excel to fetch the values from 2nd column from sku id column.

Upvotes: 0

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

Try this code. You should add this macros to file DEPOT_products.xlsx. I suppose that your data are in Sheet1 in each file in columns A and B (starting from the second row), but you can easily change it if it isn't true for you:

Sub test()

   Dim Wb1 As Workbook
   Dim Wb2 As Workbook
   Dim sh1 As Worksheet
   Dim sh2 As Worksheet
   Dim lastrow1 As Long
   Dim lastrow2 As Long
   Dim vlookup As String

   On Error GoTo ErrHandler

   'current workbook (DEPOT_products.xlsx)'
   Set Wb1 = ThisWorkbook

   'path to the NewDepotProdsDB.xls file'
   Workbooks.Open Filename:="C:\NewDepotProdsDB.xls", ReadOnly:=True
   Set Wb2 = ActiveWorkbook

   'set correct name of the sheet with your data in file DEPOT_products.xlsx'
   Set sh1 = Wb1.Sheets("Sheet1")

   'set correct name of the sheet with your data in file NewDepotProdsDB.xls'
   Set sh2 = Wb2.Sheets("Sheet1")

   'determining last row of your data in file DEPOT_products.xlsx'
   lastrow1 = sh1.Range("A" & sh1.Rows.Count).End(xlUp).Row

   'determining last row of your data in file NewDepotProdsDB.xls'
   lastrow2 = sh2.Range("A" & sh2.Rows.Count).End(xlUp).Row

   'write the formula to match your SKU column in DEPOT_products.xlsx to SKU column in NewDepotProdsDB.xls and get column CategoryIds'
   vlookup = "VLOOKUP(A2," & "[" & Wb2.Name & "]" _
       & sh2.Name & "!" & "$A$2:$B$" & lastrow2 _
       & ",2, 0)"
   sh1.Range("B2:B" & lastrow1).Formula = "=IF(ISERROR(" & vlookup & ")," _
        & """not found""" & "," & vlookup & ")"

   'copy formulas result and paste them like a values (to kill formulas) - you can comment the code below, so you will have formulas instead values'
   sh1.Range("B2:B" & lastrow1).Copy
   sh1.Range("B2").PasteSpecial xlPasteValues
   Application.CutCopyMode = False


   ' close NewDepotProdsDB.xls
   Wb2.Close False

ErrHandler:
    If Err.Number = 1004 Then
        MsgBox "File not found: " + Err.Description
    Else
        MsgBox "Unknown error: " + Err.Description
    End If
End Sub

Hope it helps. I've tried to comment the code in details, but if you have some questions, ask please.

UPD: Alternatively you can use formula instead VBA code. Just write it in DEPOT_products.xlsx in the sheet with your data (column CategoryIds):

=IF(ISERROR(VLOOKUP(A2,'C:\Work\[NewDepotProdsDB.xls]Sheet1'!$A$2:$B$500,2,0)),"not found",VLOOKUP(A2,'C:\Work\[NewDepotProdsDB.xls]Sheet1'!$A$2:$B$500,2,0))

1) change A2 to address of your first SKU in DEPOT_products.xlsx and put this formula to the cell, corresponding the first CategoryIds

2) change 'C:\Work\[NewDepotProdsDB.xls]Sheet1'!$A$2:$B$500 to the path of your NewDepotProdsDB.xls file + correct sheet + correct range of SKU and CategoryIds.

3) drop this formula down to the last row with SKU

Upvotes: 1

Related Questions