Excel Manual Merging without losing data

[joceychin]

Hi

in MS word, we can merge table cells without losing any data. However,
can we do that in Excel too ?

I cannot use any formular as i have variable no of rows to merger.
I have to review the rows before deciding which are the rows i want to
merge.
i have hundreds of rows to review and so it will be nice if i can find
a way to merge without losing data in the subsequent rows.

for some advice please.
thank you
Jocey

 

[Ron Rosenfeld]

Hi

in MS word, we can merge table cells without losing any data. However,
can we do that in Excel too ?

I cannot use any formular as i have variable no of rows to merger.
I have to review the rows before deciding which are the rows i want to
merge.
i have hundreds of rows to review and so it will be nice if i can find
a way to merge without losing data in the subsequent rows.

for some advice please.
thank you
Jocey

MERGE has a very specific meaning in Excel that does not include retaining data other than in the upper left. It also presents issues in subsequent access to data.

What, exactly, do you want to do? If it is a matter of combining data in multiple rows, or hiding some data, that can be done; and usually automated by using VBA.

 

[joceychin]

MERGE has a very specific meaning in Excel that does not include retaining data other than in the upper left.  It also presents issues in subsequent access to data.

What, exactly, do you want to do?  If it is a matter of combining data in multiple rows, or hiding some data, that can be done; and usually automated by using VBA.

i was thinking of automating this tasks too. However, there is no
specific pattern of "merging" the rows.
the number of rows to merge is not fix. Someone is going to review the
rows manually first.
The task we are doing is more for documentation, and the merging is to
improve readability.
unless there is a way to write a macro to identify the selected cell
and then combine all the data together.
I do not know how to do it though.

 

[Ron Rosenfeld]

i was thinking of automating this tasks too. However, there is no
specific pattern of "merging" the rows.
the number of rows to merge is not fix. Someone is going to review the
rows manually first.
The task we are doing is more for documentation, and the merging is to
improve readability.
unless there is a way to write a macro to identify the selected cell
and then combine all the data together.
I do not know how to do it though.

If the person examining the cells uses clearly defined rules to determine which to combine, then yes, it can be automated. If the rules are somewhat vague, perhaps based on similarities rather than equivalences, then any algorithm becomes much more complex, and out of my area of competence.

However, to at least partially automate the process of combining data, the reviewer could select those cells to be combined, and a macro could then be run to combine the text in those cells, with a suitable delimiter, and write the result to some other cell (or even overwrite an existing cell). The particulars depend critically on more detail than you have provided, but here is an example that might be helpful:

========================
Option Explicit
Sub CombineCells()
Dim rSrc As Range, c As Range
Dim v() As String
Dim i As Long

Set rSrc = Selection
ReDim v(1 To rSrc.Count)
For Each c In rSrc
i = i + 1
v(i) = c.Text
Next c

Debug.Print Join(v, " ")

End Sub
========================

As written, the output is written to the Immediate Window in the VBE, but it could equally well be written to some cell on any worksheet.

 

[isabelle]

hi,

For Each c In Selection
If Not c = Empty Then txt = txt & " " & c
Next
With Selection
.ClearContents
.MergeCells = True
.WrapText = True
.ShrinkToFit = True
.VerticalAlignment = xlTop
End With
Selection = txt


--
isabelle


Le 2011-12-08 00:03, (e-mail address removed) a écrit :
It also presents issues in subsequent access to data.
that can be done; and usually automated by using VBA.