merged cells and filter

[Guest]

I've been trying to list all my actors and their movies on an excel table... the problem is that some actors are in more than one movie. I would like to be able to have only one cell for the actor name but as many cells as there are movies. to do so, I tried to merge the cells... the problem is that if I do a filter by movie it's perfect, but if I do a filter by name, I only get the first movie, in addition, with the merge cells, I'm not able to sort the data anymore..
Does anyone know how to do it without reapiting the actor name

thanks for your help

 

[Frank Kabel]

Hi
this is one of the problems with merged cells. This is also a reason
why most people would recommend NOT to use merged cells. So I would
suggest to repeat the actor's name and NOT merge the cells.

 

[Robert Christie]

Hi Gaetan
One way, not recommended if spreadsheet is to be accessed
by others, but if for your personal use only; tedious to
complete but it will filter, look neater and not require
merging of cells.

Assuming first row is header row, ACTOR | MOVIE TITLE |
In A2 Actors name and in Column B2 B3..... the movies
he/she appeared in, lets say 5 movies.
Run Actors Name down to A6, then select Cells A3 to A6
and format background color to same as Text to hide. eg,
white on white.

HTH
Regards Bob C.

-----Original Message-----
I've been trying to list all my actors and their movies

on an excel table... the problem is that some actors are
in more than one movie. I would like to be able to have
only one cell for the actor name but as many cells as
there are movies. to do so, I tried to merge the cells...
the problem is that if I do a filter by movie it's
perfect, but if I do a filter by name, I only get the
first movie, in addition, with the merge cells, I'm not
able to sort the data anymore...

 

[Robert Christie]

Hi again Gaetan
To Speed up Text color change insert a helper column,
next to column containing actors names.
In row 2 of this column enter =EXACT(A1,A2) and filldown.
This will show FALSE in 2 row And TRUE in rows 3 to 6 in
helper column. Each time the actors name changes in
column A the FALSE will appear in helper column.
Use Autofilter and select TRUE.
Highlight remainig actor names and change cell text to
same as cell background color.
Remove Autofilter, select helper column and either delete
or hide column.

HTH
Regards Bob C.


is white

 

[BrianB]

My 2cents opinion is that you should use a "database" approach. Set up
simple data table that repeats names as often as necessary. You ca
then use the powerful Excel features to do the hard work and transfe
information to another sheet for formatting if necessary.

Also, avoid merged cells whenever possible. They bring big problems
as you have discovered

 

[Debra Dalgleish]

Instead of merging the cells, list the actors name in each row. Then,
you can sort and filter without problems.

To hide the duplicate names when the list is sorted by actor, you can
use conditional formatting:

http://www.contextures.com/xlCondFormat03.html#Duplicate

 

[Guest]

It's a good idea if you're seearching by name, unfortunately if you're looking at the movie, you'll get a white row... I've tried almost everything and when it's working in one direction it's not the case in the other hand... it's so frustrating..
anyway, thanks for your hel

gaetan