I don't think you can do it without code:

Try this .... input Sheet1, output Sheet2


Dim r As Long, rr As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Dim constr As String

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

rr = 1
With ws1
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
constr = ""
For r = 2 To lastrow
If .Cells(r, "a") = .Cells(r + 1, "A") Then
constr = constr & .Cells(r, "B") & ","
Else
constr = constr & .Cells(r, "B") & ","
rr = rr + 1
ws2.Cells(rr, 1) = .Cells(r, "A")
ws2.Cells(rr, 2) = Left(constr, Len(constr) - 1)
constr = ""
End If
Next r
End With

HTH

"sebastian stephenson" wrote:

I have set of data

Key Text
1 x
1 y
1 z
2 a
2 b
3 c
3 d

I want to use a function that will return

Key Text
1 x,y,z
2 a,b
3 c,d

I apply the formula:

VlookUp() to the first data set and I only receive the following output

Key Text newText
1 x
2 a
3 c

Does anyone know of a different formula I can use inplace of VLookUp()??

I have tried to apply the following code from a previous example, but this
gives the same results:

=INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=E3,ROW($A$1: $A$30)),ROW(A1)))

Can anybody help?


--
Learning SQL and Access