Match index & concatenate help!!

[kwt890]

Hello All,

So I want to return a text value based on 4 criteria. I can do this with the Index Match function, however I want to concatenate ALL text values found in the column that match the 4 criteria into a single cell seperated by "; ". I desperately need help on this! The data would look like below as a sample (Columns A,B,C,D,E)

[TABLE="class: grid, width: 500, align: left"]
<TBODY>[TR]
[TD]Region [/TD]
[TD]Type[/TD]
[TD]Status[/TD]
[TD]Audit Name[/TD]
[TD]Month[/TD]
[/TR]
[TR]
[TD]Asia[/TD]
[TD]Internal[/TD]
[TD]Complete[/TD]
[TD]APAC[/TD]
[TD]January[/TD]
[/TR]
[TR]
[TD]Asia[/TD]
[TD]Internal[/TD]
[TD]Complete[/TD]
[TD]Korea GO[/TD]
[TD]January[/TD]
[/TR]
[TR]
[TD]Europe[/TD]
[TD]External[/TD]
[TD]Complete[/TD]
[TD]UK sheffield[/TD]
[TD]January[/TD]
[/TR]
</TBODY>[/TABLE]








I want the audit names concatenated into a single cell if Region="Asia", Type="Internal", Status="Complete", and Month="January". Therefore, the result in the cell should read "APAC; Korea GO". If I just have the function then I would then have the usability to search other criteria, etc.

Any help on this would be greatly appreciated!

-Taylor

 

[kwt890]

Hello All,

So I want to return a text value based on 4 criteria. I can do this with the Index Match function, however I want to concatenate ALL text values found in the column that match the 4 criteria into a single cell seperated by "; ". I desperately need help on this! The data would look like below as a sample (Columns A,B,C,D,E)

[TABLE="class: grid, width: 500, align: left"]
<TBODY>[TR]
[TD]Region
[/TD]
[TD]Type
[/TD]
[TD]Status
[/TD]
[TD]Audit Name
[/TD]
[TD]Month
[/TD]
[/TR]
[TR]
[TD]Asia
[/TD]
[TD]Internal
[/TD]
[TD]Complete
[/TD]
[TD]APAC
[/TD]
[TD]January
[/TD]
[/TR]
[TR]
[TD]Asia
[/TD]
[TD]Internal
[/TD]
[TD]Complete
[/TD]
[TD]Korea GO
[/TD]
[TD]January
[/TD]
[/TR]
[TR]
[TD]Europe
[/TD]
[TD]External
[/TD]
[TD]Complete
[/TD]
[TD]UK sheffield
[/TD]
[TD]January
[/TD]
[/TR]
</TBODY>[/TABLE]








I want the audit names concatenated into a single cell if Region="Asia", Type="Internal", Status="Complete", and Month="January". Therefore, the result in the cell should read "APAC; Korea GO". If I just have the function then I would then have the usability to search other criteria, etc.

Any help on this would be greatly appreciated!

-Taylor

Okay - So I found this UDF and it works perfectly. However it only uses 1 criteria to return concatenated results. How do I change the code to have 4 total criteria met?

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
                    
    Rem the first three argumens of ConcatIf mirror those of SUMIF
    Rem the Delimiter and NoDuplicates arguments are optional (default "" and False)
    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