Concatenate with more than one logical test and multiple results

[dl08in]

I'm looking for a way to compare a text string or cell address to a column of possible matches. Then I want to concatenate notes from every row that has a match, but only if a logical test is true. A UDF would be perfect.

I've seen one from Rick Rothstein mentioned a couple times now, but I can't figure out how to add the logical test that I require. In the table below is an example of what I'm looking for. The values in column A (Item) are irrelevant for my purposes, but I want to compare their cell addresses (text strings) against a lookup table in D1:F7. Column B is an example of the results I'm looking for. Note that item $A$2 is present in D3, but the logical test in E3 returns FALSE so B2 is empty. $A$3 is present in D2 and D5. E2 is TRUE and E5 is FALSE, so only F2 is returned in B3. I hope this makes sense.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Item[/TD]
[TD]Results[/TD]
[TD][/TD]
[TD]Cell References[/TD]
[TD]Logical Test[/TD]
[TD]Notes[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]$A$3, $A$5[/TD]
[TD]TRUE[/TD]
[TD]AA[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]AA[/TD]
[TD][/TD]
[TD]$A$2[/TD]
[TD]FALSE[/TD]
[TD]BB[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD]EE[/TD]
[TD][/TD]
[TD]$A$6[/TD]
[TD]FALSE[/TD]
[TD]CC[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD]AA[/TD]
[TD][/TD]
[TD]$A$3[/TD]
[TD]FALSE[/TD]
[TD]DD[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]5[/TD]
[TD]EE[/TD]
[TD][/TD]
[TD]$A$4, $A$6, $A$7[/TD]
[TD]TRUE[/TD]
[TD]EE[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]6[/TD]
[TD]EE, FF[/TD]
[TD][/TD]
[TD]$A$7[/TD]
[TD]TRUE[/TD]
[TD]FF[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

B2: control+shift+enter, not just enter, and copy down:

=REPLACE(ACONCAT(IF(ISNUMBER(SEARCH(CELL("address",A2),$D$2:$D$7)),
    IF($E$2:$E$7,", "&$F$2:$F$7,""),"")),1,2,"")

For this formula to work, you need to add ACONCAT as a module, using Alt+F1, to your workbook:

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

 

[dl08in]

That works! I don't fully understand it, but it does exactly what I wanted. Thank you!

 

[Aladin Akyurek]

That works! I don't fully understand it, but it does exactly what I wanted. Thank you!

You are welcome.