Index & Match. Concatenate Function

[hazza147]

Hi,

How would I use the concatenate function or something similar to produce a list of error messages which is built though index and matching a condition which matches more than one of the rows in the table. As it matches more than 1 I would like the error messages found in a different column to concatenate together to give the user as much detail as possible.

Quite difficult to explain I know, let me know if you want me to make anything clearer!

Thanks,
Harry

 

[Gerald Higgins]

Can you give us maybe two or three different examples of what you want to do please ?

 

[hazza147]

Hi, Thanks for getting back to me.

I would like to be able to use the Index and Match Formula integrated to the Concatenate function to be able to join together all error messages of active rows. Eg:

<table border="0" cellpadding="0" cellspacing="0" width="331" height="156"><col style="width: 48pt;" width="64"> <col style="width: 55pt;" width="73"> <col style="width: 71pt;" width="95"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" align="center" width="64" height="20">ID</td> <td style="width: 55pt;" align="center" width="73">Active</td> <td style="width: 71pt;" align="center" width="95">Error Message</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">1</td> <td align="center">No</td> <td align="center">Alpha</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">2</td> <td align="center">Yes</td> <td align="center">Bravo</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">3</td> <td align="center">No</td> <td align="center">Charlie</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">4</td> <td align="center">Yes</td> <td align="center">Delta</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">5</td> <td align="center">Yes</td> <td align="center">Echo</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="center" height="20">6</td> <td align="center">No</td> <td align="center">Foxtrot
</td> </tr> </tbody></table>
So I need a formula which goes down column 2 finds every word "Yes" then prints the message to the right of it. I.e. the above would return 'Bravo, Delta, Echo'

 

[T. Valko]

Hi, Thanks for getting back to me.

I would like to be able to use the Index and Match Formula integrated to the Concatenate function to be able to join together all error messages of active rows. Eg:

<TABLE height=156 cellSpacing=0 cellPadding=0 width=331 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 55pt" width=73><COL style="WIDTH: 71pt" width=95><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="WIDTH: 48pt; HEIGHT: 15pt" align=middle width=64 height=20>ID</TD><TD style="WIDTH: 55pt" align=middle width=73>Active</TD><TD style="WIDTH: 71pt" align=middle width=95>Error Message</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" align=middle height=20>1</TD><TD align=middle>No</TD><TD align=middle>Alpha</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" align=middle height=20>2</TD><TD align=middle>Yes</TD><TD align=middle>Bravo</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" align=middle height=20>3</TD><TD align=middle>No</TD><TD align=middle>Charlie</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" align=middle height=20>4</TD><TD align=middle>Yes</TD><TD align=middle>Delta</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" align=middle height=20>5</TD><TD align=middle>Yes</TD><TD align=middle>Echo</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" align=middle height=20>6</TD><TD align=middle>No</TD><TD align=middle>Foxtrot

</TD></TR></TBODY></TABLE>
So I need a formula which goes down column 2 finds every word "Yes" then prints the message to the right of it. I.e. the above would return 'Bravo, Delta, Echo'

See if this is what you had in mind:

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3

 

[mikerickson]

This UDF should do what you want.
The first three arguments of ConcatIf mirror those of SUMIF.

In your situation, the formula would be =ConcatIf(B:B, "Yes", C:C, ", ")

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

 

[hazza147]

Thank you very very much Mikerichson that's done it for me.

PLEASE CLOSE THREAD.