Data clubing Help

[krishhi]

Hello,

I have some sort of problem with clubing the data. I have attached a excel file for better explanation.

Waiting for your reply,

krrish

[Bob Phillips]

Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = Lastrow To 2 Step -1

If .Cells(i, "A").Value2 = "" Then

.Cells(i - 1, "B").Value2 = .Cells(i - 1, "B").Value2 & " " & .Cells(i, "B").Value2
.Rows(i).Delete
End If
Next i
End With

Application.ScreenUpdating = True
End Sub

[krishhi]

Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = Lastrow To 2 Step -1

If .Cells(i, "A").Value2 = "" Then

.Cells(i - 1, "B").Value2 = .Cells(i - 1, "B").Value2 & " " & .Cells(i, "B").Value2
.Rows(i).Delete
End If
Next i
End With

Application.ScreenUpdating = True
End Sub


XLD

Thanks for the reply, But I want the results as in excel file.

The "C" Column is the Results, "A" and "B" are the sources.


For example, I have Data in "A1" and "A5" ["A2,A3,A4" are blanks] and "B1 to B6". So B1&B2 ... B4 should be placed in "C1" then "B5 & B6" Should be placed in "C5" on the basis of value in "A5"

[krishhi]

Any help guys?

[krishhi]

Finally I got it Myself.

Xld your tips on Improving Programming really helps.

Just practice and Practice... [:)]
thanks bro.

here My code:

Sub Concatenate()
Dim i, lr As Long
Dim tmp As String
Dim k As Integer

Application.ScreenUpdating = False

lr = ActiveSheet.UsedRange.Rows.Count

For i = 1 To lr

If Range("A" & i).Value <> 0 Then
tmp = Range("B" & i).Value
k = i

Else

tmp = tmp & " " & Range("b" & i).Value
End If

Range("C" & k).Value = tmp


Next
tmp = ""

Application.ScreenUpdating = True

End Sub

[Bob Phillips]

Took me a while to figure out what you meant, but I think this is it



Public Sub ProcessData()
Dim Lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = Lastrow To 1 Step -1

If .Cells(i, "A").Value2 <> "" Then

If .Cells(i + 1, "A").Value2 <> "" Or i = Lastrow Then

.Cells(i, "C").Value2 = .Cells(i, "B").Value2
Else

.Cells(i, "C").Value2 = .Cells(i, "B").Value2 & " " & .Cells(i + 1, "C").Value2
.Cells(i + 1, "C").Value2 = ""
End If
Else

If .Cells(i + 1, "A").Value2 = "" Or i = Lastrow Then

.Cells(i, "C").Value2 = .Cells(i, "B").Value2 & " " & .Cells(i + 1, "C").Value2
.Cells(i + 1, "C").Value2 = ""
Else

.Cells(i, "C").Value2 = .Cells(i, "B").Value2
End If
End If
Next i
End With

Application.ScreenUpdating = True
End Sub

[krishhi]

Xld

Thank you very much.