VLOOKUP For Multiple Matches

[srj1359]

Hello!

I have two sheets (Titles and Accounts).

The Accounts sheet has two columns: Participant Name (column A) and PRODUCT_ID (column B).

The Titles sheet has 32 columns, but the only one that matters is PRODUCT_ID (column AF).

In the Accounts sheet, multiple participants can have the same product ID.

In the Titles sheet, multiple titles can have the same product ID.

I'm trying to find a way to search to pull in the multiple participant names that match each product ID and the only way I think of is a VLOOKUP (though I'm open to other suggestions).

Please let me know if I need to clarify anything.

If it helps, I work in publishing, so think of it in terms of customer A bought a Science bundle and a History bundle. Customer B only bought a Science bundle. Each bundle has multiple books in it. How can I find out how many titles customer A has and how many customer B has?

Thank you in advance. I have been searching and trying different ideas for hours and would so appreciate some help from someone with more knowledge than me

 

[srj1359]

@Peter_SSs

That would be ideal, but I was trying not to be too greedy

 

[Peter_SSs]

Try this with a copy of your workbook.

Sub CollectNames()
  Dim d As Object
  Dim a As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  With Sheets("Accounts")
    a = .Range("A2", .Range("B" & Rows.Count).End(xlUp)).Value
  End With
  For i = 1 To UBound(a)
    d(a(i, 2)) = d(a(i, 2)) & ";" & a(i, 1)
  Next i
  With Sheets("Titles")
    a = .Range("AF2", .Range("AF" & Rows.Count).End(xlUp)).Value
    For i = 1 To UBound(a)
      a(i, 1) = Mid(d(a(i, 1)), 3)
    Next i
    With .Range("AG2").Resize(UBound(a))
      .Value = a
      .TextToColumns DataType:=xlDelimited, Semicolon:=True, Comma:=False, Space:=False, Other:=False
    End With
  End With
End Sub

Results for me from your sample data:

srj1359.xlsm
	A																															AF	AG	AH
1	publication_title																															PRODUCT_ID
2	In Search of Lost Time by Marcel Proust																															AAA-00001	BC University	DEF University
3	Ulysses by James Joyce																															AAA-00002	BC University	DEF University
4	Don Quixote by Miguel de Cervantes																															AAA-00003	BC University	DEF University
5	The Great Gatsby by F. Scott Fitzgerald																															AAA-00004	BC University	DEF University
6	One Hundred Years of Solitude by Gabriel Garcia Marquez																															AAA-00005	BC University	DEF University
7	Moby **** by Herman Melville																															ZZZ-00001	EF University
8	War and Peace by Leo Tolstoy																															ZZZ-00002	EF University
9	Lolita by Vladimir Nabokov																															ZZZ-00003	EF University
10	Hamlet by William Shakespeare																															ZZZ-00004	EF University
11	The Catcher in the Rye by J. D. Salinger																															ZZZ-00005	EF University
12	The Odyssey by Homer																															AAA-00001	BC University	DEF University
13	The Brothers Karamazov by Fyodor Dostoyevsky																															AAA-00002	BC University	DEF University
14	Crime and Punishment by Fyodor Dostoyevsky																															AAA-00003	BC University	DEF University
15	Madame Bovary by Gustave Flaubert																															AAA-00004	BC University	DEF University
16	The Divine Comedy by Dante Alighieri																															AAA-00005	BC University	DEF University
17	The Adventures of Huckleberry Finn by Mark Twain																															ZZZ-00001	EF University
18	Alice's Adventures in Wonderland by Lewis Carroll																															ZZZ-00002	EF University
19	Pride and Prejudice by Jane Austen																															ZZZ-00003	EF University
20	Wuthering Heights by Emily Brontë																															ZZZ-00004	EF University
21	To the Lighthouse by Virginia Woolf																															ZZZ-00005	EF University
Titles

 

[srj1359]

@Peter_SSs I think this should work, except it's cutting off the first letter of the first participant name (in your example above notice in AG it says "BC University" instead of "ABC University." Can you please tell me how to fix this?

 

[alansidman]

Here is a PQ solution

Bring both tables into the PQ editor and join them

let
    Source = Table.NestedJoin(Table1, {"PRODUCT_ID"}, Table2, {"PRODUCT_ID"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"publication_title"}, {"Table2.publication_title"})
in
    #"Expanded Table2"

Book2
	A	B	C
