ExcelBanter - Merging cells

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Merging cells (https://www.excelbanter.com/excel-programming/334717-merging-cells.html)

I have a spreadsheet where I would like to merge the cells in each row
into one cell, excluding the first three columns.
So columns A to C I don’t want to merge.
However, I would like to merge C1 to M1 into one cell in row one and so
on, for example.
The problem is that in each row the number of cells that have data in
them to be merged will vary.
So I’m asking for a formula that is capable to search each row and
merge all data, after column C.
For each merged cell I would like to have some sort of separator, say a
comma, to distinguish the merged cells.
I hope that this hasn’t been asked before, or that this hasn’t been too
confusing.
Thanks for any help

--
sonic
------------------------------------------------------------------------
sonic's Profile: http://www.excelforum.com/member.php...o&userid=23060
View this thread: http://www.excelforum.com/showthread...hreadid=387698

if you are open to the idea of a macro advise

--
duane

------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=387698

please, if you can,
thank you

--
sonic
------------------------------------------------------------------------
sonic's Profile: http://www.excelforum.com/member.php...o&userid=23060
View this thread: http://www.excelforum.com/showthread...hreadid=387698

this concatenates column d and on for each row (with a " , " between
pieces) and then inserts result in column d

i guess you could insert column d first, concatenate columns e and o,
and eliminate the 2nd loop.

Sub concat()
'istring(i) are the concatenated strings
' assume raw data starts in row 1 and is 1000 rows max
Dim istring(1000) As String
erow = Cells(1, 1).End(xlDown).Row
For i = 1 To erow
istring(i) = ""
icol = Cells(i, 1).End(xlToRight).Column
For j = 4 To icol
If j = 4 Then istring(i) = Cells(i, j).Value Else _
istring(i) = istring(i) & " , " & Cells(i, j).Value
Next j
Next i
Cells(1, 4).EntireColumn.Insert
For i = 1 To erow
Cells(i, 4) = istring(i)
Next i
Columns("d:d").Columns.AutoFit
End Sub

--
duane

------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=387698

Thank you very much!!!
That was stunning
It did just what I neede

--
soni
-----------------------------------------------------------------------
sonic's Profile: http://www.excelforum.com/member.php...fo&userid=2306
View this thread: http://www.excelforum.com/showthread.php?threadid=38769

Sorry, I probably didn’t explain myself properly.
After further testing, I have discovered that the script stops as soon
as it reaches an empty cell.
I have realised now that sometimes there can be data in a row, after an
empty cell.
Is it possible for the macro to keep searching the entire row, until it
finds all data within that row, even if it hits an empty cell and keeps
going?

If it makes it any easier, can it keep searching to say, column 100?
That would be Plenty.

Thanks again

--
sonic
------------------------------------------------------------------------
sonic's Profile: http://www.excelforum.com/member.php...o&userid=23060
View this thread: http://www.excelforum.com/showthread...hreadid=387698

Change the row that reads.....

erow = Cells(1, 1).End(xlDown).Row

to......

erow = Cells(Rows.Count,1).End(xlup).Row

--
Cheers
Nigel

"sonic" wrote in
message ...

Sorry, I probably didn't explain myself properly.
After further testing, I have discovered that the script stops as soon
as it reaches an empty cell.
I have realised now that sometimes there can be data in a row, after an
empty cell.
Is it possible for the macro to keep searching the entire row, until it
finds all data within that row, even if it hits an empty cell and keeps
going?

If it makes it any easier, can it keep searching to say, column 100?
That would be Plenty.

Thanks again

--
sonic
------------------------------------------------------------------------
sonic's Profile:
http://www.excelforum.com/member.php...o&userid=23060
View this thread: http://www.excelforum.com/showthread...hreadid=387698

I have modified the code from duane to the following, hope this helps.

1. It removes the 1000 row limit (and will use less memory in the process)
2. Will allow empty rows and columns to exist within the extent of the range
3. Ignores empty columns by stepping over them (does not add a comma after
empty cell)

Sub concat()
'istring is a concatenated strings
' assumes raw data starts in row 1
Dim istring As String, i As Long, j As Integer, erow As Long, icol As
Integer
erow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(1, 4).EntireColumn.Insert
For i = 1 To erow
istring = ""
icol = Cells(i, Columns.Count).End(xlToLeft).Column
For j = 5 To icol
If j = 5 Then istring = Cells(i, j).Value Else _
If Len(Trim(Cells(i, j))) 0 Then istring = istring & " , "
& Cells(i, j).Value
Next j
Cells(i, 4) = istring
Next i
Columns(4).Columns.AutoFit
End Sub

--
Cheers
Nigel

"sonic" wrote in
message ...

Sorry, I probably didn't explain myself properly.
After further testing, I have discovered that the script stops as soon
as it reaches an empty cell.
I have realised now that sometimes there can be data in a row, after an
empty cell.
Is it possible for the macro to keep searching the entire row, until it
finds all data within that row, even if it hits an empty cell and keeps
going?

If it makes it any easier, can it keep searching to say, column 100?
That would be Plenty.

Thanks again

--
sonic
------------------------------------------------------------------------
sonic's Profile:
http://www.excelforum.com/member.php...o&userid=23060
View this thread: http://www.excelforum.com/showthread...hreadid=387698

Hi, A further refinement to overcome the problem of the first column being
empty, thus preventing the string beginning with a comma.

Sub concat()
'istring is a concatenated strings
' assumes raw data starts in row 1
Dim istring As String, i As Long, j As Integer, erow As Long, icol As
Integer
erow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(1, 4).EntireColumn.Insert
For i = 1 To erow
istring = ""
icol = Cells(i, Columns.Count).End(xlToLeft).Column
For j = 5 To icol
If Len(Trim(Cells(i, j).Value)) 0 Then
If Len(istring) = 0 Then
istring = Trim(Cells(i, j).Value)
Else
istring = istring & " , " & Trim(Cells(i, j).Value)
End If
End If
Next j
Cells(i, 4) = istring
Next i
Columns(4).Columns.AutoFit
End Sub

--
Cheers
Nigel

"Nigel" wrote in message
...
I have modified the code from duane to the following, hope this helps.

1. It removes the 1000 row limit (and will use less memory in the process)
2. Will allow empty rows and columns to exist within the extent of the
range
3. Ignores empty columns by stepping over them (does not add a comma after
empty cell)

Sub concat()
'istring is a concatenated strings
' assumes raw data starts in row 1
Dim istring As String, i As Long, j As Integer, erow As Long, icol As
Integer
erow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(1, 4).EntireColumn.Insert
For i = 1 To erow
istring = ""
icol = Cells(i, Columns.Count).End(xlToLeft).Column
For j = 5 To icol
If j = 5 Then istring = Cells(i, j).Value Else _
If Len(Trim(Cells(i, j))) 0 Then istring = istring & " ,
"
& Cells(i, j).Value
Next j
Cells(i, 4) = istring
Next i
Columns(4).Columns.AutoFit
End Sub

--
Cheers
Nigel

"sonic" wrote in
message ...

Sorry, I probably didn't explain myself properly.
After further testing, I have discovered that the script stops as soon
as it reaches an empty cell.
I have realised now that sometimes there can be data in a row, after an
empty cell.
Is it possible for the macro to keep searching the entire row, until it
finds all data within that row, even if it hits an empty cell and keeps
going?

If it makes it any easier, can it keep searching to say, column 100?
That would be Plenty.

Thanks again

--
sonic
------------------------------------------------------------------------
sonic's Profile:
http://www.excelforum.com/member.php...o&userid=23060
View this thread:
http://www.excelforum.com/showthread...hreadid=387698

Hi Nigel,
Thanks so much for your time and effort.
That code is exactly what I needed.

Duane,
Thank you to you too again also.

I would have never been able to do this without the both of you.
Cheers

--
sonic
------------------------------------------------------------------------
sonic's Profile: http://www.excelforum.com/member.php...o&userid=23060
View this thread: http://www.excelforum.com/showthread...hreadid=387698

you are welcome, hth. I did it quick - nice refinements by Nigel

--
duan

-----------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...fo&userid=1162
View this thread: http://www.excelforum.com/showthread.php?threadid=38769