0

I have a list of 7000 products in Excel ("list1"). Each can be identified by a unique identifier ("sku"). I retrieved a different list of 50-100 products from the main list ("list2"). With this separate list, I want to run the following code in Excel:

For each product in list2, search sku in column A list1
   if match:
      rowNumber = current.row.number()
      Replace entire contents in rowNumber and column U with "Not Visible Individually"

Objective

I have an exported product list from Magento which include simple and grouped products. I want to quickly change all the individual products within the group products "Visibility" from "Catalog, Search" to "Not Visibility Individually"

Please ask if this needs more clarification

Raystafarian
  • 21,963
  • 12
  • 64
  • 91
codaamok
  • 1,393

2 Answers2

2

You can do this with VBA, something like this -

Sub test()
Dim ws1 As Worksheet
Dim ws2 As Worksheet

 'rename sheet1 to point to the data you want to change and 'ws2 to the sheets you're looking up from
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

Dim rngsearch As Range
Dim rngfnd As Range
'These point to the SKU columns, change as needed   
Set rngsearch = ws2.Range("A:A")
Set rngfnd = ws1.Range("A:A")

Dim c As Range
Dim d As Range
For Each c In rngsearch
    If c <> "" Then
        For Each d In rngfnd
            If d = c Then
              'this assumes SKU in A and target in U  
              d.Offset(20) = "Not Visibility Individually"
            End If
        Next
    End If
Next

End Sub

If the sku shows up more than once, it will find those as well.

Test this on a copy of your data.

Raystafarian
  • 21,963
  • 12
  • 64
  • 91
1

I decided to use a VLOOKUP solution instead.

Create another sheet with 1 column of SKUs and the second column of "Not Visible Individually" from top to bottom. Then ran the function from top to bottom in column U on list1 ("visiblity" column).

codaamok
  • 1,393