Filtering Text Data from Multiple columns

[Guest]

My mother created at a spreadsheet to document all her movies. The columns
include TITLE, GENRE, RELEASE DATE, and 12 separate columns for actors
(ACTOR_1, ACTOR_2, etc.). She has a second sheet in the workbook, with all
the actors listed. This has 3 columns (Name, Movie Title, and Release Date).

I'm adding my collection to her 'database' and she's mad that I won't enter
anything on the "ACTORS" sheet. If I'm looking for a specific actor, I just
use FIND, and FIND ALL to see what movies we have that they were in. The
question; is there a way to export the find results to a separate sheet? Or
a function/tool in Excel that would search across mutliple columns (for all
instances of Kevin Spacey) and easily show what movies we have that have him?
Does this question make sense?

Thanks to anyone who can help.

 

[Frank Kabel]

Hi
you may have a look at the following addin:
http://www.rondebruin.nl/easyfilter.htm

 

[Biff]

Hi!

This is relatively easy to do with formulas but try
Frank's suggestion first and see if that's what you really
want.

If you decide you want to go the formula route, just post
back and "we'll" fix you right up!

Biff

 

[Guest]

Thanks for the quick feedback (much quicker than I anticipated), but it won't
work. EasyFilter, at least in this iteration, specifically precludes
multiple column entries. I also wanted to clarify what I'm looking to do.
The spreadsheet with the movies contains 12 actor columns. When entering
movie data into each row, the actors are entered in order of billing. Using
Kevin Spacey as an example, three movies he appeared in would be entered like
this:

MODE TITLE ACTOR_1 ACTOR_2 [...] ACTOR_6
DVD AMERICAN BEAUTY SPACEY
DVD NEGOTIATOR, THE JACKSON SPACEY
DVD USUAL SUSPECTS, THE BALDWIN BYRNE [...] SPACEY

Currently, there are only 400 movies entered, but it's still a little
unwieldy. If I want to do a 'Kevin Spacey' movie marathon, how do I
-QUICKLY- hide all movies WITHOUT Spacey in them? It cannot be a formula
entered on the fly each time, as I need to make it idiot proof for non-excel
users. It can be a formula or non-VBA macro on another sheet in the
workbook, where all that needs to be entered is the search string and and
keystroke (or button).

Thanks again

 

[Biff]

Hi!

No problem, doesn't require an on the fly formula each
time.

Assume your Sheet1 is the 'database'.
Column headers are in row 1, A1:H1.
Movie titles in column B.
Actors in columns C:H.
The entire range of the database is A1:H400.

On Sheet2 is where you want to extract your data.
Sheet2 A1 is where you enter your search criteria.

Now, enter this array formula with the key combo of
CTRL,SHIFT,ENTER and copy it down to enough rows so that
all the possible matches found will be returned. I guess
that would be a judgement call on your part. If you have
400 movies, how many times will even the most popular
actor appear in 400 movies?

=IF(ISERROR(INDEX(Sheet1!$B$2:$B$400,SMALL(IF(Sheet1!
$C$2:$H$400=$A$1,ROW(INDIRECT("A$1:A$"&COUNTA(Sheet1!
$A$2:$A$500)))),ROW(1:1)))),"",INDEX(Sheet1!
$B$2:$B$400,SMALL(IF(Sheet1!$C$2:$H$400=$A$1,ROW(INDIRECT
("A$1:A$"&COUNTA(Sheet1!$A$2:$A$500)))),ROW(1:1))))

Don't let this formula "scare" you. There's nothing to it,
really.

If this seems to be too much for you, I can either put
together a sample file for you to study or if you'd like,
you can email a copy of your file and I'll see what I can
do with it.

If you go the email option you'll need to post an address
for me to contact you. My address as posted is bogus.

Or, you just might want to use filters!

Biff

-----Original Message-----
Thanks for the quick feedback (much quicker than I anticipated), but it won't
work. EasyFilter, at least in this iteration, specifically precludes
multiple column entries. I also wanted to clarify what I'm looking to do.
The spreadsheet with the movies contains 12 actor columns. When entering
movie data into each row, the actors are entered in order of billing. Using
Kevin Spacey as an example, three movies he appeared in would be entered like
this:

