Conditional sort and concatenate

[miconian]

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.

[Richie(UK)]

Hi miconian,

Well, the simplest approach (IMO) would be to add use column C in your example to a simple If statement - If column B value = "this one" then use column A value, otherwise show nothing. Then use a modified version of the UDF that excludes blank cells from the calculation.

I've attached a revised example workbook. For those not able to download files, I modified the SortConcat UDF as indicated below:

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
        If Not cl.Value = "" Then           'Richie(UK)
            arr1(i) = cl.Value
            i = i + 1
        End If                              'Richie(UK)
    Next
    ReDim Preserve arr1(1 To i - 1)           'Richie(UK)
    '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

(Apologies to Bill and Tracy if I've completely screwed it up, but it appears to work OK! )

[miconian]

Richie,

It works beautifully. Thank you very much.

Could you please comment briefly about your changes to the code above?