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

T

TheBoogExcelQs

New Member
Joined
Sep 30, 2014
Messages
4
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?
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Hi, see solution by Aladin Akyurek in the thread

<title>Excel Jeanie HTML</title>Sheet2

ABCDE
GiftName
Coffe TableJhon Doe
PenJhon Doe
PencilDoe
WatchJhon Doe

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: center"]Jhon Doe[/TD]
[TD="align: center"]Doe[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: center"]Gift Item[/TD]
[TD="align: center"]Gift Item[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: center"]Coffe Table[/TD]
[TD="align: center"]Pencil[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: center"]Pen[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: center"]Watch[/TD]

</tbody>

Formeln der Tabelle
ZelleFormel
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
 
Last edited:
Upvote 0
Hi, thank you. Close, but I'm looking for something more like:

Sheet2

ABCDE
GiftName
Coffe TableJhon Doe
PenJhon Doe
PencilDoe
WatchJhon Doe

<tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]

[TD="align: center"]Jhon Doe[/TD]
[TD="align: center"]Coffe Table, Pen, Watch[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="align: center"]Doe[/TD]
[TD="align: center"]Pencil[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

Any ideas?

3PlankWalker said:
Hi, see solution by Aladin Akyurek in the thread

<title>Excel Jeanie HTML</title>Sheet2

ABCDE
GiftName
Coffe TableJhon Doe
PenJhon Doe
PencilDoe
WatchJhon Doe

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: center"]Jhon Doe[/TD]
[TD="align: center"]Doe[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: center"]Gift Item[/TD]
[TD="align: center"]Gift Item[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: center"]Coffe Table[/TD]
[TD="align: center"]Pencil[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: center"]Pen[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: center"]Watch[/TD]

</tbody>

Formeln der Tabelle
ZelleFormel
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
Click to expand...
 
Upvote 0
I guess my question boils down to:

In Excel, can you concatenate multiple cells (in a column) based on meeting a single conditional argument?
 
Upvote 0
I'm afraid my VBA is quite poor, but give this a go:

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Hyperlink formula to row after last used row in table
Replies
1
Views
396
sspatriots
S
O
  • Solved
I want it all! Using single lookup command to return both multiple Rows and Columns
Replies
4
Views
426
Fluff
Fluff
A
  • Question Question
Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell
Replies
8
Views
510
Peter_SSs
Peter_SSs
M
  • Question Question
Conditional Formatting/Moving Rows Based on Matching and Opposite Data
Replies
1
Views
204
Madam_Xavier
M
S
  • Solved
Formula that will Look for a value in a table column that is on another worksheet...
Replies
5
Views
355
sspatriots
S

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top