How to ignore duplicate values while using concatenateif in excel?

I work on Microsoft Excel with a lot of data, especially Manufacturing date(MFD) and Batch No.

A date can have number of entries, possibilities of the entries be similar or different. for example,

MFD             B.No.   
05-Apr-18   AD0418BB05    
05-Apr-18   AD0418BB05    
06-Apr-18   AD0418BB06    
06-Apr-18   AD0418BB06    
07-Apr-18   AA0418BB07    
07-Apr-18   BB0418BB07

I tried consolidating the dates in another column, using the formula ,

=IFERROR(INDEX($A$2:$A$15, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$15), 0)),"")

In the next column, i used concatenateif formula to concatenate the values corresponding to each date. the code is as follows,

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

The output that i get is,

MFD         B.No.   
05-Apr-18   AD0418BB05,AD0418BB05    
06-Apr-18   AD0418BB06,AD0418BB06    
07-Apr-18   AA0418BB07,BB0418BB07

On 07-Apr-18, it is great. But, on 05-Apr-18 AND 06-Apr-18, two similar entries are concatenated. I want it be displayed only once. Have anyone got any ideas what I should do?

Thanks in advance.

P.S. : I can't carry out remove duplicates option, as i have many data related to this!!

Solution

You need to adjust your concatenate function for this.

I introduced a AllowDuplicates parameter to choose if duplicates should be concatenated or not.

So use it like =CONCATENATEIF($A$2:$A$15, D2, $B$2:$B$15, ",", FALSE) to have no duplicates.

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

    Dim strResult As String
    On Error GoTo ErrHandler
    If CriteriaRange.Count <> ConcatenateRange.Count Then
        ConcatenateIf = CVErr(xlErrRef)
        Exit Function
    End If

    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")

    Dim i As Long
    For i = 1 To CriteriaRange.Count
        If CriteriaRange.Cells(i).Value = Condition Then
            If AllowDuplicates Then
                strResult = strResult & Separator & ConcatenateRange.Cells(i).Value
            Else
                If Not dict.Exists(ConcatenateRange.Cells(i).Value) Then
                    dict.Add ConcatenateRange.Cells(i).Value, 0
                    strResult = strResult & Separator & ConcatenateRange.Cells(i).Value
                End If
            End If
        End If
    Next i

    If strResult <> "" Then
        strResult = Mid(strResult, Len(Separator) + 1)
    End If

    ConcatenateIf = strResult
    Exit Function
ErrHandler:
    ConcatenateIf = CVErr(xlErrValue)
End Function