Woocommerce Question posted in
The official Woocommerce documentation can be found here.

Woocommerce – How to TRANSPOSE or TEXTJOIN each "group" of images for a certain SKU?

Sheet containing 1000+ product SKUs, and ~4000 images. Not every SKU will have the same number of images.

Right now, it lists 1 image per line:

SKU001   Image001_1.jpg
SKU001   Image001_2.jpg
SKU001   Image001_3.jpg
SKU002   Image002_1.jpg
SKU002   Image002_2.jpg
SKU003   Image003_1.jpg

…and so on.

In order to import these products into WooCommerce, I need to provide a comma-separated list of images for each SKU:

SKU001 Image001_1.jpg,Image001_2.jpg,Image001_3.jpg
SKU002 Image002_1.jpg,Image002_2.jpg
SKU003 Image003_1.jpg

I know how to use the TRANSPOSE function, manually. I also know how to use TEXTJOIN to combine cells that are already listed horizontally.

What I don’t know is how to do it automatically by SKU.

I.e. read Column A, and TRANSPOSE all items in Column B until the value in Column A changes.

I’m sure there’s something with COUNTIF, TRANSPOSE, FILTER, or a combination thereof, but everything I’ve tried so far, failed.

Any suggestions?

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    OK, figured it out :)

    It's a 3-step solution but it's a MUCH simpler formula.

    Step 1: isolate the SKUs (1 unique instance of each) - make a new column/sheet, and put the following formula in it:

    =unique(A:A)

    (where "A" is the column containing the SKUs).

    This gives us a list that looks like:

    [ Column A ] [ Column B ] [ Column C ]    [ Column D ]
                                        ('=UNIQUE(A:A)')
   SKU001     Image001_1.jpg               SKU001    
   SKU001     Image001_2.jpg               SKU002
   SKU001     Image001_3.jpg               SKU003
   SKU002     Image002_1.jpg               SKU004
   SKU002     Image002_2.jpg
   SKU003     Image003_1.jpg

    Step 2: now that we have ONE unique SKU, we can use it to (1) filter out results from Column B (Condition 1) as long as they parallel the value in Column A that matches to the value of the current line in Column D (Condition 2)... and (2) transpose the results horizontally:

    =transpose(filter(B:B,A:A=D1))

    We put this formula into Column E, and copy it down so Sheets auto-increments the value D1, D2, D3, etc:

    [ Column D ] [ Column E ]
   SKU001      =transpose(filter(B:B,A:A=D1))
   SKU002      =transpose(filter(B:B,A:A=D2))
   SKU003      =transpose(filter(B:B,A:A=D3))
   SKU004      =transpose(filter(B:B,A:A=D4))

    To put it another way: FILTER "reads Column B WHILE Column A equals D1". And then the resulting list of values from Column B is turned 90 degrees and written out horizontally.

    Results:

    [ Column D ]  [ Column E ]    [ Column F ]    [ Column G ]
   SKU001     Image001_1.jpg  Image001_2.jpg  Image001_3.jpg
   SKU002     Image002_1.jpg  Image002_2.jpg 
   SKU003     Image003_1.jpg

    So now, we have a list of image URLs listed horizontally, 1 per cell. But we need a combined list, comma-separated. This is where TEXTJOIN comes in.

    Step 3: find the next completely empty column (no image URLs), for example Column M, and use:

    =TEXTJOIN(",",TRUE,E1:L1)

    TEXTJOIN takes a delimiter (in this case comma, ","), has an option to skip empty cells (TRUE), and then a range (E1 through L1).

    Result (Column M):

    Image001_1.jpg,Image001_2.jpg,Image001_3.jpg
Image002_1.jpg,Image002_2.jpg

    ...which is exactly what was needed for a WooCommerce import.

    The benefit of this is that you can see the intermediate steps, it's easier to troubleshoot, and you also yield a filtered list of SKUs in case you need it for a catalog or something.


  2. 0

    try:

    =ARRAYFORMULA(REGEXREPLACE(SPLIT(TRANSPOSE(SUBSTITUTE(
 TRIM(QUERY(QUERY({A1:A&"♦", B1:B}, 
 "select max(Col2) 
  where Col2 is not null 
  group by Col2 
  pivot Col1"),,999^99)), " ", ",")), "♦"), "^,", ))

    0

    Login or Signup to reply.
  3. 0

    This extract only from SKU:

    ={
   UNIQUE(filter(sort(A70:A),A70:A<>"")),
     arrayformula (
       substitute (  
         trim(arrayformula (TRANSPOSE (
            QUERY (
               TRANSPOSE (transpose (arrayformula (
                 if (sequence (counta (filter (sort (A70:A),A70:A<>""))) * 
                     TRANSPOSE (sequence (counta (unique (filter(sort(A70:A),A70:A<>""))))^0)*1>
                     transpose(query(filter(sort(A70:A),A70:A<>""),
                       "Select count(Col1) group by Col1 label count(Col1) ''")),
                     "",
                     "Image" & right (transpose (UNIQUE (filter (sort (A70:A),A70:A<>""))),3) & "_" & 
                               sequence (counta(A70:A)) * TRANSPOSE (sequence (counta (unique (A70:A)))^0)*1 & ".jpg")))&" ")
              , ,100)
          ))) ," ",", "))
 }

    enter image description here

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

Back To Top
Search