I am having trouble with a formula to concatenate the descriptions into 1
cell for each S/C #, some have 2, 3 of 4 cells.

Material No. Full Description
904120016 VALVE,SOLENOID, 240V 50HZ, HERION, P/N
9301800-0201, FOR GAS SAFETY SHUT OFF AND VENT
BALL VALVES, ON ALUMINA KILNS
907010005 CABLE,BUILDING, 7/0.67, SINGLE CORE, COPPER,
BLACK, PVC / V75, .6 / 1KV, 100M ROLL
907010006 CABLE,BUILDING, 7/0.67, SINGLE CORE, COPPER, RED,
PVC / V75, .6 / 1KV, 100M ROLL

Can you elaborate more about it? You mean cells or rows?
--
Greatly appreciated
Eva


"peterh" wrote:

I am having trouble with a formula to concatenate the descriptions into 1
cell for each S/C #, some have 2, 3 of 4 cells.

Material No. Full Description
904120016 VALVE,SOLENOID, 240V 50HZ, HERION, P/N
9301800-0201, FOR GAS SAFETY SHUT OFF AND VENT
BALL VALVES, ON ALUMINA KILNS
907010005 CABLE,BUILDING, 7/0.67, SINGLE CORE, COPPER,
BLACK, PVC / V75, .6 / 1KV, 100M ROLL
907010006 CABLE,BUILDING, 7/0.67, SINGLE CORE, COPPER, RED,
PVC / V75, .6 / 1KV, 100M ROLL

Maybe this is what you want?

Function mergem(r As Range) As String
mergem = r.Cells(1, 1).Value
k = 1
For Each rr In r
If k < 1 Then
mergem = mergem & "," & rr.Value
End If
k = 2
Next
End Function

With your data in cells A1:E1, =mergem(A1:E1)

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Eva" wrote:

Can you elaborate more about it? You mean cells or rows?
--
Greatly appreciated
Eva


"peterh" wrote:

I am having trouble with a formula to concatenate the descriptions into 1
cell for each S/C #, some have 2, 3 of 4 cells.

Material No. Full Description
904120016 VALVE,SOLENOID, 240V 50HZ, HERION, P/N
9301800-0201, FOR GAS SAFETY SHUT OFF AND VENT
BALL VALVES, ON ALUMINA KILNS
907010005 CABLE,BUILDING, 7/0.67, SINGLE CORE, COPPER,
BLACK, PVC / V75, .6 / 1KV, 100M ROLL
907010006 CABLE,BUILDING, 7/0.67, SINGLE CORE, COPPER, RED,
PVC / V75, .6 / 1KV, 100M ROLL

Ryan

Just a head's up.

In cases where there are any blank cells in the range, your code will add
extra commas.

This revision will ignore blank cells.

Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.text) 0 Then sbuf = sbuf & Cell.text & ","
Next
ConCatRange = Left(sbuf, Len(sbuf) - 2)
End Function


Gord Dibben MS Excel MVP

On Tue, 15 Dec 2009 14:59:02 -0800, ryguy7272
wrote:

Maybe this is what you want?

Function mergem(r As Range) As String
mergem = r.Cells(1, 1).Value
k = 1
For Each rr In r
If k < 1 Then
mergem = mergem & "," & rr.Value
End If
k = 2
Next
End Function

With your data in cells A1:E1, =mergem(A1:E1)

Eva,

S/C #'s in Column "C"
Descriptions in Column "E"
Need result in Column "L"

"Eva" wrote:

Can you elaborate more about it? You mean cells or rows?
--
Greatly appreciated
Eva


"peterh" wrote:

I am having trouble with a formula to concatenate the descriptions into 1
cell for each S/C #, some have 2, 3 of 4 cells.

Material No. Full Description
904120016 VALVE,SOLENOID, 240V 50HZ, HERION, P/N
9301800-0201, FOR GAS SAFETY SHUT OFF AND VENT
BALL VALVES, ON ALUMINA KILNS
907010005 CABLE,BUILDING, 7/0.67, SINGLE CORE, COPPER,
BLACK, PVC / V75, .6 / 1KV, 100M ROLL
907010006 CABLE,BUILDING, 7/0.67, SINGLE CORE, COPPER, RED,
PVC / V75, .6 / 1KV, 100M ROLL

Gord-

Sir, that's why you are an MVP!!



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"peterh" wrote:

Eva,

S/C #'s in Column "C"
Descriptions in Column "E"
Need result in Column "L"

"Eva" wrote:

Can you elaborate more about it? You mean cells or rows?
--
Greatly appreciated
Eva


"peterh" wrote:

I am having trouble with a formula to concatenate the descriptions into 1
cell for each S/C #, some have 2, 3 of 4 cells.

Material No. Full Description
904120016 VALVE,SOLENOID, 240V 50HZ, HERION, P/N
9301800-0201, FOR GAS SAFETY SHUT OFF AND VENT
BALL VALVES, ON ALUMINA KILNS
907010005 CABLE,BUILDING, 7/0.67, SINGLE CORE, COPPER,
BLACK, PVC / V75, .6 / 1KV, 100M ROLL
907010006 CABLE,BUILDING, 7/0.67, SINGLE CORE, COPPER, RED,
PVC / V75, .6 / 1KV, 100M ROLL