Concatenate from a range to a single cell

M

MrBungleBear

New Member
Joined
Jan 18, 2011
Messages
10
Hi.

I have a frustrating problem - here is the set up:

I have a calendar sheet where the dates go down the rows, and there is one column per member of staff. The staff is split in to three streams (different apps to cover, etc).

In the calendar, we mark a "C" for when someone will be on-call, R for when they will be doing a release, and so on.

In a separate sheet in the workbook, rather than having people look at the whole calendar of 30+ people to see who is on what shift for a given stream, I have an On-Call Rota where the rows are again the dates, and the columns are the streams. In each cell of this sheet, there should be the names of all the people of that stream who will be on-call for that date, so a concatenation of the actual staff members from the calendar, separated by CHAR(10).

I have been unable to find a combination of INDEX, MATCH, LOOKUP, VLOOKUP, etc, etc that gets me past the main problem: they only ever match the first C (or the first R for the On-Release Rota). I can't get a formula for a single cell in the rota that will return all the names where there is a C in the calendar in such a way that I can concatenate them.

Here is the kind of thing I have had to do:

=CONCATENATE(IF(ISNA(MATCH("*R*",Calendar!AA47,0)),"",Calendar!AA$11&CHAR(10)),IF(ISNA(MATCH("*R*",Calendar!AB47,0)),"",Calendar!AB$11&CHAR(10)),IF(ISNA(MATCH("*R*",Calendar!AD47,0)),"",Calendar!AD$11&CHAR(10)),IF(ISNA(MATCH("*R*",Calendar!AE47,0)),"",Calendar!AE$11&CHAR(10)),IF(ISNA(MATCH("*R*",Calendar!AF47,0)),"",Calendar!AF$11&CHAR(10)),IF(ISNA(MATCH("*R*",Calendar!AG47,0)),"",Calendar!AG$11&CHAR(10)),IF(ISNA(MATCH("*R*",Calendar!AH47,0)),"",Calendar!AH$11&CHAR(10)),IF(ISNA(MATCH("*R*",Calendar!AI47,0)),"",Calendar!AI$11&CHAR(10)),IF(ISNA(MATCH("*R*",Calendar!AJ47,0)),"",Calendar!AJ$11&CHAR(10)),IF(ISNA(MATCH("*R*",Calendar!AK47,0)),"",Calendar!AK$11&CHAR(10)),IF(ISNA(MATCH("*R*",Calendar!AL47,0)),"",Calendar!AL$11&CHAR(10)))

I am sure you will agree that (a) it is really not elegant at all!! and (b) if you were to move a staff member in the calendar, the whole thing falls apart with #REF errors and the like... Horrible.

There ought to be a way to do this with a whole range (ie: the columns for that stream) that would return all the names (in row 11) for any column where there is a "C" in the coresponding date row and name column combo.

eg:
A B C D E
1 S T R E A M A
2 John Bob Rich Tom
3 1 Feb C C
4 2 Feb C C
5 3 Feb C C
6 4 Feb C C
7 5 Feb C C


and we want another sheet to show

Rota

A B
1 STREAM A

2 1 Feb Rich
Tom

3 2 Feb John
Bob

4 3 Feb Rich
Tom

5 4 Feb John
Rich

6 5 Feb Bob
Tom


Does anyone have any solutions, preferrably not requiring VBA? I looked at Array Formula stuff, but there doesn't seem to be a set of functions that work as an array formula to give me what I want.

Thanks!
G>
 
Last edited by a moderator:

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Re: Concatneat from a range to a single cell

Argh - didn't realise all the formatting would get stripped... Please let me know if the examples still make sense. If not, I will repost with some sort of html thingy... Dammit!

G>
 
Upvote 0
Re: Concatneat from a range to a single cell

Hi & welcome to the Board!

See my signature on various methods to display your sample. I am unable to understand the sample you posted.
 
Upvote 0
Here is a slightly better version of the example. Still not great - can't seem to download the utilities suggested...

G>



Code: 
<html>
<head>
</head>

<body link=blue vlink=purple>

