See if this UDF will work for you :
Function RangeCon(rngTarget As Range, targValue As String, Optional sDelimiter As String = "", Optional bSkipBlanks As Boolean = True) As String
'Adapted from code by Adrew-R excel forums
'http://www.excelforum.com/excel-formulas-and-functions/894227-extensive-concatenation-issue.html?p=3097959#post3097959
Dim sTmp As String
Dim rngLoop As Range
For Each rngLoop In rngTarget.Cells
If rngLoop.Offset(, 1).Value = targValue Then
sTmp = sTmp & rngLoop.Value & sDelimiter
End If
Next rngLoop
If Len(sTmp) > 0 Then
sTmp = Left(sTmp, Len(sTmp) - Len(sDelimiter))
End If
RangeCon = sTmp
End Function
Copy above code, Open VBA editor (Alt+F11), Insert->Module; Paste the code,then save and exit editor
Copy the Role Names into Row 1 of sheet 2, then this formula into A2 of sheet2:
Formula:
=RangeCon(Sheet1!$A$2:$A$5,A$1,"; ")
Drag across
(see attached)
Hope this helps
Bookmarks