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]

Good job jimrward!

If you could add parenthesis as OP wants, it would be perfect!

 

[jimrward]

Function ConcatAll(sep As String, rng As Range)
Dim x As String, cel As Range
x = "("
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

used as follows =ConcatAll(",",B1:W1)

you will need to add the code above and save in your personal.xls space

 

[KerryA]

Thanks to all of you who responded. All very much appreciated.
Kerry

 

[KerryA]

Is it possible to take this concatenated cell reference and tranpose it in a single column with multiple rows like text to column?

 

[jasonb75]

Depending how you want the result to look you can do it without the concatenated cell.

Select a range of 23 cells in a single column, for example Y1:Y23

Press F2 and enter the formula =TRANSPOSE(IF(A8:W9<>"OFF",A8:W9,""))

Then press Shift Ctrl & Enter to array confirm the formula.

Is the resulting format acceptable? Note that you will need to change the format of Y1 so that it shows the date correctly.

 

[KerryA]

My last correspondence with fzhuo worked perfectly to concatonate multiple cells into one cell with comma separation.

Example results: (CH,KG,LG,LN,MT,MV)

I need to take this information (CH,KG,LG,LN,MT,MV) now and reverse concatonate and transpose into on column.

Does this make sense?

Thank you,
Kerry

 

[martindwilson]

=substitute(trim(substitute(b1&" "&c1&" "&d1&" "&e1&" "&f1&" "&g1&" "&h1&" "&i1&" "&j1&" "&k1&" "&l1&" "&m1,"OFF"," "))," ",",")
HMM I WAS ANSWERING THE ORIGINAL QUESTION LOL

 

[jasonb75]

You mean like

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 364px"><COL style="WIDTH: 75px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>X</TD><TD>Y</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD> </TD><TD>CH</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD> </TD><TD>KG</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD> </TD><TD>LG</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD> </TD><TD>LN</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD> </TD><TD>LH</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD> </TD><TD>MT</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>CH,KG,LG,LN,LH,MT,MV,MA,ST,CO,DA,KD,KA,KF,LP,LP,LA</TD><TD>MV</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD> </TD><TD>MA</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD> </TD><TD>ST</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD> </TD><TD>CO</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD> </TD><TD>DA</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD> </TD><TD>KD</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD> </TD><TD>KA</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD> </TD><TD>KF</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD> </TD><TD>LP</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD> </TD><TD>LP</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD> </TD><TD>LA</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>Y2</TD><TD>=MID($X$8,(ROWS(Y$2:Y2)-1)*3+1,2)</TD></TR><TR><TD>X8</TD><TD>{=SUBSTITUTE(TRIM(MCONCAT(IF(B8:W8<>"OFF",B8:W8,"")," "))," ",",")}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1

The formula in Y2 is copied down to Y23 using autofill.

My original suggestion did the same, but would have had blank cells in place of "OFF" from the original list.

 

[KerryA]

Hi Martin,
I see that you answered my original question. I am now trying to take this one step further. I need to take this concatenated cell reference and transpose it into one column with multiple rows. I need to take this (CH,KG,LG,LN,MT,MV) and now reverse the concatenation and transpose it into a single column with each initial having its own row and no comma.

Does this make sense?

Thank you,
Kerry

 

[KerryA]

Yes exactly! I'll give it a try.

Thanks