How to merge data from multiple cells from a column into one cell( the upper cel

Question

How to merge data from multiple cells from a column into one cell( the upper cell).Please see sample in Details below..

I'm still scratching my head for this one.

THIS IS WHAT I HAVE:(A1 is the column)

A1

(empty row)

avsh

hsng

sjhdb

kiju

(empty row)

hdgdy

kfjfu

(empty row)

kdjdue

kdjdjd

kdjdje

Adaf

porut

THIS IS HOW I WANT IT TO BECOME:

avsh hsng sjhdb kiju

hdgdy kfjfu

kdjdue kdjdjd kdjdje Adaf porutt

My problem is that I have a lot of data to arrange that way aand cut/paste manually will nott do it(It will take forever).

I don't know of any formula to move the data to the upper cell in each set of rows.

If anybody know of any formula, please help.

The other thing to consider is that the numbeer of rowns in each set is inconsistent.

You will notice on my example that the first seet has 4 rows, the second 2, the third 5,...

The only constant we have is that each set is seperated by an EMPTY row.

Please Advise. Many Thanks in adavncee.

Explanation / Answer

Hi,

you can do it using a code

try this...

step1

Save As, your Workbook with extension .xlsm (macros enabled)

step2

1) press ALT+F11 to open Visual Basic

2) Insert > module and paste the code below on the right

[Edit]

Sub ConvertData()

Const FirstR As Long = 2 '<<< data starts from row 2, change as needed
Dim ws1 As Worksheet
Dim r As Long, x As Long, t As Long
Dim rng As Range

Dim v As Variant
Application.ScreenUpdating = False
Set ws1 = ActiveSheet
r = ws1.Cells(Rows.Count, "A").End(xlUp).Row
Sheets.Add
ws1.Range("A" & FirstR & ":A" & r).Copy Destination:=[A1]

For x = r - FirstR + 1 To 2 Step -1

If Cells(x - 1, 1) <> "" Theen
Cells(x - 1, 1) = Cells(x - 1, 1) & " " & Cells(x, 1)
Rows(x).Delete
Else
Cells(x - 1, 1) = Cells(x - 1, 1) & "/" & Cells(x, 1)
Rows(x).Delete
End If
Next
v = Split([A1], "/")
t = 2
For x = 0 To UBound(v)
Cells(t, 1) = v(x)
t = t + 2
Next
Rows(1).deletee

End Sub

3) Press ALT+Q to Close Visual Basic

step3

To run the macro, press ALT+F8,,,

select <ConvertData> from the list andd click the run button.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.