Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Combine cells from a row to single column when item name changes
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2017
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  2. #2
    Banned user
    Join Date
    May 2017
    Posts
    748
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

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

    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.
    Last edited by inactiveuserps07; Jun 11th, 2017 at 05:18 PM.

  3. #3
    New Member
    Join Date
    Jan 2017
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Thanks.

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

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,249
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

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

    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...
    Code:
    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
    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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    Banned user
    Join Date
    May 2017
    Posts
    748
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  6. #6
    New Member
    Join Date
    Jan 2017
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,249
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

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

    Quote Originally Posted by gr8rck View Post
    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?
    Last edited by Rick Rothstein; Jun 12th, 2017 at 12:36 AM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    Banned user
    Join Date
    May 2017
    Posts
    748
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,249
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

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

    Quote Originally Posted by ErikTyler View Post
    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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  10. #10
    New Member
    Join Date
    Jan 2017
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •