# Thread: Combining several rows in one

1. ## Combining several rows in one

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?

Thanks !

2. ## Use Pivot Tables to get the result...

A pivot table can consolidate the data exactly as you require it. No programming needed, no formulas needed
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. 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?

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.

5. 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. 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. 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. Thanks, now the other column consolidates as well
Though some of the rows just disappear after the macro is run

9. Disappear from where?
I'd like to see that. Can you attach the workbook where that happens?

10. 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

Page 1 of 2 1 2 Last

#### Posting Permissions

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