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

 

[martindwilson]

if they are all 2 letters as in your example

=MID($A$1,ROW(A1)*3-2,2) filled down
where $A$1 is the cell with the string in

 

[jasonb75]

Working on the example I gave above, if you want X9 transposed to column Z, X10 to column AA, etc, try this formula instead

=MID(INDEX($X$8:$X$39,COLUMNS($Y:Y)),(ROWS($2:2)-1)*3+1,2)

This will allow you to fill the formula down, and across without editing manually.

 

[KerryA]

Jason,

This works for what I need. Although is there a way to eliminate the spaces between variables where "OFF" was left Blank "" ?

AA-1
DF-2

KA-NC

FG
LP
JI

Ect.

Thanks,
Kerry

 

[jasonb75]

Which method did you try?

The suggestions I made in #18 and #22 should both remove the blanks.

The table shown in #18 was created using that method with the sample row from your original question.

 

[KerryA]

Sorry Jason,
Let me re phase my question.

I have my original spreadsheet in this format of employee intials and an additional code for those employees on call. My data is as follows:

A B C D E ....... W X Y
Row 10 KA-1 HT OFF OFF JT-NC OFF JR-2 DP-1

I TRIED YOU TRNASPOSE FORMULA TO EXTRACT THE INITIAL <> OFF AND PLACE INTO ONE COLUMN.

FORMULA: {=TRANSPOSE(IF(D10:Z10<>"OFF",D10:Z10,""))}

My results in a column included the BLANK cells for <> OFF like:

KA-1
HT


JT-NC

JR-2
DP-1

I would like the transpose to exclude OFF and Blank in the new column like:

KA-1
HT
JT-NC
JR-2
DP-1

Can this be done this way? Hope this makes better sense?

Kerry

 

[jasonb75]

I can't think of an easy way to do it but will have a look.

From your sample above, I guess that the other method I suggested (#18) to use with fzhou's concatenate will not work, it was reliant on 2 initials per string.

If you're still using the concatenate that version could be revised to allow for the differing lenths of string in the cells.

Does your employers security limitation prevent use of VBA or just add-ins?

 

[KerryA]

Unfortunately, Right now I am not allowed to use VBA until I have proven myself that my code will not harm our system. So right now I am using formulas.
Kerry

 

[jasonb75]

In Y2 try

=IF(ROWS(Y$2:Y2)>COUNTIF($B$8:$W$8,"<>OFF"),"",INDEX($B$8:$W$8,,SMALL(IF($B$8:$W$8<>"OFF",COLUMN($B$8:$W$8)-COLUMN($B$8)+1),ROW(Y2)-ROW(Y$2)+1)))

This needs to be array confirmed in Y2 using Shift Ctrl Enter, then filled down to Y23.

 

[jasonb75]

As an alternative that you can copy to new columns as well as rows.

Select Y2 in your sheet (if the active cell is not in column Y this will fail).

Create a new named range, name it as desired (referred to as test in my formula) then, in the "refers to" box enter

=OFFSET(Sheet1!$B$8,COLUMNS($Y:Y)-1,,,22)

Replacing Sheet1 with the name of your sheet.

In Y2 enter the formula (and array confirm with Shift Ctrl Enter)

=IF(ROWS(Y$2:Y2)>COUNTIF(test,"<>OFF"),"",INDEX(test,,SMALL(IF(test<>"OFF",COLUMN(test)-COLUMN($B$8)+1),ROW(Y2)-ROW(Y$2)+1)))

remember to change test if you used a different name when creating the named range.

Filling the formula down in Column Y will transpose and filter from row 8, column Z from row 9, etc.

 

[KerryA]

Hello JasonB75,

I tried your forula in #28. I could not get it to work. Once I pasted in and set array, copied down all I would get is the first varriable in B8. Tried several times with no success. Thanks for trying. Will try you next formula in#29. Will let you know how that goes.
Kerry