Pivot Table or lookup?

[CellShocked]

I have a list of 633 thousand entries that ties a movie number with all
the actors that were in it for about 166 thousand movie titles (numbers).

I want to reference that movie number, and extract a table or list of
only those entries that match that number from this list of all entries.

The returned list will be a list of numbers, which I will then derive a
list of names from an "actor's" sheet. This keeps the data set (file
size) smaller.

Movie_num Actor_num

1 22345
1 38425
2 12345
2 23456
2 34567
3 45678
3 56789
3 67890
3 78901

And then later...

Actor_num Actor_name

This one I can do.Those are just single lookups.

I just do not know how to extract a short list from a longer list.

 

[Ashish Mathur]

Hi,

If you wish, you may mail me the workbook at ask(at)ashishmathur(dot)com.
Since this would be a heavy file, please do not mail it to me. Upload the
file somewhere and then mail me the link.

Also, please explain the problem very clearly.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Tom Hutchins]

It sounds like an advanced filter might work well for your application. You
can use an advanced filter to copy a subset of data from a larger set of data
to another sheet. Debra Dalgleish has instructions on her Contextures site:

http://www.contextures.com/xladvfilter01.html

Hope this helps,

Hutch

 

[CellShocked]

I DID define it. Column one is a movie number in a 166k record movie
database. Column two is a number that references a list of actors.

So, the 633k record actor list will have duplicate occurrences of movie
numbers when more than one actor is in a movie, just like the sample data
shows.

Those actor numbers get looked up later. What I want to do is extract
the list of actor numbers a given (chosen) movie number has with it.

The returned list would simply be 1 to 20 (or whatever) numbers.

I would then use that list of numbers to grab the list of actor names.
Those particulars are not important.

The question is simply about extracting a small list of matching values
from a large list. A simple two column list. Put a movie number in a
cell, and a list of actor numbers appears which are matches from the
list.

 

[Ashish Mathur]

As suggested by Tom, use advanced fitlers

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[CellShocked]

As suggested by Tom, use advanced fitlers

I can form a pivot table on it, no problem. Kind of moot, since it is
only two columns of data.

If I could query that pivot table, the matching and sorting has already
been performed by it.

The pivot table shows movie numbers as row names, and each then get
separated by the number of actor in that movie, and shows their numbers.
Polling that table would be great.

1
actor num1
actor num2
actor num3
2
actor num1
actor num2
actor num3
actor num4
3
actor num6
actor num5
actor num7
actor num8
actor num9
actor num10
actor num17

ad infinitum to 166k movie records, yielding a 633k row pivot table.

It is no different than sorting the two column table on the movie number
field, which is probably more manipulable.

 

[CellShocked]

As suggested by Tom, use advanced fitlers

You do not seem to understand. I am not filtering data in those
sheets. I am looking up data *from* those sheets to fill space in a
different sheet.

I enter the movie num in my main view panel (sheet) and all the single
lookup data pops in easily. I want to ad the actor's listing.

So, what I want is a simple one-to-many lookup list to appear on my main
sheet from data on the other sheets based on the movie number I enter.
All in the same workbook. I do not want to move over to another
worksheet and manually filter it, I want it to 'pop' in, just like the
simple, single value, one-to-one lookups do.

 

[WallyWallWhackr]

Well, I want the lookup, AND the "copying" of the result to happen ON
the sheet I am in, grabbing the data from another sheet.


Examine this image to see where I have made lookups, and where I lack.
At the bottom of the screenshot, as usual, you can see the sheet names I
have used.

This should be a simple function not unlike one dropdown list that
relies on the selection made in another. That is really all I want,
except that instead of a drop down list, I simply want the list carried
into my sheet.

The image should make it obvious.

http://i255.photobucket.com/albums/hh143/WallyWallWhackr/screensht.jpg

 

[CellShocked]

Well, I want the lookup, AND the "copying" of the result to happen ON
the sheet I am in, grabbing the data from another sheet.


Examine this image to see where I have made lookups, and where I lack.
At the bottom of the screenshot, as usual, you can see the sheet names I
have used.

This should be a simple function not unlike one dropdown list that
relies on the selection made in another. That is really all I want,
except that instead of a drop down list, I simply want the list carried
into my sheet.

The image should make it obvious.

http://i255.photobucket.com/albums/hh143/WallyWallWhackr/screensht.jpg

Isn't selective assistance always nice?