Hello!
I have a really long list of SKU`s and I need to add the same tag to all of the products associated to them. I was wondering if any of you could help me in this because it would take me 2 or 3 days to do it manually. Thank you!
1 Like
you download export your products sheet and then you set your sku in sheet after that you uploaded your products sheet this is one and easy option for you
To make this a little less painful, you can download all products as an export, then create a second worksheet tab in this workbook with the list of SKUs you want to update in the first column. Paste the SKUs you want to update into the first column of the second worksheet, and in the second column there put in the tags you want to add beside each SKU. In the first worksheet with the product export data you can create a new blank column with a Vlookup function to look into the second worksheet to compare the SKUs and pull in the new tags. We do this all the time and though it’s still annoying, it’s the best we can find at the moment for mass price updates and tag changes etc.
For the Vlookup, you would go into the first cell of the blank column in the export sheet, type =Vlookup, and then start filling in the values by clicking on cells. The first cell is the SKU you would look up from the export, in my export that’s S2, so click that and see that it added it to the formula.
Next you want to tell it where to match the SKU, so click on the second worksheet and highlight the entire block of first 2 columns there with all the SKUs and tags, and return back to the first sheet. You might notice it changes the title of the worksheet in the formula to the first worksheet, that can be fixed after to match the name of the second sheet, haven’t figured out how to avoid that.
Then you want to give the formula the column number where the tag data is you want to pull from the second sheet, so for this you would use 2. Finally end the formula with false (Exact match, I never say true).
Next put a $ sign in front of the parts of the formula where it specifies the selected search area from the second sheet, this tells Excel not to increment the numbers as it goes down the spreadsheet. For our formula below that’s one in front of A, in front of 2, in front of B and in front of 92.
You can wrap the formula in an error check to add a blank or other text when there’s an error in the lookup so that it doesn’t add N/A where it doesn’t find the SLU, and you would end up with a formula that looks like this, Sheet2 is whatever your second sheet is titled:
=IFERROR(VLOOKUP(S2,Sheet2!$A$2:$B$92,2,FALSE),“”)
This will populate whatever is in column 2 in the second sheet where it matches the SKU on sheet 1.
Copy this formula into each cell all the way down to the end of the product data worksheet. One cheat here is a double-clicking the little green square that appears on the bottom right of cells when you click into them, that will copy values all the way down in most cases where there isn’t breaks in the data. You can also do the copy, highlight the whole column, then paste.
Finally you can insert a new column for your final tags data next to your existing tags column, and add a formula to combine the tag you pulled in to the existing tags and copy it all the way down as well, like this:
=Concat(,“,”,)
This should give you a brand new tags column with the old plus new tags. Some tag cells will have an extra comma at the end, that won’t hurt.o av
To avoid extra tag data where there are image rows and you want it blank, you can add a check that the original tags column has data, like this, where G is the original tags column and I2 is the new tag to include:
=IF(G2<>“”,CONCAT(G2,“, “,I2),””)
Give this new column a heading of Variant SKU just like the old one.
You’ll want to highlight the brand new tags column, do a copy, and then paste values only so that the formula is removed and just leaved the values, then delete the original tags column. It’s a good idea to do this wherever you have formulas in case you delete something the formula was looking at and then gives errors after and loses the data it was showing.
Save the workbook as a new CSV and run an import from it. You can save the old workbook as an xlsx file to keep the multiple sheets and formulas in case anything goes wrong. It’s always good to keep a copy of the original export CSV as well so that you can revert the changes if needed.
If you get errors in the formulas, check that the column formats related to the lookups are General. Also numerical data can have hidden formatting that you may need to remove, you can do this by copying the column into notepad and doing a bulk find replace of anything like quotes or equal signs, and then pasting it back into Excel.
I find Google Sheets works better with Shopify data. Sometimes our product exports open corrupted in Excel but never Google Sheets.
It’s a lot but it works, hope it helps. Vlookup is your friend once you tame it.