Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Combining several rows in one

  1. #1

    Combining several rows in one



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi, everyone!

    I have a big amount of data I over 1000 of rows and want to consolidate it, some of them need formating, while others don't.

    I have identical text in Columns 1,3 and 4, but the text in Column 2 is different for all.

    How do I make 1 row instead of 3 with Column 2 consolidated?

    Please see the attachment.

    Thanks !
    Attached Files Attached Files

  2. #2
    Conjurer Rudi's Avatar
    Join Date
    Jan 2014
    Location
    Cape Town, RSA
    Posts
    130
    Articles
    0
    Excel Version
    Excel 365 ProPlus

    Use Pivot Tables to get the result...

    Hi Caladbolg,

    A pivot table can consolidate the data exactly as you require it. No programming needed, no formulas needed
    Just select your data
    Activate the pivot table, and add the fields to the Rows drop area
    Switch the table format from Compact View to Tabular View
    Switch off sub totals for all the fields

    See attached workbook for result...

    Attachment 1963

  3. #3
    Thanks a lot! That may work, though is there a way to make the output result exactly as in the attachment i.e. no additional rows like rows 26 and 27 and letters A, B and C are in one cell?

  4. #4
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Hello Caladbolg17

    I have absolutely no idea how this can be done with a formula but if you are open to a macro have a look at the attached workbook.

    I have your data being on sheet1 and put the results on sheet2.

    You should also have a look at this http://www.excelguru.ca/content.php?184 because cross posting can be done in a proper manner.
    Attached Files Attached Files

  5. #5
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    minor adjustment in case active sheet is not sheet1 when macro is run

    change
    Code:
    'determine the range to work with
    Set rng = Sheets("Sheet1").Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    to
    Code:
    'determine the range to work with
    With Sheets("Sheet1")
        Set rng = .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
    End With

  6. #6
    Wow! That's totally awesome!
    Though I found out that column 3 also has different values, what should I change in macros for Column 3 to also consolidate in one cell, like Column 2?

    Thanks for the info about cross posting, I will keep that in mind.

  7. #7
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    add another line where match is found, similar to what is already there but for column "C" and offset (0,2)

    Hope that works for you.

  8. #8
    Thanks, now the other column consolidates as well
    Though some of the rows just disappear after the macro is run
    Last edited by Caladbolg17; 2014-01-19 at 03:57 PM.

  9. #9
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Disappear from where?
    I'd like to see that. Can you attach the workbook where that happens?

  10. #10
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    After playing around with this I think "disappear" may refer to nothing being added to sheet2 when the macro is run a second time.
    Try this
    Attached Files Attached Files

Page 1 of 2 1 2 LastLast

Posting Permissions

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