Simplifying a Formula

[Tootlez]

My formula is super long and I know there has to be a way to make it shorter, probably with a VBA loop, but I'm sick of looking at it, and I don't know where to start.

General Setup:
Sheet 1 has 3 cells: Cell B has a VLookUp to Sheet 2, which returns a number. Cell C uses the number to print Cell A as a image file name with Cell B's value minus one to create a image gallery list.

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]XX-SKU[/TD]
[TD]5[/TD]
[TD]/XX-SKU_1.jpg,/XX-SKU_2.jpg,/XX-SKU_3.jpg,/XX-SKU_4.jpg[/TD]
[/TR]
</tbody>[/TABLE]

The Formula in Cell C is ridiculously long due to the amount of IF's needed to cover at least to 20 cases of numbers.

I'm not all too familiar with all the syntax/referencing for VBA. I am not sure how to generate the list properly either.

The first number (not shown in the table above) has NO underscore, and is not apart of this Cell C. Cell C is blank if there is only a 1 image.
For the following gallery items: there is to be a comma between each, and all start with the forward slash.

Any help is much appreciated

 

[phoenixdown]

Can you give us your formulas for the columns A, B and C above?
is there a missing column with another value being referenced?


Do you want to do away with A and B and just leave C as a result?

 

[Tootlez]

Ah sorry the tables on here are fickle ... yes there is only one more cell missing and that is just the cell for when Cell B (above) is 1, where I put the plain sku without the underscore.

A is just my running list of Sku's (no Formula)

CELL B is just pulling the number from Sheet 2 which counts my images for me.

=VLOOKUP(SUBSTITUTE(A2, " /2",""), 'No of Images'!E:H, 4, FALSE)

CELL C's up to the result for CELL B's number

=IF(OR(L2=0,L2=1), "",  IF(L2=2, "/"&SUBSTITUTE(A2, " /2", "")&"_1.jpg",
  IF(L2=3, "/"&SUBSTITUTE(A2, " /2", "")&"_1.jpg,/"&SUBSTITUTE(A2, " /2", "")&"_2.jpg",
  IF(L2=4, "/"&SUBSTITUTE(A2, " /2", "")&"_1.jpg,/"&SUBSTITUTE(A2, " /2", "")&"_2.jpg,/"&SUBSTITUTE(A2, " /2", "")&"_3.jpg",
  IF(L2=5, "/"&SUBSTITUTE(A2, " /2", "")&"_1.jpg,/"&SUBSTITUTE(A2, " /2", "")&"_2.jpg,/"&SUBSTITUTE(A2, " /2", "")&"_3.jpg,/"&SUBSTITUTE(A2, " /2", "")&"_4.jpg", ... )))))

(Note: Ah I forgot to mention the substitute. -- I have some items that have a /2 on the end, which you cannot have in a file name, so I had to remove it somehow, and thus, a million substitutes, which also triggered me to want to find a better way)

I need all my cells to stay as my import file needs them.
This is primarily a stand-a-lone sheet, I only added in the vlookup Cell B because I was tired of manually counting files.

 

[Tootlez]

I started playing around with some ideas .... Curious if I'm on the right track

Currently I have it just outputting to just the 1 cell, but I have an I set up to do that later, which assuming it will just be an outer for loop over everything.

Sub gallery_gen()

    Dim imgs As Integer
    Dim gal As String
    Dim i As Integer


    If Range("$B2") = 1 Then Range("$C2") = "/" & Replace(Range("$A2"), " /2", "") & ".jpg"

    [U]Else if Range("$L2") > 1 then[/U]
        For imgs = 2 To Range("$L2")
            
           gal = gal & Range("$C2") = "/" & Replace(Range("$A2"), " /2", "") & "_" & imgs - 1 & ".jpg"
        Next
    
End Sub

I'm trying to figure out my error on the line underlined


[It's time to leave work. I'll catch any reply tomorrow when I get in]

 

[phoenixdown]

Not sure of a clean way to do this with only formula's. I think you will need a macro which I am not so good at but others here are and will see this thread soon.

There is a IF formula limit you would hit before you reached 20 images too btw.

 

[István Hirsch]

It was a great help if you would show us just the source data in one column and the expected result in another produced manually (for different L2-s)

 

[Tootlez]

Ok.

So if I have 5 images for SKU-A the image gallery should read: /SKU-A_1.jpg,/SKU-A_2.jpg,/SKU-A_3.jpg,/SKU-A_4.jpg
If I have 2 Images of SKU-B the image gallery should read: /SKU-B_1.jpg
If I have 8 images of SKU-C it will read: /SKU-C_1.jpg,/SKU-C_2.jpg,/SKU-C_3.jpg,/SKU-C_4.jpg,/SKU-C_5.jpg,/SKU-C_6.jpg,/SKU-C_7.jpg


I will have X amount of SKU's so i figured it would go into a for loop to run as many rows i have.
The numbers vary from 1 to how ever many images I have (I had a time I needed it for 20 images).
If I have 1 image, the gallery is always blank.

I figured a macro would be the best answer, I just don't know VBA clear enough with out looking up every since line and keyword.

 

[István Hirsch]

Is it OK if each file name is in a different cell?

 

[Tootlez]

They need to be in one cell, read out like the examples above with a comma between each of the /SKU_#.jpg

 

[István Hirsch]

Give this UDF a try:

Function fffx(s, n As String) As String
    For i = 1 To n - 1
        fname(i) = "/" & s & "_" & i & ".jpeg"
    Next
    fffx = Application.Join(fname, ",")
End Function