Using ConcatenateIf function trying to remove duplicates

nmganey

New Member
Joined
Oct 11, 2016
Messages
18
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, ",")
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
This formula will generate a list of unique entries, no VBA needed...
=IFERROR(INDEX($B$50:$B$74,MATCH(0,INDEX(COUNTIF($E$49:E49,$B$50:$B$74),),0)),"")
where $B$50:$B$74 is the data you want returned
$E$49:E49,creates the list to count
 

nmganey

New Member
Joined
Oct 11, 2016
Messages
18
I put in the formula as follows;

=iferror(index(Sheet1!$A$1:$A$7520,MATCH(0,INDEX(COUNTIF(Sheet1!$B1:$7520,Sheet1!$A$1:$A$7520),),0)),"")
$A$1:$A$7520 = PAGE #
$B$1:$B$7520 = Categories

It only returns 1 page number I am looking for it to return all the page numbers that have the same Categories for example

apples 2,5,6,9,11,12,14,16,18,21,22,59,68
grapes 1,2,3,4,7,10,13,15,17
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,980
My ConcatIf function has a NoDuplicates boolean argument

Code:
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
 

nmganey

New Member
Joined
Oct 11, 2016
Messages
18

ADVERTISEMENT

My ConcatIf function has a NoDuplicates boolean argument

Code:
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



I get #Name? when I put the formula =CONCATENATEIF($B$2:$B$15, SHEET1!A2, $A$2:$A$15, ",")
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,980
My function is named ConcatIf, not ConcatenateIf. Its an old UDF that I keep at hand, not an adaptation of the OP code.

The formula would be
=CONCATIF($B$2:$B$15, SHEET1!A2, $A$2:$A$15, ",", TRUE)
 
Last edited:

nmganey

New Member
Joined
Oct 11, 2016
Messages
18

ADVERTISEMENT

Great!!!! Thanks that worked Outstandingly!!!
 

Fredek

Board Regular
Joined
Mar 8, 2011
Messages
65
Hi mikerickson,

I used the code you wrote for nmganey, it works at concatenating, however it does not seem to exclude dupes. Any idea why? Do I have to have to add anything to get rid of the dupes?

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

Thanks,
Fred
 
Last edited:

Fredek

Board Regular
Joined
Mar 8, 2011
Messages
65
Apologies, I just realised the True/False at the end of the formula switches the dupes off.

Thanks!
 

overclock

New Member
Joined
May 24, 2019
Messages
12
Office Version
  1. 365
Platform
  1. Windows
My ConcatIf function has a NoDuplicates boolean argument

Code:
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

Thank you for sharing. This concatif UDF is faster than the one running around the internet. Is it possible to have the function alphabetize the results taking into account the results can be number and alpha characters?

I couldn't figure out how to PM you so hopefully you see my reply/question.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,430
Messages
5,636,204
Members
416,908
Latest member
Streetsweeper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top