FORMULA TO COMBINE MULTIPLES CELLS

HI All,

May i know any formula i can apply for following beside CONCATENATE & macro?

Attachment 23178

Result to appear as --> 2 ANDY, 5 SARA, 1 KIKI, 1 LALA
In some cases, row 1 might be blank. So if Row 1 are blank. The result will be 5 SARA, 1 KIKI, 1 LALA

Really hope to get help on this.
TIA

Hi cyee
Here a sample code.
you can call this Sub from a commandButton for example.

Code:

---

Sub Concat()
Dim I, S
S = ""
For I = 1 To 6
    If Not IsEmpty(Cells(I, 1)) And Not IsEmpty(Cells(I, 2)) Then
        S = S & Cells(I, 1) & " " & Cells(I, 2) & ", "
    End If
Next I
MsgBox S
End Sub

---