Getting All Headers Name in Sorted based on Sorted Rows Values of Table

[GrandZeno]

Hi All Experts,

Today is my first day of registration and requirement, though i am learning from forum from past 6 months.
Right now i am working on a file which has Weekdays as Column Header, Current Price as a Row Header and PriceVariations as data.
so i need a formula to get when Current Price & weekdays based on sorted price.

I have attached my sample excel file with solution of Supportive Columns.
but i am looking for a formula output in single cell without supporting Columns.
may be my array function attempts in sample file could be modified to get desired output.


Please support and thanks in advance.

 

[p45cal]

Any cell in row 4:

=TEXTJOIN(" | ",FALSE,TAKE(SORT(VSTACK({" X ","Mon","Tue","Wed","Thu","Fri","Sat","Sun"},C4:J4),2,-1,TRUE),1))

or

=TEXTJOIN(" | ",FALSE,TAKE(SORT(VSTACK(SUBSTITUTE($L$3:$S$3,"Price"," X "),C4:J4),2,-1,TRUE),1))

or

=TEXTJOIN(" | ",FALSE,TAKE(SORT(VSTACK(SUBSTITUTE(LEFT($C$3:$J$3,3),"Cur"," X "),C4:J4),2,-1,TRUE),1))

copy down.

If you don't have VSTACK or TAKE then

=TEXTJOIN(" | ",FALSE,INDEX(SORT(CHOOSE({1;2},SUBSTITUTE(LEFT($C$3:$J$3,3),"Cur"," X "),C4:J4),2,-1,TRUE),1,0))

Or in a single cell in row 4:

=BYROW(C4:J24,LAMBDA(a,TEXTJOIN(" | ",FALSE,TAKE(SORT(VSTACK(SUBSTITUTE(LEFT(C3:J3,3),"Cur"," X "),a),2,-1,TRUE),1))))

which will spill down so no copy down needed.

 

[vletm]

GrandZeno​

What kind of result do You looking for Product Code 007?
There are two duplicated values 13.80 & 12.30.
... then the result could be like Wed | Mon & Tue | X | Thu | Fri & Sun | Sat

 

[Peter Bartholomew]

Broadly speaking

= BYROW(priceVariation, LAMBDA(variation,
    TEXTJOIN(" | ",,SORTBY(day, variation,-1))
  ))

will return the result you require.