This is better suited to VBA

Option Explicit

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim iPrev As Long
Dim PrevValue
Dim rng As Range

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If Cells(i, "A").Value = PrevValue Then
Cells(iPrev, "C").Value = Cells(iPrev, "C").Value & _
", " + Cells(i, "B").Value
If rng Is Nothing Then
Set rng = Rows(i)
Else
Set rng = Union(rng, Rows(i))
End If
Else
iPrev = i
PrevValue = Cells(i, "A").Value
Cells(i, "C").Value = Cells(i, "A").Value & _
" " + Cells(i, "B").Value
End If
Next i

If Not rng Is Nothing Then
rng.Delete
End If
Columns("A:B").Delete

Set rng = Nothing

End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"varun" wrote in message
...
Can you Please help
Question
column1 has list of various post codes
column2 has name of the suburbs
eg
4006 Bowen Hills
4006 Herston
4006 Newstead
4006 Fortitude Valley
4007 Hamilton
4007 Ascot
4008 Pinkenba
4009 Eagle Farm
4010 Mayne
4010 Albion
4011 Eagle Junction
4011 Hendra
4011 Clayfield

can you please help me with a formula in excel which gives the result as
follow

4006 Bowen Hills, Herston ,Newstead ,Fortitude Valley
4007 Hamilton, Ascot
4008 Pinkenba
4009 Eagle Farm
4010 Mayne, Albion
4011 Eagle Junction, Hendra ,Clayfield

Thank you

varun