Is it possible to sort table column values and display the header value in the next set of table columns

[vampsthevampyre]

Evening Everybody

I'm looking to sort values in a table and then display the results sorted in the next table columns.

The table below shows what I'm looking to achieve with row 5 being Col1, Col2, Col3.

Tried a couple of things but keep getting spill errors, any help would be appreciated

Regards

Ian

roulette.xlsx
	D	E	F	G	H	I	J
3	ID	Col1	Col2	Col3	1stCol	2ndCol	3Col
4	1	1	2	3	Col1	Col2	Col3
5	2	2	3	2	Col1	Col3	Col2
6	3	3	2	1	Col3	Col2	Col1
7	4	3	1	1	Col2	Col3	Col1
8	5	0	1	3
Sheet19

Cell Formulas
Range	Formula
D4:D8	D4	=ROW()-ROW(Table72[#Headers])

 

[hagia_sofia]

Hello, please test what @djclements recently posted in another thread:

=LET(
a,E4:G8,
IFNA(IF(1,WRAPROWS(SORTBY(TOCOL(a),TOCOL(IF({1},SEQUENCE(ROWS(a)),a)),1,TOCOL(ISNA(a)),1,TOCOL(a),1),COLUMNS(a)),a),""))

 

[vampsthevampyre]

Hello, please test what @djclements recently posted in another thread:

=LET(
a,E4:G8,
IFNA(IF(1,WRAPROWS(SORTBY(TOCOL(a),TOCOL(IF({1},SEQUENCE(ROWS(a)),a)),1,TOCOL(ISNA(a)),1,TOCOL(a),1),COLUMNS(a)),a),""))

It sort of works but still gets a spill array if placed in the table H4, also it only gives the values not the column headers

 

[hagia_sofia]

Is the output supposed to be within offical Excel table?

 

[vampsthevampyre]

Is the output supposed to be within offical Excel table?

I was hoping it could be

 

[hagia_sofia]

So, something like:

=XLOOKUP(SMALL(Table72[@[Col1]:[Col3]],1),Table72[@[Col1]:[Col3]],Table72[[#Headers],[Col1]:[Col3]])

=IF(XLOOKUP(SMALL(Table72[@[Col1]:[Col3]],2),Table72[@[Col1]:[Col3]],Table72[[#Headers],[Col1]:[Col3]])=[@1stCol],XLOOKUP(SMALL(Table72[@[Col1]:[Col3]],2),Table72[@[Col1]:[Col3]],Table72[[#Headers],[Col1]:[Col3]],,,-1),XLOOKUP(SMALL(Table72[@[Col1]:[Col3]],2),Table72[@[Col1]:[Col3]],Table72[[#Headers],[Col1]:[Col3]]))

=IF(XLOOKUP(SMALL(Table72[@[Col1]:[Col3]],3),Table72[@[Col1]:[Col3]],Table72[[#Headers],[Col1]:[Col3]])=[@1stCol],XLOOKUP(SMALL(Table72[@[Col1]:[Col3]],3),Table72[@[Col1]:[Col3]],Table72[[#Headers],[Col1]:[Col3]],,,-1),XLOOKUP(SMALL(Table72[@[Col1]:[Col3]],3),Table72[@[Col1]:[Col3]],Table72[[#Headers],[Col1]:[Col3]]))

 

[djclements]

Hello, please test what @djclements recently posted in another thread:

=LET(
a,E4:G8,
IFNA(IF(1,WRAPROWS(SORTBY(TOCOL(a),TOCOL(IF({1},SEQUENCE(ROWS(a)),a)),1,TOCOL(ISNA(a)),1,TOCOL(a),1),COLUMNS(a)),a),""))

That variant was customized for a situation involving #N/A errors. The same method could be applied here, but I might rewrite it like this:

=LET(
    idλ, LAMBDA(n,TOCOL(CHOOSE(n,Table72[[#Headers],[Col1]:[Col3]],Table72[ID]))),
    data, Table72[[Col1]:[Col3]],
    cols, COLUMNS(data),
    VSTACK(SEQUENCE(,cols)&"_Col",WRAPROWS(SORTBY(idλ({1}),idλ({2}),1,TOCOL(data),1),cols))
)

It's unfortunate the OP is asking to return the results within the same table. It would be fine in a neighboring column to the right of the table, in my opinion, but your alternative formulas for the three calculated columns should do the trick.

 

[Peter_SSs]

your alternative formulas for the three calculated columns should do the trick.

I don't think so unless sample data like row 9 below is not possible.

vampsthevampyre.xlsm
	D	E	F	G	H	I	J
3	ID	Col1	Col2	Col3	1stCol	2ndCol	3Col
4	1	1	2	3	Col1	Col2	Col3
5	2	2	3	2	Col1	Col3	Col2
6	3	3	2	1	Col3	Col2	Col1
7	4	3	1	1	Col2	Col3	Col1
8	5	0	1	3	Col1	Col2	Col3
9	6	2	2	2	Col1	Col3	Col3
Sheet1

Cell Formulas
Range	Formula
H4:H9	H4	=XLOOKUP(SMALL(Table72[@[Col1]:[Col3]],1),Table72[@[Col1]:[Col3]],Table72[[#Headers],[Col1]:[Col3]])
I4:I9	I4	=IF(XLOOKUP(SMALL(Table72[@[Col1]:[Col3]],2),Table72[@[Col1]:[Col3]],Table72[[#Headers],[Col1]:[Col3]])=[@1stCol],XLOOKUP(SMALL(Table72[@[Col1]:[Col3]],2),Table72[@[Col1]:[Col3]],Table72[[#Headers],[Col1]:[Col3]],,,-1),XLOOKUP(SMALL(Table72[@[Col1]:[Col3]],2),Table72[@[Col1]:[Col3]],Table72[[#Headers],[Col1]:[Col3]]))
J4:J9	J4	=IF(XLOOKUP(SMALL(Table72[@[Col1]:[Col3]],3),Table72[@[Col1]:[Col3]],Table72[[#Headers],[Col1]:[Col3]])=[@1stCol],XLOOKUP(SMALL(Table72[@[Col1]:[Col3]],3),Table72[@[Col1]:[Col3]],Table72[[#Headers],[Col1]:[Col3]],,,-1),XLOOKUP(SMALL(Table72[@[Col1]:[Col3]],3),Table72[@[Col1]:[Col3]],Table72[[#Headers],[Col1]:[Col3]]))

These would be my alternatives.

vampsthevampyre.xlsm
	D	E	F	G	H	I	J
3	ID	Col1	Col2	Col3	1stCol	2ndCol	3Col
4	1	1	2	3	Col1	Col2	Col3
5	2	2	3	2	Col1	Col3	Col2
6	3	3	2	1	Col3	Col2	Col1
7	4	3	1	1	Col2	Col3	Col1
8	5	0	1	3	Col1	Col2	Col3
9	6	2	2	2	Col1	Col2	Col3
Sheet1

Cell Formulas
Range	Formula
H4:H9	H4	=INDEX(SORTBY(Table72[[#Headers],[Col1]:[Col3]],Table72[@[Col1]:[Col3]]),1)
I4:I9	I4	=INDEX(SORTBY(Table72[[#Headers],[Col1]:[Col3]],Table72[@[Col1]:[Col3]]),2)
J4:J9	J4	=INDEX(SORTBY(Table72[[#Headers],[Col1]:[Col3]],Table72[@[Col1]:[Col3]]),3)