1	Participant Name	PRODUCT_ID	Table2.publication_title
2	ABC University	AAA-00001	In Search of Lost Time by Marcel Proust
3	ABC University	AAA-00001	The Odyssey by Homer
4	DEF University	AAA-00001	In Search of Lost Time by Marcel Proust
5	DEF University	AAA-00001	The Odyssey by Homer
6	ABC University	AAA-00002	Ulysses by James Joyce
7	ABC University	AAA-00002	The Brothers Karamazov by Fyodor Dostoyevsky
8	DEF University	AAA-00002	Ulysses by James Joyce
9	DEF University	AAA-00002	The Brothers Karamazov by Fyodor Dostoyevsky
10	ABC University	AAA-00003	Don Quixote by Miguel de Cervantes
11	ABC University	AAA-00003	Crime and Punishment by Fyodor Dostoyevsky
12	DEF University	AAA-00003	Don Quixote by Miguel de Cervantes
13	DEF University	AAA-00003	Crime and Punishment by Fyodor Dostoyevsky
14	ABC University	AAA-00004	The Great Gatsby by F. Scott Fitzgerald
15	ABC University	AAA-00004	Madame Bovary by Gustave Flaubert
16	DEF University	AAA-00004	The Great Gatsby by F. Scott Fitzgerald
17	DEF University	AAA-00004	Madame Bovary by Gustave Flaubert
18	ABC University	AAA-00005	One Hundred Years of Solitude by Gabriel Garcia Marquez
19	ABC University	AAA-00005	The Divine Comedy by Dante Alighieri
20	DEF University	AAA-00005	One Hundred Years of Solitude by Gabriel Garcia Marquez
21	DEF University	AAA-00005	The Divine Comedy by Dante Alighieri
22	DEF University	ZZZ-00001	Moby **** by Herman Melville
23	DEF University	ZZZ-00001	The Adventures of Huckleberry Finn by Mark Twain
24	DEF University	ZZZ-00002	War and Peace by Leo Tolstoy
25	DEF University	ZZZ-00003	Lolita by Vladimir Nabokov
26	DEF University	ZZZ-00004	Hamlet by William Shakespeare
27	DEF University	ZZZ-00005	The Catcher in the Rye by J. D. Salinger
28	DEF University	ZZZ-00002	Alice's Adventures in Wonderland by Lewis Carroll
29	DEF University	ZZZ-00003	Pride and Prejudice by Jane Austen
30	DEF University	ZZZ-00004	Wuthering Heights by Emily Brontë
31	DEF University	ZZZ-00005	To the Lighthouse by Virginia Woolf
Sheet4

 

[alansidman]

Additionally, if you like Peter's presentation then using the data in the above post, you can create a Power Pivot as follows

Book2
	A	B
3	Table2.publication_title	School
4	Alice's Adventures in Wonderland by Lewis Carroll	DEF University
5	Crime and Punishment by Fyodor Dostoyevsky	ABC University, DEF University
6	Don Quixote by Miguel de Cervantes	ABC University, DEF University
7	Hamlet by William Shakespeare	DEF University
8	In Search of Lost Time by Marcel Proust	ABC University, DEF University
9	Lolita by Vladimir Nabokov	DEF University
10	Madame Bovary by Gustave Flaubert	ABC University, DEF University
11	Moby **** by Herman Melville	DEF University
12	One Hundred Years of Solitude by Gabriel Garcia Marquez	ABC University, DEF University
13	Pride and Prejudice by Jane Austen	DEF University
14	The Adventures of Huckleberry Finn by Mark Twain	DEF University
15	The Brothers Karamazov by Fyodor Dostoyevsky	ABC University, DEF University
16	The Catcher in the Rye by J. D. Salinger	DEF University
17	The Divine Comedy by Dante Alighieri	ABC University, DEF University
18	The Great Gatsby by F. Scott Fitzgerald	ABC University, DEF University
19	The Odyssey by Homer	ABC University, DEF University
20	To the Lighthouse by Virginia Woolf	DEF University
21	Ulysses by James Joyce	ABC University, DEF University
22	War and Peace by Leo Tolstoy	DEF University
23	Wuthering Heights by Emily Brontë	DEF University
Sheet5

See this link for how to do it: Excel: Reporting Text in a Pivot Table - Strategic Finance

 

[Peter_SSs]

I think this should work, except it's cutting off the first letter of the first participant name

Yes, sorry, I changed one part of my code and forgot to change another corresponding part.

Try making this change to the code.

a(i, 1) = Mid(d(a(i, 1)), 3)
a(i, 1) = Mid(d(a(i, 1)), 2)