Conditional Concatination

[FARAZ QURESHI]

Hi every1

I have NINE columns showing different levels. An example:

Level 1 Level 2 Level 3 Level 4
A 1 a
A 2 a i
A 2 a ii
A 2 b i
B 1
B 2 a
B 2 b
C 1
C 2
D
E 1

Now what sort of SHORT formula should be placed in the column E (actually
10th column in the original data) which would concatinate the levels' columns
resulting as follows, without using the IF() condition for placing "-"
(hyphens):

A - 1 - a
A - 2 - a - i
A - 2 - a - ii
A - 2 - b - i
B - 1
B - 2 - a
B - 2 - b
C - 1
C - 2
D
E - 1

 

[Bob Phillips]

A simple UDF

Public Function MyJoin(ParamArray rng())
Dim i As Long
Dim cell As Range
Dim tmp As String

For i = LBound(rng) To UBound(rng)

For Each cell In rng(i)

If cell.Value <> "" Then

tmp = tmp & "-" & cell.Value
End If
Next cell
Next i
If Len(tmp) > 0 Then tmp = Right(tmp, Len(tmp) - 1)
MyJoin = tmp
End Function


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Pete_UK]

Put this in E2:

=A2&IF(B2<>""," - "&B2,"")&IF(C2<>""," - "&C2,"")&IF(D2<>""," -
"&D2,"")

then copy down.

Not sure why you want to avoid using IF.

Hope this helps.

Pete

 

[Dave Curtis]

Hi, Or a shortish formula, put in E2 and copied down:

=LEFT(A2&"-"&B2&"-"&C2&"-"&D2,2*COUNTA(A22)-1)

Dave

A simple UDF

Public Function MyJoin(ParamArray rng())
Dim i As Long
Dim cell As Range
Dim tmp As String

For i = LBound(rng) To UBound(rng)

For Each cell In rng(i)

If cell.Value <> "" Then

tmp = tmp & "-" & cell.Value
End If
Next cell
Next i
If Len(tmp) > 0 Then tmp = Right(tmp, Len(tmp) - 1)
MyJoin = tmp
End Function


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Pete_UK]

Hi Dave,

this doesn't work for the third example.

Pete

 

[Dave Curtis]

Well spotted, Pete,

Here's a revised attempt

=LEFT(A2&"-"&B2&"-"&C2&"-"&D2,LEN(A2)+LEN(B2)+LEN(C2)+LEN(D2)+COUNTA(A22)-1)

Dave