combine/merge row cells text between blank rows

[wapo]

Wondering if someone could help with the following problem. I have a large list of text in one column which i need to combine between blank rows into one cell or a new column, the number of rows to be combined varies. for example

aa
bb

ww
xx
zz

rr

gg
hh
ii
jj


would become

aa bb
ww xx zz
rr
gg hh ii jj

I have over 30000 lines so doing it by hand is not an option.
Thanks for any help or suggestions.

 

[skippybeans]

Is it possible to combine an unknown number of rows from column A on sheet 1 of a spreadsheet into a single cell of sheets 2 of the same spreadsheet. Also, to do this so that each row from the column A is separated with a comma (,) and a space IF there is text present, but no comma if there is NO text present. I've used the CONCATENATE funtction in excel to do this but am unsure of how to NOT include a bunch of commas and spaces if there is no text in the cell in column A.

Thanks!

 

[hiker95]

skippybeans,

Welcome to the MrExcel forum.

Is it possible to combine an unknown number of rows from column A on sheet 1 of a spreadsheet into a single cell of sheets 2 of the same spreadsheet. Also, to do this so that each row from the column A is separated with a comma (,) and a space IF there is text present, but no comma if there is NO text present. I've used the CONCATENATE funtction in excel to do this but am unsure of how to NOT include a bunch of commas and spaces if there is no text in the cell in column A.

Probably yes, but, your raw data and results are probably not like wapo's.


Please do not post your questions in threads started by others - - this is known as thread hijacking.
Always start a new thread for your questions and, if you think it helps, provide a link to any other thread as a reference.
Start a new thread for your question and be sure to give it a search friendly title that accurately describes your need.


In your New Thread, include:

What version of Excel are you using?

An appropriate/informative title and instructions.

And, in your New Thread, please post a screenshot of the raw data worksheet, and, post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

 

[waterL]

Hi hiker,

I am new here and I don't know anything about macro, but I randomly found this forum yesterday and applied the macro to do my work, the result is exactly what I need! Can you please help me to modify the macro so that the combined cell would align with the last row instead of the first row?

Thank you very much!

 

[Rick Rothstein]

Is it possible to combine an unknown number of rows from column A on sheet 1 of a spreadsheet into a single cell of sheets 2 of the same spreadsheet. Also, to do this so that each row from the column A is separated with a comma (,) and a space IF there is text present, but no comma if there is NO text present. I've used the CONCATENATE funtction in excel to do this but am unsure of how to NOT include a bunch of commas and spaces if there is no text in the cell in column A.

See my mini-blog article here for code, and instructions on how to install/use it, that I think will do what you want...

Flexible Concatenation Function

 

[Rick Rothstein]

Though I realise you're happy with the excellent solution hiker95 has provided, I thought I'd tweak my attempt as well if nothing else but to see how two different people approach the same problem:

With Robert's idea of showing you how different people approach the same problem in mind, this is how I would do it...

Sub CombineData()
  Dim A As Range
  For Each A In Columns("A").SpecialCells(xlConstants).Areas
    If A.Count = 1 Then
      A(1).Offset(, 1).Value = A.Value
    Else
      A(1).Offset(, 1).Value = Join(Application.Transpose(A.Value), " ")
    End If
  Next
End Sub

 

[Rick Rothstein]

With Robert's idea of showing you how different people approach the same problem in mind, this is how I would do it...

Sub CombineData()
  Dim A As Range
  For Each A In Columns("A").SpecialCells(xlConstants).Areas
    If A.Count = 1 Then
      A(1).Offset(, 1).Value = A.Value
    Else
      A(1).Offset(, 1).Value = Join(Application.Transpose(A.Value), ", ")
    End If
  Next
End Sub

If you ever follow any of my postings, you will find that I favor tight, compact code. With that in mind, here is the above code compacted slightly...

Sub CombineData()
  Dim A As Range
  For Each A In Columns("A").SpecialCells(xlConstants).Areas
    A(1).Offset(, 1).Value = Join(Application.Transpose(A.Resize(A.Count + 1).Value), " ")
  Next
End Sub

 

[hiker95]

waterL,

Thanks for the confidence.

If your sample raw data looks like this:

Excel 2007
	A	B
1	aa
2	bb
3
4	ww
5	xx
6	zz
7
8	rr
9
10	gg
11	hh
12	ii
13	jj
14
Sheet1

After the updated macro we get this:

Excel 2007
	A	B
1	aa
2	bb	aa bb
3
4	ww
5	xx
6	zz	ww xx zz
7
8	rr	rr
9
10	gg
11	hh
12	ii
13	jj	gg hh ii jj
14
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub CombineTextV2()
' hiker95, 07/04/2013
' http://www.mrexcel.com/forum/excel-questions/396357-combine-merge-row-cells-text-between-blank-rows-2.html
Dim Area As Range, sr As Long, er As Long
Application.ScreenUpdating = False
For Each Area In Range("A1", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
  With Area
    sr = .Row
    er = sr + .Rows.Count - 1
    If sr = er Then
      Range("B" & er) = Range("A" & er)
    Else
      Range("B" & er) = Join(Application.Transpose(Range("A" & sr & ":A" & er).Value), " ")
    End If
  End With
Next Area
Columns("A:B").AutoFit
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the CombineTextV2 macro.


If you have a lot of raw data, and, the above macro does not work correctly, I can re-write the macro using an array in memory.

 

[Rick Rothstein]

I am new here and I don't know anything about macro, but I randomly found this forum yesterday and applied the macro to do my work, the result is exactly what I need! Can you please help me to modify the macro so that the combined cell would align with the last row instead of the first row?

Here is the code I posted in Message #16 (for the original question) modified to output the combined text next to the last cell in each "group" as you have requested...

Sub CombineData()
  Dim A As Range
  For Each A In Columns("A").SpecialCells(xlConstants).Areas
    A(A.Count).Offset(, 1).Value = Join(Application.Transpose(A.Resize(A.Count + 1).Value), " ")
  Next
End Sub

 

[hiker95]

waterL,

If you have a lot of raw data (the raw data in my last reply #17 range A1:A14 copied down to row 22,399), the following macro using an array in memory is fast.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub CombineTextV3()
' hiker95, 07/04/2013
' http://www.mrexcel.com/forum/excel-questions/396357-combine-merge-row-cells-text-between-blank-rows-2.html
Dim a As Variant, i As Long, h As String
Columns(2).ClearContents
a = Cells(1).Resize(Cells(Rows.Count, 1).End(xlUp).Row + 1, 2)
For i = 1 To UBound(a, 1)
  If a(i, 1) <> "" Then
    h = h & a(i, 1) & " "
  ElseIf a(i, 1) = "" Then
    If Right(h, 1) = " " Then h = Left(h, Len(h) - 1)
    a(i - 1, 2) = h
    h = ""
  End If
Next i
Cells(1).Resize(UBound(a, 1), UBound(a, 2)) = a
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the CombineTextV3 macro.

 

[waterL]

hiker95 & Rick,

I have tried all of them, and they work perfectly!
The task original took a weeks to finish if done manually, but now it only needs one button!
I hope I can understand the logic of these code soon in the future.

Thank you very much!

Regards,
waterL