Concatenation w/ multiple criteria

[KerryA]

Hello guru's,

I have a new employee workbook I have been developing to automate a schedule for employees. In this workbook I have one sheet with a date in col A8:A39 and between B8:W39 I have employee initials or the word OFF. If the employee is scheduled to work their initials are present and if they are scheduled OFF the word OFF is present. What I need is to concatenate each row of only employees initals excluding the employees if they are OFF.

Example:

<TABLE style="WIDTH: 816pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1095 border=0 x:str><COLGROUP><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" span=22 width=47><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 46pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right width=61 height=15 x:num="40544">1/1/2011

</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>OFF</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>CH</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>OFF</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>KG</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>LG</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>LN</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>LH</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>MT</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>MV</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>MA</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>OFF</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>ST</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>CO</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>DA</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>OFF</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>KD</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>KA</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>KF</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>LP</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>OFF</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>LP</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>LA</TD></TR></TBODY></TABLE>


I would like to concatenate this row of data from B8:W39 and exclude any employee that is OFF. I cannot add any add on do to security linitation at my employer. If their is a formula that can do this would be great or away to put these initial in a new column.

Thanks in advance,
Kerry

 

[fzhou]

Hi Kerry,

I don't how you are going to treat "OFF", leave it blank or something else. But can you use this:

=+IF(b3="OFF","",B8)&IF(c3="OFF","",C8)&IF(d3="OFF","",D8)&IF(e3="OFF","",E8)...

 

[KerryA]

Hi fzhou,
All this data is in the same row so I don't beleive your example will work for this. In cell A8 is a date and in cells B8:W8 is either an employee initial or OFF. If this helps here is an example:


A B C D E F
1/1/11 OFF

 

[KerryA]

Example: ROW 8

A B C D E F G H I J
<TABLE style="WIDTH: 501pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=672 border=0 x:str><COLGROUP><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" span=13 width=47><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 46pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right width=61 height=15 x:num="40544"> 1/1/2011</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47> OFF</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>CH</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>OFF</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>KG</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>LG</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>LN</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>OFF</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>MT</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>MV</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>


</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>OFF</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>ST</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 35pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=47>CO</TD></TR></TBODY></TABLE>
What I need the concatenation to look like in Col K is:

(CH,KG,LG,LN,MT,MV)

 

[fzhou]

What is your expected result in cell X8 then?

 

[KerryA]

I just shortened this for the example. What I would like is the concatenated string of only initials without the cells with OFF. I would like to have a "," in between each initial.]
Does this help?
Thanks,
Kerry

 

[fzhou]

That's exactly this formula will give you:

=+"("&IF(LEN(B8)=3,"",B8&",")&IF(LEN(C8)=3,"",C8&",")&IF(LEN(D8)=3,"",D8&",")&IF(LEN(E8)=3,"",E8&",")&IF(LEN(F8)=3,"",F8&",")&IF(LEN(G8)=3,"",G8&",")&IF(LEN(H8)=3,"",H8&",")&IF(LEN(I8)=3,"",I8&",")&IF(LEN(J8)=3,"",J8&",")&IF(LEN(K8)=3,"",K8&",")&IF(LEN(L8)=3,"",L8&",")&IF(LEN(M8)=3,"",M8&",")&IF(LEN(N8)=3,"",N8&",")&IF(LEN(O8)=3,"",O8&",")&IF(LEN(P8)=3,"",P8&",")...

Result in X8 is (CH,KG,LG,LN,LH,MT,MV,MA,ST,CO,DA,

That's not what you expected?

 

[jimrward]

it could be done neatly with a custom function in VBA to step thru the range and join them all up and leave out the OFF ones or it could be done the ugly using formula in two stages

join up all the cells and replace OFF with a null empty value

 

[KerryA]

fzhou...GREAT JOB!!!

This appears to work perfectly. Let me test it out on my complete list but for now apears to work perfectly.

Thank you

 

[jimrward]

custom function approach

Function ConcatAll(sep As String, rng As Range)
Dim x As String, cel As Range
For Each cel In rng
    If cel.Value <> "OFF" Then
        x = x & cel.Value & sep
    End If
Next
ConcatAll = Left(x, Len(x) - 1)
End Function