# Microsoft Office Application Help - Excel Help forum > Excel Charting & Pivots >  > [SOLVED] Combine multiple attributes of an object and display the result

## marian.gulie

Hello,
I've created an excel file containing movie titles and their genre (on multiple columns - one movie can have 2 genres), as seen in the attached file and bellow.
Movie Genre.xlsx
I want to automatically sort and display al movies (even the new ones that I'll add to the list) based on their genre. 
The genre should appear only once in the displayed table (pivot), but should be verified in both columns. 
The result can be also seen in that file.

               INPUT                                                       

TITLE...........GENRE......GENRE
MOVIE_1......GEN_1......GEN_2
MOVIE_2......GEN_1......GEN_3
MOVIE_3......GEN_2
MOVIE_4......GEN_3......GEN_4
MOVIE_5......GEN_4
MOVIE_6......GEN_5
MOVIE_7......GEN_1......GEN_4


RESULT
GEN_1......MOVIE_1
................MOVIE_7
................MOVIE_2
GEN_2......MOVIE_3
................MOVIE_1
GEN_3......MOVIE_4
................MOVIE_2
GEN_4......MOVIE_5
................MOVIE_7
................MOVIE_4
GEN_5......MOVIE_6

----------


## AliGW

Welcome to the forum.  :Smilie: 

How about this?

=LET(a,A5:B11,b,CHOOSECOLS(A5:C11,1,3),v,VSTACK(a,b),f,FILTER(v,INDEX(v,,2)<>0),c,CHOOSECOLS(f,2,1),SORTBY(c,INDEX(c,,1),1))

----------


## AliGW

Improved version:

=LET(a,A5:B11,b,CHOOSECOLS(A5:C11,1,3),v,VSTACK(a,b),f,FILTER(v,INDEX(v,,2)<>0),c,CHOOSECOLS(f,2,1),SORTBY(c,INDEX(c,,1),1,INDEX(c,,2),1))

Conditional formatting rule for H4:

=COUNTIF(H$4:H4,H4)>1

Applies to:

=$H$4:$H$400

----------


## marian.gulie

That worked perfectly.
Thank you.
I must now understand each step of the provided solution.  :Smilie:

----------


## AliGW

Just ask here if there's anything you don't get. Use EVALUATE FORMULA on the Formulas ribbon to help you.

Glad to have helped.  :Smilie: 

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

----------

