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

 

[jasonb75]

I haven't tested this, $B$2 should reference the single cell with tht date in the active sheet, SCHEDULE!$C$2:$C$200 should reference the column of dates, SCHEDULE!$D$2:$Z$200 should reference the rest of the table.

=IF(COUNTIF(SCHEDULE!$C$2:$C$200,$B$2),IF(ROWS(E25:E$25)>COUNTIF(INDEX(SCHEDULE!$D$2:$Z$200,MATCH($B$2,SCHEDULE!$C$2:$C$200),0),"<>OFF"),"",INDEX(SCHEDULE!$D$2:$Z$200,MATCH($B$2,SCHEDULE!$C$2:$C$200),SMALL(IF(INDEX(SCHEDULE!$D$2:$Z$200,MATCH($B$2,SCHEDULE!$C$2:$C$200),0)<>"OFF",COLUMN($D$1:$Z$1)-COLUMN($D$1)+1),ROW(E25)-ROW(E$25)+1))),"")

Hopefully it will be somewhere close.

 

[KerryA]

I haven't tested this, $B$2 should reference the single cell with tht date in the active sheet, SCHEDULE!$C$2:$C$200 should reference the column of dates, SCHEDULE!$D$2:$Z$200 should reference the rest of the table.

=IF(COUNTIF(SCHEDULE!$C$2:$C$200,$B$2),IF(ROWS(E25:E$25)>COUNTIF(INDEX(SCHEDULE!$D$2:$Z$200,MATCH($B$2,SCHEDULE!$C$2:$C$200),0),"<>OFF"),"",INDEX(SCHEDULE!$D$2:$Z$200,MATCH($B$2,SCHEDULE!$C$2:$C$200),SMALL(IF(INDEX(SCHEDULE!$D$2:$Z$200,MATCH($B$2,SCHEDULE!$C$2:$C$200),0)<>"OFF",COLUMN($D$1:$Z$1)-COLUMN($D$1)+1),ROW(E25)-ROW(E$25)+1))),"")

Hopefully it will be somewhere close.

Hello Jason,
I have this working somewhat. although getting a few werd responses. Should COLUMN($D$1:$Z$1)-COLUMN($D$1)+1) not reference the active sheet. I think it should reference the data sheet. Is this correct. If so I cant get it working quite yet.
Kerry

 

[jasonb75]

I have this working somewhat. although getting a few werd responses.

In what way?

Should COLUMN($D$1:$Z$1)-COLUMN($D$1)+1) not reference the active sheet. I think it should reference the data sheet. Is this correct. If so I cant get it working quite yet.
Kerry

Makes no difference which sheet for that part, it could be either, or even another different sheet, as long as it exists.

 

[KerryA]

I have it working, apparently a Kerry issue and not your formula. This formula has come a long way with your help and I really appreciate your help.
As I implement this formula I get other ideas for its use. When I extract the initials or employee on call using the above formula I get everyone scheduled to work. I can also use this to extract only those employees on weekends where the date is WEEKDAY(B2,2)>5 which indicates the date is a weekend and if the employee in range in SCHEDULE!$D$2:$Z$200 has a length greater than 2 will only pull those employees on call. Is it possible to add whe WEEKDAY and LEN to this formula.

I am sorry to keep adding to this but I think this can help me in many ways.

Thanks again,
Kerry

 

[KerryA]

Hello Jason,
I have this working somewhat. although getting a few werd responses. Should COLUMN($D$1:$Z$1)-COLUMN($D$1)+1) not reference the active sheet. I think it should reference the data sheet. Is this correct. If so I cant get it working quite yet.
Kerry

Jasonb75,
This formula works perfect until I hits a row that repeats under a column that I already extracted cell data from. Do you know how I might be able to get the last KA for 1/7/2011?

A sample of My Data on worksheet Schedule looks like this:
C AG AH AI AJ
1/3/2011 KA
1/4/2011 KD-1
1/5/2011 LC
1/6/2011 KF-1
1/7/2011 KA

 

[KerryA]

This is the formula I am referencing in Reply #45

 

[KerryA]

Hello Jason,
I have this working somewhat. although getting a few werd responses. Should COLUMN($D$1:$Z$1)-COLUMN($D$1)+1) not reference the active sheet. I think it should reference the data sheet. Is this correct. If so I cant get it working quite yet.
Kerry

Sorry, very tired today.

This is the formula I am referencing in Reply #45
<!-- / message -->

 

[KerryA]

I am trying to reference your formula in reply #42
Kerry

 

[jasonb75]

I can also use this to extract only those employees on weekends where the date is WEEKDAY(B2,2)>5 which indicates the date is a weekend and if the employee in range in SCHEDULE!$D$2:$Z$200 has a length greater than 2 will only pull those employees on call. Is it possible to add whe WEEKDAY and LEN to this formula.

I'm assuming that you're referring to the date in $B$2?

Possible, but at a quick glance, not practical.

Which version of excel are you using?

With VBA not being permissable I'm thinking an extra (hidden) sheet might be a better way to solve this.

This formula works perfect until I hits a row that repeats under a column that I already extracted cell data from. Do you know how I might be able to get the last KA for 1/7/2011?

Not sure I get what you mean there, what is the actual result you're getting, and what is expected?

What date is in $B$2?