Multiple Words in one cell/sorting

[DF]

Hi,
I'm a novice with Excel...I've created a spreadsheet with
2 columns - Movie titles and Categories (i.e. Action,
Comedy, Thriller etc). I know how to sort, but I have
some movies with multiple categories such as
Action/Comedy. I'd like the movie to appear in both
categories when I go to sort. Is there any type of
formula I can create so the movie would appear in both
categories? Right now it sorts to whichever category I
have listed first. Thanks

 

[PTT, Inc.]

Not realistically, depending on the "separator" you use and how consistent
the data is. Your best bet might be to break up the "Category" into
"Primary Category" and "Secondary Category". You might be able to use the
"Text to column" feature under the "Data" menu to split the Category column
into two columns.

I know you can get to the data using the MID function, but I'm not sure if
it can be sorted based on what it finds. Maybe someone else will know of
another way, but by splitting this out and making dropdowns using "Data",
"Validation", "Allow", "List", you can certainly simplify your selections
and speed up the process.

Bill Foley
www.pttinc.com

 

[Candice Mesk]

Even better than sorting (for this instance) is an AutoFilter. Read up on
how to do it. You can then type as many categories into the same cell as you
like and do a custom filter on the category column to list only those
entries where a particular genre is contained (alone or not). That way if
you are looking for Comedy, the list will show Comedy, Comedy drama, Action
Comedy and so forth.

I have included a tutorial for you.

Regards,
Candice

 

[Bob Phillips]

Creating helper columns for both categories would be easy, and you could add
buttons top sort by primary or secondary, but I am confused as to how an
item would appear in both categories as it would only exist once, and so
however you sorted, it would either appear in category A or category B.

It seems to me that if you want to do what you want, you need to create the
entry twice, once with category A, and then with category B. Sorting then
becomes trivial.

Or am I missing something?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Candice Mesk]

I wrote a tutorial in Word, but Microsoft won't allow me to send it to their
newsgroup. If you are interested, please e-mail me at (e-mail address removed)
and I can send it to you directly.

 

[Candice Mesk]

Bob and Bill, do you think my idea is better? I thought about it, and I
think it's the best way to do it. But I would think that...

 

[Bob Phillips]

Candice,

Autofilter is good, but I am not sure that it would suit the question , as
the OP wants to include an item in many categories. So for instance, if a
film is Action and Western, the filter would have to have at least one of
these words. Say he chose Action, then all Westerns (except the duplicates)
would be excluded. This may or may not suit, but is not exactly what the OP
asked (they often don't ask for what they really want<vbg>).

IMO, he still needs a sorting solution, so that he can see all items in the
order he wants, but he would benefit from adding an Autofilter so as to be
more specific, and home in on categories.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Candice Mesk]

Thank you Bob for your feedback...

Some people really want Office to do thing Microsoft hasn't even thought of
yet!! I find this quite often and I'm not always sure if it's possible

 

[Bob Phillips]

You are so right! The good thing is that the these things are so often
possible.

Regards

Bob

 

[DF]

Thanks Candice, let me try that route..that may be the
ticket. I did not see the tutorial...is that something
you can attach here? If not, please e-mail to
(e-mail address removed). Thanks for everyone's input on this

 

[DF]

Hi again,

I was able to do the auto filter!!! That worked perfect.
By utilizing the custom option I'm able to pick one topic
say comedy and it will list all the movies with that word
contained in my category...that is exactly what I needed.

Thanks everyone!!