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

"CellShocked" <cellshocked@thecellvalueattheendofthespreadsheet. org wrote
in message ...

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.

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.






On Mon, 26 Oct 2009 19:26:34 +0530, "Ashish Mathur"
wrote:

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

"CellShocked" <cellshocked@thecellvalueattheendofthespreadsheet. org wrote
in message ...

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.

As suggested by Tom, use advanced fitlers

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"CellShocked" <cellshocked@thecellvalueattheendofthespreadsheet. org wrote
in message ...

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.






On Mon, 26 Oct 2009 19:26:34 +0530, "Ashish Mathur"
wrote:

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

"CellShocked" <cellshocked@thecellvalueattheendofthespreadsheet. org wrote
in message ...

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.

On Tue, 27 Oct 2009 09:16:16 +0530, "Ashish Mathur"
wrote:

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.

On Tue, 27 Oct 2009 09:16:16 +0530, "Ashish Mathur"
wrote:

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.