I'm using the custom "SortConcat" function from Jelen and Syrstad's "VBA and Macros for Microsoft Excel."
Function SortConcat(Rng As Range) As Variant
Dim MySum As String, arr1() As String
Dim j As Integer, i As Integer
Dim cl As Range
Dim concat As Variant
On Error GoTo FuncFail:
'initialize output
SortConcat = 0#
'avoid user issues
If Rng.Count = 0 Then Exit Function
'get range into variant variable holding array
ReDim arr1(1 To Rng.Count)
'fill array
i = 1
For Each cl In Rng
arr1(i) = cl.Value
i = i + 1
Next
'sort array elements
Call BubbleSort(arr1)
'create string from array elements
For j = UBound(arr1) To 1 Step -1
If Not IsEmpty(arr1(j)) Then
MySum = arr1(j) & ", " & MySum
End If
Next j
'assign value to function
SortConcat = Left(MySum, Len(MySum) - 2)
'exit point
concat_exit:
Exit Function
'display error n cell
FuncFail:
SortConcat = Err.Number & " - " & Err.Description
Resume concat_exit
End Function
Sub BubbleSort(List() As String)
'Sorts the List array ascending order
Dim First As Integer, Last As Integer
Dim i As Integer, j As Integer
Dim Temp
First = LBound(List)
Last = UBound(List)
For i = First To Last - 1
For j = i + 1 To Last
If UCase(List(i)) > UCase(List(j)) Then
Temp = List(j)
List(j) = List(i)
List(i) = Temp
End If
Next j
Next i
End Sub
The issue is that I want to concatenate only the data in a given column that correspond to a value in another column. You could say, for example, that I have a list of names in column A, and I want to concatenate those names that have the text "this one!" appearing next to them in column B.
Sorry if I'm violating copywright. "VBA and Macros for Microsoft Excel" is a great book, and I recommend that everyone buy it. : )
Cross-posted from mrexcel.com, where I got 0 responses. Sample worksheet attached.