Use a lookup to find conditional values in multiple rows and concatenate the results

[TheBoogExcelQs]

I have a spreadsheet that lists gifts on a registry in one column. In another column, are potential names of the folks that bought the items.

What i'd like to do is, use a lookup formula of some sort to find all the instances of a name (ex. "John Doe") and then combine all of the registry items they purchased into another cell. I'd like the name finder to be variable so I can use it down a list of guests.

Is this possible to do in excel?

 

[3PlankWalker]

Hi, see solution by Aladin Akyurek in the thread

<title>Excel Jeanie HTML</title>Sheet2

	A	B	C	D	E
1	Gift	Name		Jhon Doe	Doe
2	Coffe Table	Jhon Doe		3	1
3	Pen	Jhon Doe
4	Pencil	Doe
5	Watch	Jhon Doe		Gift Item	Gift Item
6				Coffe Table	Pencil
7				Pen
8				Watch

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:78px;"><col style="width:64px;"><col style="width:64px;"><col style="width:75px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Formeln der Tabelle

Zelle Formel D2 =COUNTIF($B$2:$B$5,D$1) E2 =COUNTIF($B$2:$B$5,E$1) D6 {=IF(ROWS($D$5:D5)<=D$2,INDEX($A$2:$A$5,IF(D$2>1,SMALL(IF($B$2:$B$5=D$1,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($D$5:D5)),MATCH(D$1,$B2:$B$5,0))),"")} E6 {=IF(ROWS($D$5:E5)<=E$2,INDEX($A$2:$A$5,IF(E$2>1,SMALL(IF($B$2:$B$5=E$1,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($D$5:E5)),MATCH(E$1,$B2:$B$5,0))),"")} D7 {=IF(ROWS($D$5:D6)<=D$2,INDEX($A$2:$A$5,IF(D$2>1,SMALL(IF($B$2:$B$5=D$1,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($D$5:D6)),MATCH(D$1,$B3:$B$5,0))),"")} E7 {=IF(ROWS($D$5:E6)<=E$2,INDEX($A$2:$A$5,IF(E$2>1,SMALL(IF($B$2:$B$5=E$1,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($D$5:E6)),MATCH(E$1,$B3:$B$5,0))),"")} D8 {=IF(ROWS($D$5:D7)<=D$2,INDEX($A$2:$A$5,IF(D$2>1,SMALL(IF($B$2:$B$5=D$1,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($D$5:D7)),MATCH(D$1,$B4:$B$5,0))),"")} E8 {=IF(ROWS($D$5:E7)<=E$2,INDEX($A$2:$A$5,IF(E$2>1,SMALL(IF($B$2:$B$5=E$1,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($D$5:E7)),MATCH(E$1,$B4:$B$5,0))),"")} <tbody> </tbody>

Enthält Matrixformel: Umrandende { } nicht miteingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen!

Matrix verstehen

<tbody>
</tbody>

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

 

[TheBoogExcelQs]

Hi, thank you. Close, but I'm looking for something more like:

Sheet2

	A	B	C	D	E
1	Gift	Name		Jhon Doe	Coffe Table, Pen, Watch
2	Coffe Table	Jhon Doe		Doe	Pencil
3	Pen	Jhon Doe
4	Pencil	Doe
5	Watch	Jhon Doe

<tbody>
</tbody>

Any ideas?

Hi, see solution by Aladin Akyurek in the thread

<title>Excel Jeanie HTML</title>Sheet2

	A	B	C	D	E
1	Gift	Name		Jhon Doe	Doe
2	Coffe Table	Jhon Doe		3	1
3	Pen	Jhon Doe
4	Pencil	Doe
5	Watch	Jhon Doe		Gift Item	Gift Item
6				Coffe Table	Pencil
7				Pen
8				Watch

<tbody>
</tbody>

Formeln der Tabelle

Zelle Formel D2 =COUNTIF($B$2:$B$5,D$1) E2 =COUNTIF($B$2:$B$5,E$1) D6 {=IF(ROWS($D$5:D5)<=D$2,INDEX($A$2:$A$5,IF(D$2>1,SMALL(IF($B$2:$B$5=D$1,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($D$5:D5)),MATCH(D$1,$B2:$B$5,0))),"")} E6 {=IF(ROWS($D$5:E5)<=E$2,INDEX($A$2:$A$5,IF(E$2>1,SMALL(IF($B$2:$B$5=E$1,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($D$5:E5)),MATCH(E$1,$B2:$B$5,0))),"")} D7 {=IF(ROWS($D$5:D6)<=D$2,INDEX($A$2:$A$5,IF(D$2>1,SMALL(IF($B$2:$B$5=D$1,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($D$5:D6)),MATCH(D$1,$B3:$B$5,0))),"")} E7 {=IF(ROWS($D$5:E6)<=E$2,INDEX($A$2:$A$5,IF(E$2>1,SMALL(IF($B$2:$B$5=E$1,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($D$5:E6)),MATCH(E$1,$B3:$B$5,0))),"")} D8 {=IF(ROWS($D$5:D7)<=D$2,INDEX($A$2:$A$5,IF(D$2>1,SMALL(IF($B$2:$B$5=D$1,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($D$5:D7)),MATCH(D$1,$B4:$B$5,0))),"")} E8 {=IF(ROWS($D$5:E7)<=E$2,INDEX($A$2:$A$5,IF(E$2>1,SMALL(IF($B$2:$B$5=E$1,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($D$5:E7)),MATCH(E$1,$B4:$B$5,0))),"")} <tbody> </tbody>

Enthält Matrixformel: Umrandende { } nicht miteingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen!

Matrix verstehen

<tbody>
</tbody>

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

 

[TheBoogExcelQs]

I guess my question boils down to:

In Excel, can you concatenate multiple cells (in a column) based on meeting a single conditional argument?

 

[XOR LX]

I guess my question boils down to:

In Excel, can you concatenate multiple cells (in a column) based on meeting a single conditional argument?

Not without VBA, no. (Or else unfeasibly long formula constructions.)

Regards

 

[TheBoogExcelQs]

Thanks XOR LX!

I'm not familiar with VBAs. Is there something I can do to help learn how to do this with a VBA?

 

[XOR LX]

I'm afraid my VBA is quite poor, but give this a go:

Sub Concat1()

Dim SearchNames As Range, SourceNames As Range

Set SearchNames = Range("D1:D" & Cells(Rows.Count, "D").End(xlUp).Row)
Set SourceNames = Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)

On Error Resume Next

For Each r In SearchNames
    For Each s In SourceNames
        If s.Value = r.Value Then
            r.Offset(0, 1).Value = s.Offset(0, -1).Value & ", " & r.Offset(0, 1).Value
        End If
    Next s    
    r.Offset(0, 1).Value = Left(r.Offset(0, 1).Value, Len(r.Offset(0, 1).Value) - 2)
Next r

End Sub

Regards