Using ConcatenateIf function trying to remove duplicates

[nmganey]

I have an excel sheet that has page numbers, Categories, and item numbers. I want to make almost an index of the Category then unique page numbers this is the code I am using now I need to find out were to add to remove the Dups.

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant

Dim xResult As String
Dim i as integer

On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
ConcatenateIf = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells(i).Value = Condition Then
xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
End If
Next i
If xResult <> "" Then
xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function


then in my cell my formula is

=CONCATENATEIF($A$2:$A$15, D2, $B$2:$B$15, ",")

 

[mikerickson]

I see your question. And yes, one could write a different UDF that would do that, but modifying ConcatIf would not be the way to go.
Let me see what I can do.

 

[mikerickson]

After some thought, the quickest way would be to not build sorting into ConcatIf, but to write a stand-alone sorting function.

Function SortDelimitedString(ByVal unSortedString As String, Optional Delimiter As String = " ", Optional Descending As Boolean) As String
    Dim strLeft As String, strPivot As String, strRight As String
    Dim Words As Variant, i As Long
    Words = Split(unSortedString, Delimiter)
    If UBound(Words) < 1 Then
        SortDelimitedString = unSortedString
    Else
        strPivot = Words(0)
        For i = 1 To UBound(Words)
            If (Words(i) < strPivot) Xor Descending Then
                strLeft = strLeft & Delimiter & Words(i)
            Else
                strRight = strRight & Delimiter & Words(i)
            End If
        Next i
        strLeft = Mid(strLeft, Len(Delimiter) + 1)
        strRight = Mid(strRight, Len(Delimiter) + 1)
       
        If strLeft <> vbNullString Then
            strPivot = SortDelimitedString(strLeft, Delimiter, Descending) & Delimiter & strPivot
        End If
        If strRight <> vbNullString Then
            strPivot = strPivot & Delimiter & SortDelimitedString(strRight, Delimiter, Descending)
        End If

        SortDelimitedString = strPivot
    End If
End Function

Note the optional Descending argument.

It sounds what you are looking for would be something like =SortDelimitedString(ConcatIf(some_arguments), same_delimiter)

 

[overclock]

After some thought, the quickest way would be to not build sorting into ConcatIf, but to write a stand-alone sorting function.
...
It sounds what you are looking for would be something like =SortDelimitedString(ConcatIf(some_arguments), same_delimiter)

Thank you for the quick reply. Your solution works wonderfully. Please tell me you had the SortDelimitedString function laying around like you did concatif. That's some mad coding skills if you wrote that up that quick. If you did write that up that quick and just for me, thank you, thank you, thank you.

 

[ZafarAliKhokharSindhi]

My ConcatIf function has a NoDuplicates boolean argument

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
    Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
    Dim i As Long, j As Long
    With compareRange.Parent
        Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
    End With
    If compareRange Is Nothing Then Exit Function
    If stringsRange Is Nothing Then Set stringsRange = compareRange
    Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
    stringsRange.Column - compareRange.Column)
    
    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                    ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                End If
            End If
        Next j
    Next i
    ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function

concatif range is locked if we insert rows or columns whether $ is removed
while default sumif function ranges move with respect to insert rows/columns, please help!

 

[ZafarAliKhokharSindhi]

concatif range is locked if we insert rows or columns whether $ is removed
while default sumif function ranges move with respect to insert rows/columns, please help!

Sorry, I just viewed the formula in text format cells, it is changing range automatically as sumif is changing.

Brilliant function concatif
This function is best it do not return delimeter if we have empty range, this is best point.
I found this type of vba on How to ignore duplicate values while using concatenateif in excel? which return delimeter if we have empty cells.

You are best.