How to sort multiple values in one cell?

[Wijsvlok]

I am making a table with movies and their movie Genres alongside some other information.
Is there a way that i can sort by one Movie Genre even though some movies have more than one Genre.
For example


The hungergames science-fiction, Fantasy
Harry potter Fantasy, adventure
The Avangers Science-fiction, Action

now i would want to be able to sort by Science-fiction and get only the 1 and 3 movie and if i sort by Fantasy in my table i want to have the 1st and the 2nd one
the problem I have now at the moment is that i can only sort by: "Science-fiction, Fantasy" _ "Fantasy, Adventure" and "Science-Fiction, Action" due to it being in the same cell

Thanks in Advance

 

[fjns]

Hi, see the linked file for a possible solution...
Wrong cells visible on Google Drive, because Google Drive does not know the FILTER function. The formula works correctly with your Office 365.

The formula used in the table...
=IFERROR(SORT(FILTER(Films!A:A;IFERROR(SEARCH(B1;Films!B:B&"#"&Films!C:C&"#"&Films!D:D);0)>0));"")

Films.xlsx

 

[Peter_SSs]

Welcome to the MrExcel board!

i would want to be able to sort by Science-fiction and get only the 1 and 3 movie

Do you really mean sort, or do you mean filter?

From your description, it sounds like you actually want to filter.
If so, in the Genre column use 'Text Filters' and 'Contains'

.. and enter Science-Fiction -> OK

Result:

 

[Wijsvlok]

Welcome to the MrExcel board!


Do you really mean sort, or do you mean filter?

View attachment 89803

From your description, it sounds like you actually want to filter.
If so, in the Genre column use 'Text Filters' and 'Contains'

View attachment 89801

.. and enter Science-Fiction -> OK

View attachment 89802

Result:

View attachment 89804

Thank you so much this helped really good!

 

[Wijsvlok]

Hi, see the linked file for a possible solution...
Wrong cells visible on Google Drive, because Google Drive does not know the FILTER function. The formula works correctly with your Office 365.

The formula used in the table...
=IFERROR(SORT(FILTER(Films!A:A;IFERROR(SEARCH(B1;Films!B:B&"#"&Films!C:C&"#"&Films!D:D);0)>0));"")

Films.xlsx

View attachment 89786

View attachment 89787

View attachment 89788

Thank you it works as good as the otherone, really great!

 

[fjns]

Hi, I am very glad that the formula works.
If I have enough knowledge, I am happy to help at any time.

 

[Peter_SSs]

Thank you so much this helped really good!

You're welcome.

If you are looking for a formula approach then a fairly simple one, using your existing table structure with multiple genres in the same column could be like this.

Wijsvlok.xlsm
	A	B	C	D	E	F	G	H
1								Genres
2		Movie	Genre		Genre	Science-Fiction		Science-Fiction
3		The Hungergames	Science-Fiction, Fantasy					Fantasy
4		Harry Potter	Fantasy, adventure		Films	The Hungergames		Adventure
5		The Avengers	Science-Fiction, Action			The Avengers		Action
6
7
Films

Cell Formulas
Range	Formula
F4:F5	F4	=FILTER(Table1[Movie],ISNUMBER(SEARCH(F2,Table1[Genre])))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
F2	List	=$H$2:$H$5

 

[fjns]

Hi, I created a new table for multi filters.
(Wrong cells visible on Google Drive, because Google Drive does not know the FILTER function. The formula works correctly with your Office 365.)

The new formulas used in the new table...
Films!E2: =IF(A2="","",SUM((INDIRECT("FilmsMultiFilter!C1:C"&COUNTA(Genres!A:A))="YES")*IFERROR(SEARCH(INDIRECT("FilmsMultiFilter!B1:B"&COUNTA(Genres!A:A)),INDIRECT("B$"&ROW(A2))&"#"&INDIRECT("C$"&ROW(A2))&"#"&INDIRECT("D$"&ROW(A2)))>0,FALSE))) (Range: E2:E7...)
FilmsMultiFilter!E2: =IF(AND(K$1<>"AND",K$1<>"OR"),"",IFERROR(SORT(FILTER(Films!A:A,IF(K$1="AND",Films!E:E=COUNTA(FILTER(B:B,C:C="YES")),IFERROR(VALUE(Films!E:E),0)>0))),""))

Films2.xlsx