Sort By

[akalien]

Greetings to all,

I have a file where in 9 columns (C8:K36) either the same status may appear or several different status may appear.
I have as well a custom list Z9:Z17

Is there any way to get a Sortby based on my custom list if the status is found once within the 9 columns and not only in the first of my 9 columns?

Many thanks in advance

 

[kvsrinivasamurthy]

Pl post data in XL2BB format.

 

[akalien]

Unfortunately not possible as many things blocked in office.

Here's the actual formula for my whole sheeet I have if this may help:

=SORTBY($A$8:$K$70;MATCH($C$8:$C$70;$Z8:$Z$31;0);1)

 

[kvsrinivasamurthy]

In 2019 version SORTBY function is not available.

 

[popipipo]

You can make you own sortlist:

Excel options
Advanced
General
Edit custom lists

 

[akalien]

Sorry but by now I have 365

 

[kvsrinivasamurthy]

In M8

=SORTBY(A8:K24,MATCH(C8:C24,Z8:Z17,0),1,MATCH(D8:D24,Z8:Z17,0),1,MATCH(E8:E24,Z8:Z17,0),1,MATCH(F8:F24,Z8:Z17,0),1,MATCH(G8:G24,Z8:Z17,0),1,MATCH(H8:H24,Z8:Z17,0),1,MATCH(I8:I24,Z8:Z17,0),1,MATCH(J8:J24,Z8:Z17,0),1,MATCH(K8:K24,Z8:Z17,0),1)

I have taken last row as 24. Change it as required.

 

[akalien]

Great Job MANY THANKS AAA+++

 

[kvsrinivasamurthy]

Welcome.

 

[djclements]

SORT could also be a possibility (with a few other functions):

=DROP(SORT(HSTACK(MATCH(C8:K70;Z8:Z31;0);A8:K70);SEQUENCE(;COLUMNS(C8:K70)));;COLUMNS(C8:K70))