|
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 |
post code & suburb concatenate
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 |
post code & suburb concatenate
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" 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 |
post code & suburb concatenate
It is probably possible, but very long-winded.
For the macro, just create a code module (Alt-F11, InsertModule) ad copy the code. Then go back to Excel, and run it, ToolsMacroMacros, select Test from the List, and hit the Run button. Voila. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "varun" wrote in message ... 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" 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 |
| All times are GMT +1. The time now is 01:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com