<table x:str border=0 cellpadding=0 cellspacing=0 width=570 style='border-collapse:
 collapse;table-layout:fixed;width:428pt'>
 <col width=72 style='width:54pt'>
 <col width=138 style='mso-width-source:userset;mso-width-alt:4416;width:104pt'>
 <col width=72 span=5 style='width:54pt'>
 <tr height=21 style='height:15.75pt'>
  <td height=21 width=72 style='height:15.75pt;width:54pt'></td>
  <td class=xl27 width=138 style='width:104pt'>Calendar:</td>
  <td class=xl28 width=72 style='border-left:none;width:54pt'>A</td>
  <td class=xl28 width=72 style='border-left:none;width:54pt'>B</td>
  <td class=xl28 width=72 style='border-left:none;width:54pt'>C</td>
  <td class=xl28 width=72 style='border-left:none;width:54pt'>D</td>
  <td class=xl29 width=72 style='border-left:none;width:54pt'>E</td>
 </tr>
 <tr height=21 style='height:15.75pt'>
  <td height=21 style='height:15.75pt'></td>
  <td class=xl30 align=right style='border-top:none' x:num>1</td>
  <td class=xl24 style='border-top:none;border-left:none'> </td>
  <td colspan=4 class=xl25 style='border-right:1.0pt solid black;border-left:
  none'>S T R E A M<span style='mso-spacerun:yes'>   </span>A</td>
 </tr>
 <tr height=21 style='height:15.75pt'>
  <td height=21 style='height:15.75pt'></td>
  <td class=xl30 align=right style='border-top:none' x:num>2</td>
  <td class=xl24 style='border-top:none;border-left:none'> </td>
  <td class=xl24 style='border-top:none;border-left:none'>John</td>
  <td class=xl24 style='border-top:none;border-left:none'>Bob</td>
  <td class=xl24 style='border-top:none;border-left:none'>Rich</td>
  <td class=xl32 style='border-top:none;border-left:none'>Tom</td>
 </tr>
 <tr height=21 style='height:15.75pt'>
  <td height=21 style='height:15.75pt'></td>
  <td class=xl30 align=right style='border-top:none' x:num>3</td>
  <td class=xl26 align=right style='border-top:none;border-left:none'
  x:num="40575">01-Feb</td>
  <td class=xl24 style='border-top:none;border-left:none'> </td>
  <td class=xl24 style='border-top:none;border-left:none'> </td>
  <td class=xl24 style='border-top:none;border-left:none'>C</td>
  <td class=xl32 style='border-top:none;border-left:none'>C</td>
 </tr>
 <tr height=21 style='height:15.75pt'>
  <td height=21 style='height:15.75pt'></td>
  <td class=xl30 align=right style='border-top:none' x:num>4</td>
  <td class=xl26 align=right style='border-top:none;border-left:none'
  x:num="40576">02-Feb</td>
  <td class=xl24 style='border-top:none;border-left:none'>C</td>
  <td class=xl24 style='border-top:none;border-left:none'>C</td>
  <td class=xl24 style='border-top:none;border-left:none'> </td>
  <td class=xl32 style='border-top:none;border-left:none'> </td>
 </tr>
 <tr height=21 style='height:15.75pt'>
  <td height=21 style='height:15.75pt'></td>
  <td class=xl30 align=right style='border-top:none' x:num>5</td>
  <td class=xl26 align=right style='border-top:none;border-left:none'
  x:num="40577">03-Feb</td>
  <td class=xl24 style='border-top:none;border-left:none'> </td>
  <td class=xl24 style='border-top:none;border-left:none'> </td>
  <td class=xl24 style='border-top:none;border-left:none'>C</td>
  <td class=xl32 style='border-top:none;border-left:none'>C</td>
 </tr>
 <tr height=21 style='height:15.75pt'>
  <td height=21 style='height:15.75pt'></td>
  <td class=xl30 align=right style='border-top:none' x:num>6</td>
  <td class=xl26 align=right style='border-top:none;border-left:none'
  x:num="40578">04-Feb</td>
  <td class=xl24 style='border-top:none;border-left:none'>C</td>
  <td class=xl24 style='border-top:none;border-left:none'> </td>
  <td class=xl24 style='border-top:none;border-left:none'>C</td>
  <td class=xl32 style='border-top:none;border-left:none'> </td>
 </tr>
 <tr height=22 style='height:16.5pt'>
  <td height=22 style='height:16.5pt'></td>
  <td class=xl33 align=right style='border-top:none' x:num>7</td>
  <td class=xl34 align=right style='border-top:none;border-left:none'
  x:num="40579">05-Feb</td>
  <td class=xl35 style='border-top:none;border-left:none'> </td>
  <td class=xl35 style='border-top:none;border-left:none'>C</td>
  <td class=xl35 style='border-top:none;border-left:none'> </td>
  <td class=xl36 style='border-top:none;border-left:none'>C</td>
 </tr>
 <tr height=42 style='height:31.5pt;mso-xlrowspan:2'>
  <td height=42 colspan=7 style='height:31.5pt;mso-ignore:colspan'></td>
 </tr>
 <tr height=22 style='height:16.5pt'>
  <td height=22 colspan=7 style='height:16.5pt;mso-ignore:colspan'></td>
 </tr>
 <tr height=21 style='height:15.75pt'>
  <td height=21 style='height:15.75pt'></td>
  <td class=xl27>Rota:</td>
  <td class=xl28 style='border-left:none'>A</td>
  <td class=xl38 style='border-left:none'>B</td>
  <td colspan=3 style='mso-ignore:colspan'></td>
 </tr>
 <tr height=21 style='height:15.75pt'>
  <td height=21 style='height:15.75pt'></td>
  <td class=xl30 align=right style='border-top:none' x:num>1</td>
  <td class=xl24 style='border-top:none;border-left:none'> </td>
  <td class=xl32 style='border-top:none;border-left:none'>STREAM A</td>
  <td colspan=3 style='mso-ignore:colspan'></td>
 </tr>
 <tr height=42 style='height:31.5pt'>
  <td height=42 style='height:31.5pt'></td>
  <td class=xl30 align=right style='border-top:none' x:num>2</td>
  <td class=xl37 align=right style='border-top:none;border-left:none'
  x:num="40575">01-Feb</td>
  <td class=xl39 width=72 style='border-top:none;border-left:none;width:54pt'>Rich<br>
    Tom</td>
  <td colspan=3 style='mso-ignore:colspan'></td>
 </tr>
 <tr height=42 style='height:31.5pt'>
  <td height=42 style='height:31.5pt'></td>
  <td class=xl30 align=right style='border-top:none' x:num>3</td>
  <td class=xl37 align=right style='border-top:none;border-left:none'
  x:num="40576">02-Feb</td>
  <td class=xl39 width=72 style='border-top:none;border-left:none;width:54pt'>John<br>
    Bob</td>
  <td colspan=3 style='mso-ignore:colspan'></td>
 </tr>
 <tr height=42 style='height:31.5pt'>
  <td height=42 style='height:31.5pt'></td>
  <td class=xl30 align=right style='border-top:none' x:num>4</td>
  <td class=xl37 align=right style='border-top:none;border-left:none'
  x:num="40577">03-Feb</td>
  <td class=xl39 width=72 style='border-top:none;border-left:none;width:54pt'>Rich<br>
    Tom</td>
  <td colspan=3 style='mso-ignore:colspan'></td>
 </tr>
 <tr height=42 style='height:31.5pt'>
  <td height=42 style='height:31.5pt'></td>
  <td class=xl30 align=right style='border-top:none' x:num>5</td>
  <td class=xl37 align=right style='border-top:none;border-left:none'
  x:num="40578">04-Feb</td>
  <td class=xl39 width=72 style='border-top:none;border-left:none;width:54pt'>John<br>
    Rich</td>
  <td colspan=3 style='mso-ignore:colspan'></td>
 </tr>
 <tr height=43 style='height:32.25pt'>
  <td height=43 style='height:32.25pt'></td>
  <td class=xl33 align=right style='border-top:none' x:num>6</td>
  <td class=xl40 align=right style='border-top:none;border-left:none'
  x:num="40579">05-Feb</td>
  <td class=xl41 width=72 style='border-top:none;border-left:none;width:54pt'>Bob<br>
    Tom</td>
  <td colspan=3 style='mso-ignore:colspan'></td>
 </tr>
 <![if supportMisalignedColumns]>
 <tr height=0 style='display:none'>
  <td width=72 style='width:54pt'></td>
  <td width=138 style='width:104pt'></td>
  <td width=72 style='width:54pt'></td>
  <td width=72 style='width:54pt'></td>
  <td width=72 style='width:54pt'></td>
  <td width=72 style='width:54pt'></td>
  <td width=72 style='width:54pt'></td>
 </tr>
 <![endif]>
