Conditional Concatination
 

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

--
Best Regards,
FARAZ A. QURESHI

Conditional Concatination
 

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)

"FARAZ QURESHI" wrote in message
...
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

--
Best Regards,
FARAZ A. QURESHI

Conditional Concatination
 

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

On Jan 23, 11:37*am, FARAZ QURESHI
wrote:
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

--
Best Regards,
FARAZ A. QURESHI

Conditional Concatination
 

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

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

Dave



"Bob Phillips" wrote:

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)

"FARAZ QURESHI" wrote in message
...
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

--
Best Regards,
FARAZ A. QURESHI

Conditional Concatination
 

Hi Dave,

this doesn't work for the third example.

Pete

On Jan 23, 12:56*pm, Dave Curtis
wrote:
Hi, Or a shortish formula, put in E2 and copied down:

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

Dave

Conditional Concatination
 

Well spotted, Pete,

Here's a revised attempt

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

Dave


"Pete_UK" wrote:

Hi Dave,

this doesn't work for the third example.

Pete

On Jan 23, 12:56 pm, Dave Curtis
wrote:
Hi, Or a shortish formula, put in E2 and copied down:

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

Dave