Concatenate info from columns into rows
 

Hie, can anyone help with the following:
COL A COL B COL C COL D
Unit 1 Unit 1 3B Unit 1: 3B, 5LH, 6RH
Unit 2 Unit 1 5LH Unit 2: 4B, 6LH, 7RH
Unit 3 Unit 1 6RH Unit 3: 5B, 4LH, 9RH
Unit 4 Unit 2 4B Unit 4
Unit 2 6LH
Unit 2 7RH
Unit 3 5B
Unit 3 4LH
Unit 3 9RH
Unit 4

What i've got are 4 unique units, each of which have a handing assigned to
them.
I need to concatenate the info in each column against each unique unit,
which gives the info on one row for each unit (COL D).
This is just an example. I may have unique units which have no handing info
against them (e.g. Unit 4) and therefore will appear on there own in COL D
with no handing info against them etc.

A macro to automate this process would be greatly appreciated.
Thanks
--
jj

Concatenate info from columns into rows
 

Sub combinerows()

RowCountColA = 1
RowCountColD = 1
Do While Range("A" & RowCountColA) < ""
UnitNo = Range("A" & RowCountColA)
RowCountColB = 1
CombinedString = UnitNo & ": "
MyString = CombinedString
Do While Range("B" & RowCountColB) < ""
If Range("B" & RowCountColB) = UnitNo Then
'need to see if init string so a comma can be added
If MyString = CombinedString Then
MyString = MyString & Range("C" & RowCountColB)
Else
MyString = MyString & "," & Range("C" & RowCountColB)
End If
End If
RowCountColB = RowCountColB + 1
Loop
RowCountColA = RowCountColA + 1
Range("D" & RowCountColD) = MyString
RowCountColD = RowCountColD + 1

Loop

End Sub


"jbjtc" wrote:

Hie, can anyone help with the following:
COL A COL B COL C COL D
Unit 1 Unit 1 3B Unit 1: 3B, 5LH, 6RH
Unit 2 Unit 1 5LH Unit 2: 4B, 6LH, 7RH
Unit 3 Unit 1 6RH Unit 3: 5B, 4LH, 9RH
Unit 4 Unit 2 4B Unit 4
Unit 2 6LH
Unit 2 7RH
Unit 3 5B
Unit 3 4LH
Unit 3 9RH
Unit 4

What i've got are 4 unique units, each of which have a handing assigned to
them.
I need to concatenate the info in each column against each unique unit,
which gives the info on one row for each unit (COL D).
This is just an example. I may have unique units which have no handing info
against them (e.g. Unit 4) and therefore will appear on there own in COL D
with no handing info against them etc.

A macro to automate this process would be greatly appreciated.
Thanks
--
jj

Concatenate info from columns into rows
 

Thanx Joel
It works a treat
--
jj


"Joel" wrote:

Sub combinerows()

RowCountColA = 1
RowCountColD = 1
Do While Range("A" & RowCountColA) < ""
UnitNo = Range("A" & RowCountColA)
RowCountColB = 1
CombinedString = UnitNo & ": "
MyString = CombinedString
Do While Range("B" & RowCountColB) < ""
If Range("B" & RowCountColB) = UnitNo Then
'need to see if init string so a comma can be added
If MyString = CombinedString Then
MyString = MyString & Range("C" & RowCountColB)
Else
MyString = MyString & "," & Range("C" & RowCountColB)
End If
End If
RowCountColB = RowCountColB + 1
Loop
RowCountColA = RowCountColA + 1
Range("D" & RowCountColD) = MyString
RowCountColD = RowCountColD + 1

Loop

End Sub


"jbjtc" wrote:

Hie, can anyone help with the following:
COL A COL B COL C COL D
Unit 1 Unit 1 3B Unit 1: 3B, 5LH, 6RH
Unit 2 Unit 1 5LH Unit 2: 4B, 6LH, 7RH
Unit 3 Unit 1 6RH Unit 3: 5B, 4LH, 9RH
Unit 4 Unit 2 4B Unit 4
Unit 2 6LH
Unit 2 7RH
Unit 3 5B
Unit 3 4LH
Unit 3 9RH
Unit 4

What i've got are 4 unique units, each of which have a handing assigned to
them.
I need to concatenate the info in each column against each unique unit,
which gives the info on one row for each unit (COL D).
This is just an example. I may have unique units which have no handing info
against them (e.g. Unit 4) and therefore will appear on there own in COL D
with no handing info against them etc.

A macro to automate this process would be greatly appreciated.
Thanks
--
jj