</table>

</body>

</html>
 
Upvote 0
Re: Concatneat from a range to a single cell

I think I'm doing something very similar at the moment. I have a situation where I use =TRANSPOSE to convert vertical dates and horizontal names to the opposite way round. This creates an array that I then look at on another worksheet using

=IF(ISERROR(LOOKUP(rosters!R5,Key!$A:$A,Key!$B:$B)),"RD",LOOKUP(rosters!R5,Key!$A:$A,Key!$B:$B))

That formula looks at the cell in red on the array, matches the letter in the cell to it's corresponding letter on the worksheet 'key' and displays the shift pattern it represents on a weekly roster.

I'm not sure that's very clear but it might point you in the right direction.
 
Upvote 0
Re: Concatneat from a range to a single cell

Do you mean something like

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="10px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">STREAM A</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style=";">John</td><td style=";">Bob</td><td style=";">Rich</td><td style=";">Tom</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">01-Feb</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">C</td><td style=";">C</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">02-Feb</td><td style=";">C</td><td style=";">C</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">03-Feb</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">C</td><td style=";">C</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">04-Feb</td><td style=";">C</td><td style="text-align: right;;"></td><td style=";">C</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">05-Feb</td><td style="text-align: right;;"></td><td style=";">C</td><td style="text-align: right;;"></td><td style=";">C</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="10px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style=";">STREAM A</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">01-Feb</td><td style=";">Rich, Tom</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">02-Feb</td><td style=";">John, Bob</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">03-Feb</td><td style=";">Rich, Tom</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">04-Feb</td><td style=";">John, Rich</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">05-Feb</td><td style=";">Bob, Tom</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B11</th><td style="text-align:left">=ConcatCrit(<font color="Blue">$B$2:$E$2,INDEX(<font color="Red">$B$3:$E$7,MATCH(<font color="Green">$A11,$A$3:$A$7,0</font>),0</font>),"C",", "</font>)</td></tr></tbody></table></td></tr></table><br />

