post code & suburb concatenate

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

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" <varun@discussions.microsoft.com> wrote in message
news:2F3CEA4B-11DF-4772-8D92-F813542B2DA3@microsoft.com...
> 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

Thanks Phillips but I have no ideaabout VBA

is it possible to do in excel please let me know

thanks & appreciate your help

varun


"Bob Phillips" wrote:

> 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" <varun@discussions.microsoft.com> wrote in message
> news:2F3CEA4B-11DF-4772-8D92-F813542B2DA3@microsoft.com...
> > 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

>

It is probably possible, but very long-winded.

For the macro, just create a code module (Alt-F11, Insert>Module) ad copy
the code. Then go back to Excel, and run it, Tools>Macro>Macros, select
Test from the List, and hit the Run button. Voila.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"varun" <varun@discussions.microsoft.com> wrote in message
news:1DE8A300-0487-4B70-B922-022077BDDFC9@microsoft.com...
> Thanks Phillips but I have no ideaabout VBA
>
> is it possible to do in excel please let me know
>
> thanks & appreciate your help
>
> varun
>
>
> "Bob Phillips" wrote:
>
> > 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" <varun@discussions.microsoft.com> wrote in message
> > news:2F3CEA4B-11DF-4772-8D92-F813542B2DA3@microsoft.com...
> > > 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
>
> >