Lookups: One Value --> Many Results

[Josh]

Scenario:

Column B contains 229 unique artists from b4:b1046. Column C contains
the 1,043 songs recorded by these 229 artists (c4:c1046). Obviously
many artists have multiple songs on the list. The range b4:c1046 is
defined as top1043.

I'd like to create a formula that will automatically display (filter?)
the list to show me the breakdowns of which artists performed which
songs (i.e. under "Beatles," which appears in column B, show me the 70
songs recorded by them appearing in column C).

I've had limited success with VLOOKUP, but it only returns the first
song it finds and ignores the remainder.

I know I could simply use a drop-down filter and trudge through artist
by artist, but I'm looking to define a formula that will make my work
more efficient.

I've also tinkered with PivotTables, but haven't gotten that to work
for me yet.

Any help would be greatly appreciated.

Thanks.

 

[Frank Kabel]

Hi Josh
IMHO filerting is the best you can do in this case. A formula does not
seem to achieve what you're trying to do.
First sort your data by column B and the use a Filter

 

[Peo Sjoblom]

You can use a filter and a macro assigned to a button that will open an
input box that will filter for this particular artist
and another button to reset the filter. I made one for work for our phone
lists and where you would enter an area code to filter on.. Here's an
example

Sub Artist()
Application.ScreenUpdating = False
UserVal = Application.InputBox("Enter Artist")
If UserVal = False Then
Exit Sub
Else
Selection.AutoFilter Field:=1, Criteria1:="*" & UserVal & "*",
Operator:=xlAnd
End If
Application.ScreenUpdating = True
End Sub

Sub Reset_Filter()
Application.ScreenUpdating = False
For Each sh In Worksheets
If sh.FilterMode Then
On Error Resume Next
sh.ShowAllData
End If
Next
Range("A1").Select
Application.ScreenUpdating = True
End Sub

The first macro selects the artist, regardless if there is The Beatles or
just Beatles
the second will reset the filter and select A1

Selection.AutoFilter Field:=1

means it is the first column


--
No private emails please, for everyone's
benefit keep the discussion in the newsgroup.


Regards,

Peo Sjoblom

 

[Josh]

Thanks, Frank.

Actually, I've discovered that a PivotTable captures the
data I seek. Unfortunately, I can't seem to get the
layout right. The PivotTable tells me, in fact, that The
Beatles have appear 70 times, and captures the titles of
their 70 songs, but displaying them properly is now the
issue. Back to the drawing board.

Josh

 

[Guest]

Peo:

Thanks for the quick reply. Your suggestion is just a wee
bit beyond my understanding of Excel.

I can't imagine there isn't a more simple method to
accomplish my goal. It's fairly straightforwad, no?

Thanks again.

Josh

 

[Peo Sjoblom]

You can do it with fairly complicated formulas but the easiest way would
probably
to use a macro and autofilter/advanced filter

--
No private emails please, for everyone's
benefit keep the discussion in the newsgroup.


Regards,

Peo Sjoblom