Code: 
Function ConcatCrit(ConcatRng As Range, CritRng As Range, CritStr As String, Optional delim As String = ",") As String
    Dim i As Long, TempStr As String
    If ConcatRng.Cells.Count <> CritRng.Cells.Count Then
        ConcatCrit = "Differing Range Sizes"
        Exit Function
    End If
    For i = 1 To ConcatRng.Cells.Count
        If CritRng.Cells(i).Value = CritStr Then
            TempStr = TempStr & ConcatRng.Cells(i).Value & delim
        End If
    Next i
    ConcatCrit = Left$(TempStr, Len(TempStr) - Len(delim))
End Function
 
Upvote 0
Re: Concatneat from a range to a single cell

Whoa - that's pretty much exactly what I was looking for. It definitely gives me something to adapt, and only a teeny wee bit of VBA. Nice to know that i wasn't going nuts and that you can't escape the VBA option: there just isn't a formula way to do this.

Thanks for the help!!

Now to find a link for the HTML Maker that doesn't deny me access...

G>
 
Upvote 0
RESOLVED: Concatenate from a range to a single cell

Just making sure this is set to resolved and that the title is correct & searchable.
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Solved
Index/Match, Nested IF statements, or Array Formula?
Replies
9
Views
369
Ol Reliable
O
H
  • Solved
Excel Formula - Index Match or Something Else ??
Replies
4
Views
209
hrayani
H
M
  • Question Question
Creating a calendar by matching names and dates from multiple sheets
Replies
6
Views
2K
mjreezy
M
J
  • Solved
Using LEFT Function On a Range Instead of A Single Cell Value
Replies
4
Views
152
Fluff
Fluff
C
  • Solved
index match excel formula to vba code
Replies
9
Views
415
DanteAmor
DanteAmor

Forum statistics

Threads
1,225,393
Messages
6,184,707
Members
453,254
Latest member
topeb

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top