Converting multiple rows into a single rows

[dogger]

I have a list of clients with account numbers. If the client has more than one account they are listed in another row.

For example
David 123456
David 654321
David 789456

I want to consolidate the accounts into a single row so that it would have name, account 1, account 2 etc... where the account numbers are in seperate columns.
David 123456, 654321,789456

I hope this makes sense.

Thanks

 

[Krishnakumar]

Hi,

Have a look...

Book2
	A	B	C	D	E
1	David	123456		David	123456, 654321, 789456
2	David	654321
3	David	789456
4
Sheet1

Formula in E1,

=aconcat(IF(A1:A3=D1,B1:B3),", ")

Confirmed with Ctrl+Shift+Enter

ACONCAT is a UDF.

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
    Dim y As Variant
    
    If TypeOf a Is Range Then
        For Each y In a.Cells
            aconcat = aconcat & y.Value & sep
        Next y
    ElseIf IsArray(a) Then
        For Each y In a
            aconcat = aconcat & y & sep
        Next y
    Else
        aconcat = aconcat & a & sep
    End If
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

HTH

 

[dogger]

Thanks for the reply.

Looking back on my original post, I was not very clear on my situation.

I have about 3000 lines (account numbers), roughly 600 different client names and some may only have one account while others have up to 12. I have additional data that is repeated on every line for that specific client - address, city, state, Social Security number - that I would like have shown just once along with the name and account numbers.

What I am hoping to do is create a mail merge that will allow me to address the letter, list the account numbers and put their social security number under a signature line.

I appreciate you help.

 

[dogger]

I split the task up with the folks in the office and we completed it manually. Thanks for your time.