MODE TITLE ACTOR_1 ACTOR_2 [...] ACTOR_6
DVD AMERICAN BEAUTY SPACEY
DVD NEGOTIATOR, THE JACKSON SPACEY
DVD USUAL SUSPECTS, THE BALDWIN BYRNE [...] SPACEY

Currently, there are only 400 movies entered, but it's still a little
unwieldy. If I want to do a 'Kevin Spacey' movie marathon, how do I
-QUICKLY- hide all movies WITHOUT Spacey in them? It cannot be a formula
entered on the fly each time, as I need to make it idiot proof for non-excel
users. It can be a formula or non-VBA macro on another sheet in the
workbook, where all that needs to be entered is the search string and and
keystroke (or button).

Thanks again

Hi!

This is relatively easy to do with formulas but try
Frank's suggestion first and see if that's what you really
want.

If you decide you want to go the formula route, just post
back and "we'll" fix you right up!

Biff
the
workbook, with all mad
that I won't enter to a
separate sheet? Or we
have that have him?

.

 

[Frank Kabel]

Hi
in this case I really would try to use 'Data - Filter - autofilter'. This
should be easy to use for beginners as well

--
Regards
Frank Kabel
Frankfurt, Germany

Thanks for the quick feedback (much quicker than I anticipated), but it
won't
work. EasyFilter, at least in this iteration, specifically precludes
multiple column entries. I also wanted to clarify what I'm looking to do.
The spreadsheet with the movies contains 12 actor columns. When entering
movie data into each row, the actors are entered in order of billing.
Using
Kevin Spacey as an example, three movies he appeared in would be entered
like
this:

MODE TITLE ACTOR_1 ACTOR_2 [...] ACTOR_6
DVD AMERICAN BEAUTY SPACEY
DVD NEGOTIATOR, THE JACKSON SPACEY
DVD USUAL SUSPECTS, THE BALDWIN BYRNE [...] SPACEY

Currently, there are only 400 movies entered, but it's still a little
unwieldy. If I want to do a 'Kevin Spacey' movie marathon, how do I
-QUICKLY- hide all movies WITHOUT Spacey in them? It cannot be a formula
entered on the fly each time, as I need to make it idiot proof for
non-excel
users. It can be a formula or non-VBA macro on another sheet in the
workbook, where all that needs to be entered is the search string and and
keystroke (or button).

Thanks again

Hi!

This is relatively easy to do with formulas but try
Frank's suggestion first and see if that's what you really
want.

If you decide you want to go the formula route, just post
back and "we'll" fix you right up!

Biff

 

[Debra Dalgleish]

You can add a new column to the move list, and check for an actor. For
example:

In cell O1, add the heading "Check"
In cell O2, type the formula: =COUNTIF(C2:E2,$I$1)>0
Copy the formula down to the last row of data
In cell I1, type an actor name
Select a cell in the table, and choose Data>Filter>AutoFilter
From the dropdown list in the Check column heading, select TRUE
The selected actor's movies will be visible.

Thanks for the quick feedback (much quicker than I anticipated), but it won't
work. EasyFilter, at least in this iteration, specifically precludes
multiple column entries. I also wanted to clarify what I'm looking to do.
The spreadsheet with the movies contains 12 actor columns. When entering
movie data into each row, the actors are entered in order of billing. Using
Kevin Spacey as an example, three movies he appeared in would be entered like
this:

MODE TITLE ACTOR_1 ACTOR_2 [...] ACTOR_6
DVD AMERICAN BEAUTY SPACEY
DVD NEGOTIATOR, THE JACKSON SPACEY
DVD USUAL SUSPECTS, THE BALDWIN BYRNE [...] SPACEY

Currently, there are only 400 movies entered, but it's still a little
unwieldy. If I want to do a 'Kevin Spacey' movie marathon, how do I
-QUICKLY- hide all movies WITHOUT Spacey in them? It cannot be a formula
entered on the fly each time, as I need to make it idiot proof for non-excel
users. It can be a formula or non-VBA macro on another sheet in the
workbook, where all that needs to be entered is the search string and and
keystroke (or button).

Thanks again

:

Hi!

This is relatively easy to do with formulas but try
Frank's suggestion first and see if that's what you really
want.

If you decide you want to go the formula route, just post
back and "we'll" fix you right up!

Biff



movies. The columns


columns for actors


workbook, with all


Title, and Release Date).


that I won't enter


specific actor, I just


they were in. The


separate sheet? Or


mutliple columns (for all


have that have him?