PDA

View Full Version : [SOLVED] Conditional sort and concatenate



miconian
09-22-2004, 06:14 PM
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)
09-23-2004, 06:02 AM
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
09-24-2004, 06:50 PM
Richie,

It works beautifully. Thank you very much.

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