Lookup Value and Combine Texts

[contraububu]

A	B
Dan Brown	Banana
Jeff Hill	Grapes
Beth Sims	Berries
Dan Brown	Lemons
Beth Sims	Beets
Jeff Hill	Lettuce
Beth Sims	Carrots
Jeff Hill	Chocolate
Dan Brown	Apples

<tbody>
</tbody>

From the given example, I would like to lookup for customer "Dan Brown" and return the list of items he purchased "Banana, Lemons, Apples" in one cell. Thank you very much for helping me with the formula!

 

[Aladin Akyurek]

Add the following function code for ACONCAT to your workbook using Alt+F11...

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

Let Sheet1, A2:B10, house the data sample.

E1 houses a name like **** Brown.

E2, control+shift+enter, not just enter:

=REPLACE(ACONCAT(IF($A$2:$A$10=$E1,", "&$B$2:$B$10,"")),1,2,"")

 

[Carguy37122]

where in the code is this added?

=REPLACE(ACONCAT(IF($A$2:$A$10=$E1,", "&$B$2:$B$10,"")),1,2,"")

Thanks!

 

[contraububu]

Aladin Akyurek...is there a more simpler approach as I don't know anything about setting up VBA?

 

[Aladin Akyurek]

where in the code is this added?

=REPLACE(ACONCAT(IF($A$2:$A$10=$E1,", "&$B$2:$B$10,"")),1,2,"")

Thanks!

E1: Dan Brown
E2: Control+shift+enter, not just enter:

=REPLACE(ACONCAT(IF($A$2:$A$10=$E1,", "&$B$2:$B$10,"")),1,2,"")

Aladin Akyurek...is there a more simpler approach as I don't know anything about setting up VBA?

You wanted the results, tucked in a single cell. Hence the approach.

Here is the workbook that implements the set up:
https://dl.dropboxusercontent.com/u/65698317/contraububu sublist with aconcat.xlsm