Combine cells from a row to single column when item name changes

[gr8rck]

Hi,

Need a little help combining cells automatically when the columns aren't the exact same number of lines for each item.

As an example

Column A (A1-A2):
12PM-black-ivory-stripe-dress_1.jpg
12PM-black-ivory-stripe-dress_2.jpg

Column A (A3-A6)
12PM-blue-ivory-stripe-dress_1.jpg
12PM-blue-ivory-stripe-dress_2.jpg
12PM-blue-ivory-stripe-dress_3.jpg
12PM-blue-ivory-stripe-dress_4.jpg

I can get the above to show up in cell A1 by using the combine method, but what i can't get is that method to automatically understand there is a new product on line A3 and it has 4 items and should combine those 4 items into B3. It only wants to combine using same formula above using just 2 values.

I have a long list of products in column A1 that vary from 2 product images to 6 product images.

Any assistance would be helpful. Didn't see a way to upload a sheet on here like I did before.

Please advise.

Thanks,
Ryan.

 

[inactiveuserps07]

Hi, Ryan. You'll need to figure out a way to post an image, or to at least show us what you're trying to get as a result in Column B for the same range. We will also need to know what you're currently using as a formula to get those values in Column B.

 

[gr8rck]

Thanks.

here is the image link.
https://ibb.co/k7Uoca

 

[Rick Rothstein]

Because the the variability of counts per filename, I doubt if you will be able to do that with formulas alone. Here is a macro that should do it for you...

[table="width: 500"]
[tr]
	[td]Sub gr8rck()
  Dim Addr1 As String, Addr2 As String, Txt As String, Delimiter As String, Ar As Variant
  Delimiter = ", "
  Addr1 = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Addr2 = Range(Addr1).Offset(1).Address(0, 0)
  Range(Addr2).Offset(, 1) = Evaluate(Replace(Replace("IF(@="""","""",IF(LEFT(@,FIND(""_"",@))=LEFT(#,FIND(""_"",#&""_"")),"""",@))", "@", Addr2), "#", Addr1))
  For Each Ar In Range(Addr2).Offset(, 1).SpecialCells(xlBlanks).Areas
    Txt = Join(Application.Transpose(Ar.Offset(-1, -1).Resize(Ar.Rows.Count + 1)), ", ")
    If Right(Txt, Len(Delimiter)) = Delimiter Then Txt = Left(Txt, Len(Txt) - Len(Delimiter))
    Ar(1).Offset(-1) = Txt
  Next
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (gr8rck) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[inactiveuserps07]

Hey, Ryan. Here's what I recommend, if you want it to be formulas only with no VBA:

1. Make a copy of your Excel file with a different name, just so you don't have to panic that I don't know what I'm doing.

2. Add a column header to Column B (in cell B1): Combo List (or whatever)

3. Delete everything in Column B from B2 down.

4. Select everything from cell A1 to the bottom of Column B, stopping when you've reached your last entry in Column A. It should all be highlighted.

5. Go to the Insert tab and click "Table." Your data is now an official table. This will allow you to add records without copying formulas over and over.

6. Once your data is a table, place the following formula into cell B2:

=IF(LEFT(A2,LEN(A2)-8)=LEFT(A1,LEN(A1)-8),"",A2&(IF(COUNTIF(A:A,LEFT(A2,LEN(A2)-8)&"*")>1,", "&A3,""))&(IF(COUNTIF(A:A,LEFT(A2,LEN(A2)-8)&"*")>2,", "&A4,""))&(IF(COUNTIF(A:A,LEFT(A2,LEN(A2)-8)&"*")>3,", "&A5,""))&(IF(COUNTIF(A:A,LEFT(A2,LEN(A2)-8)&"*")>4,", "&A6,""))&(IF(COUNTIF(A:A,LEFT(A2,LEN(A2)-8)&"*")>5,", "&A7,""))&(IF(COUNTIF(A:A,LEFT(A2,LEN(A2)-8)&"*")>6,", "&A8,""))&(IF(COUNTIF(A:A,LEFT(A2,LEN(A2)-8)&"*")>7,", "&A9,"")))

It will copy itself down your table and give you the results you want.

It also will allow up to 8 images instead of 6, in case that changes.

 

[gr8rck]

Thanks everyone. @ ErikTyler's method proved to be the better option for my specific case. I did end up with a new problem which I'll post in a new thread since its a different type of formula most likely.

Thanks,
Ryan.

 

[Rick Rothstein]

Thanks everyone. @ ErikTyler's method proved to be the better option for my specific case. I did end up with a new problem which I'll post in a new thread since its a different type of formula most likely.

Okay, so that would mean you have a guaranteed maximum number of filenames that can possibly be in any one number sequence... so please tell us what that maximum number is?

 

[inactiveuserps07]

Hi, Rick. The OP included "...that vary from 2 product images to 6 product images." I went to eight, just in case.

It's a bit long, but it works. I'm sure there's some shorter way to do it.

 

[Rick Rothstein]

Hi, Rick. The OP included "...that vary from 2 product images to 6 product images."

I have got to learn to read better as I completely missed that.

 

[gr8rck]

I actually had a couple scenarios where it went to 10 but just manually added in the last 2 entries a few times. I think the formula might have re-arranged some of my ordering but not sure yet.

Anyhow, it was a great solution.

Thanks,

Ryan.