I used a User Defined Function that circulates on the Forum call Concatall (see code below). It is written by tigeravatar. I used it wrapped around this array-entered formula in cell F1 in the attached and filled down:
Formula:
=Concatall(IFERROR(SMALL(IF($B1=$B1:$B$16,$C1:$C$16),ROW($B$1:$B$16)),""),", ")
If you aren’t familiar with array-entered formulas array enter means the formula must be committed by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
If you are not familiar with how to use UDFs copy the below code, in Excel open the VBA editor (Alt + F11), click on Edit in the menu and Insert Module. Then paste this code into that module.
By the way: The formula above does not include the Personal.xlsb! prefix to Concatall that the upload has. That is because I store my UDFs / macros in that macro file. If you do not store yours in that file you’ll need to delete that prefix from formula in the upload. I did not include the UDF in this file. I am a bit squeamish about downloading .xlsm files. I suspect others are, too.
'tigeravatar ExcelForum
Public Function ConcatAll(ByVal varData As Variant, Optional ByVal sDelimiter As String = vbNullString, Optional ByVal bUnique As Boolean = False) As String
'Created by TigerAvatar at www.excelforum.com, September 2012
'Purpose is to concatenate many strings into a single string
'Can be used with arrays, range objects, and collections
Dim DataIndex As Variant 'Used to loop through arrays, range objects, and collections
Dim strResult As String 'Used to build the result string
'Test if varData is an Array, Range, or Collection
If IsArray(varData) _
Or TypeOf varData Is Range _
Or TypeOf varData Is Collection Then
'Found to be an, array, range object, or collection
'Loop through each item in varData
For Each DataIndex In varData
'Check if the item isn't empty
If Len(DataIndex) > 0 Then
'Found the item isn't empty, check if user specified bUnique as True
If bUnique = True Then
'bUnique is true, check if the item has been included in the result yet
If InStr(1, "||" & strResult & "||", "||" & DataIndex & "||", vbTextCompare) = 0 Then
'Item has not been included in the result, add item to the result
strResult = strResult & "||" & DataIndex
End If
Else
'bUnique is not true, add item to the result
strResult = strResult & "||" & DataIndex
End If
End If
Next DataIndex
'Correct strResult to remove beginning delimiter and convert "||" to the specified sDelimiter
strResult = Replace(Mid(strResult, 3), "||", sDelimiter)
Else
'Found not to be an array, range object, or collection
'Simply set the result = varData
strResult = varData
End If
'Output result
ConcatAll = strResult
End Function
Does this do what you want?
Bookmarks