Shopify Question posted in
The Shopify help center can be found here.

Merge two excel sheets the imported cells to match with each cells – Shopify

I have an inventory list which has contains 1000 product with zero pictures, and a sheet which have 300 links of the product pictures in excel, I’m looking for a way to combine the two sheets, so that each product will match with its picture link.

NOTE:
1. I have tried using Kutools add-in to import pictures matching to a cell value, which works perfectly, but Shopify wants only image links, not the image itself.
2. I then tried Ablebits add-in to compare the two sheets so that I can delete the cells that don’t match, that works too, but I can’t get the links column to match the names column.
3. Some of the links in the picture sheet contains partial, or full name of a product
4.The product sheet has a column with the names of the products
I’m using Excel 2016.
5. I have Kutool Add-ins, Ablebit Tools Add-ins and Ablebit Data Add-ins installed.

The First sheet “Product list” is the sheet that contains the list of the products Screenshot of the product list, The second sheet “Links of Product Images” contains the links of the images Screenshot of the Image link, The last sheet is an example of how I want it to be Screenshot of my desired result, Each image link matches to the product.

How can I achieve this please help?
Thanks in advance.

Tags:

2

Answers


  1. 0

    Assuming sanitized name column in Product List sheet has matches in File Name column in Links of Product Images sheet you can use a VLookup to get the URL. This also assumes that there are is not the same sanitized name for multiple products that have different URLs. It is easy enough to change formula if there is a lookup column that provides unique matches available. Hard to tell with screenshots provided.

    In Product List sheet enter the following formula in column K and drag down for as many rows as required:

    =IFERROR(VLOOKUP(C2,'Links of Product Images'!A:B,2,FALSE),"")

    Image of formula

    You may need to amend the column lookups if the match is found using different columns.

    Login or Signup to reply.
  2. 0 
    Sub sa()
Sheet3.Range("A:B").Clear
Sheet1.Select 'PRODUCTLIST
Sheet1.Range("C:C").Copy
Sheet3.Select 'FINAL RESULT
Sheet3.Range("A1").PasteSpecial Paste:=xlPasteValues
                For Each PRng In Range("A:A").Cells.SpecialCells(xlCellTypeConstants)
            For Each RRng In Sheet2.Range("A:A").Cells.SpecialCells(xlCellTypeConstants)
             If RRng = PRng Then
                RRng.Cells(1, 2).Copy
                Sheet3.Range("B65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
            End If
           Next
           Next
End Sub

    Please Inform if it is Useful .. Thank You..

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search