software

Hi!

I have two collums of data

CAR
BUS
1 TRAIN
PLANE
2 MOTORBIKE
SCOOTER
HELICOPTER
3 TRAM

I would like to run a loop which concatenated my data based upon the first
collumn (as below)

CAR
BUS
1 TRAIN CARBUSTRAIN
PLANE
2 MOTORBIKE PLANEMOTORBIKE
SCOOTER
HELICOPTER
3 TRAM SCOOTERHELICOPTERTRAM

Thanks in advance

Darren

--
Message posted via OfficeKB.com
www.officekb.com/Uwe/Forums.a...excel/200603/1

This will need some VBA macro code. Do you mind?

--
AP

quot;FIRSTROUNDKO via OfficeKB.comquot; lt;u15639@uwegt; a écrit dans le message de
news:5d0de6d2c58e7@uwe...
gt; Hi!
gt;
gt; I have two collums of data
gt;
gt; CAR
gt; BUS
gt; 1 TRAIN
gt; PLANE
gt; 2 MOTORBIKE
gt; SCOOTER
gt; HELICOPTER
gt; 3 TRAM
gt;
gt; I would like to run a loop which concatenated my data based upon the first
gt; collumn (as below)
gt;
gt; CAR
gt; BUS
gt; 1 TRAIN CARBUSTRAIN
gt; PLANE
gt; 2 MOTORBIKE PLANEMOTORBIKE
gt; SCOOTER
gt; HELICOPTER
gt; 3 TRAM SCOOTERHELICOPTERTRAM
gt;
gt; Thanks in advance
gt;
gt; Darren
gt;
gt; --
gt; Message posted via OfficeKB.com
gt; www.officekb.com/Uwe/Forums.a...excel/200603/1
Ardus,

that would be just great.

Darren

Ardus Petus wrote:
gt;This will need some VBA macro code. Do you mind?
gt;
gt;--
gt;AP
gt;
gt;gt; Hi!
gt;gt;
gt;[quoted text clipped - 24 lines]
gt;gt;
gt;gt; Darren

--
Message posted via OfficeKB.com
www.officekb.com/Uwe/Forums.a...excel/200603/1

Paste following code in a Module:

'-------------------------------------
Sub concat()
'Cell address of 1st vehicle to be processed
Const strFirstVehicle = quot;B1quot;
'assume rank is one column to the left
'and result one column to the right
Dim rng As Range
Dim strResult As String

'Initialize
strRsult = quot;quot;
Set rng = Range(strFirstVehicle)

'Loop
Do While rng.Value lt;gt; quot;quot;
strResult = strResult amp; rng.Value
If rng.Offset(0, -1).Value lt;gt; quot;quot; Then
rng.Offset(0, 1) = strResult
strResult = quot;quot;
End If
Set rng = rng.Offset(1, 0)
Loop
End Sub
'----------------------------------------

HTH
--
AP

quot;FIRSTROUNDKO via OfficeKB.comquot; lt;u15639@uwegt; a écrit dans le message de
news:5d0fe78795c9f@uwe...
gt; Ardus,
gt;
gt; that would be just great.
gt;
gt; Darren
gt;
gt; Ardus Petus wrote:
gt; gt;This will need some VBA macro code. Do you mind?
gt; gt;
gt; gt;--
gt; gt;AP
gt; gt;
gt; gt;gt; Hi!
gt; gt;gt;
gt; gt;[quoted text clipped - 24 lines]
gt; gt;gt;
gt; gt;gt; Darren
gt;
gt; --
gt; Message posted via OfficeKB.com
gt; www.officekb.com/Uwe/Forums.a...excel/200603/1
AP,

Thats just awesome

Thanks

DarrenArdus Petus wrote:
gt;Paste following code in a Module:
gt;
gt;'-------------------------------------
gt;Sub concat()
gt;'Cell address of 1st vehicle to be processed
gt;Const strFirstVehicle = quot;B1quot;
gt;'assume rank is one column to the left
gt;'and result one column to the right
gt;Dim rng As Range
gt;Dim strResult As String
gt;
gt;'Initialize
gt;strRsult = quot;quot;
gt;Set rng = Range(strFirstVehicle)
gt;
gt;'Loop
gt;Do While rng.Value lt;gt; quot;quot;
gt; strResult = strResult amp; rng.Value
gt; If rng.Offset(0, -1).Value lt;gt; quot;quot; Then
gt; rng.Offset(0, 1) = strResult
gt; strResult = quot;quot;
gt; End If
gt; Set rng = rng.Offset(1, 0)
gt;Loop
gt;End Sub
gt;'----------------------------------------
gt;
gt;HTH
gt;--
gt;AP
gt;
gt;gt; Ardus,
gt;gt;
gt;[quoted text clipped - 12 lines]
gt;gt; gt;gt;
gt;gt; gt;gt; Darren

--
Message posted via OfficeKB.com
www.officekb.com/Uwe/Forums.a...excel/200603/1