Sorting Data

[mummbles74]

I have read a million different web pages and lost myself down a thousand rabit holes and I am not sure this is even possible but what I am looking to do is sort data by ascending values in Column Y but keep duplicate values in Column A together.

As a very crude example I would want the finished table to look like this:

Tag	Risk Rank
1	20
1	11
2	19
2	12
3	18
3	17
3	13
4	14
4	13

With it originally looking like this:

Tag	Risk Rank
1	20
2	19
3	18
3	17
4	14
3	13
4	13
2	12
1	11

 

[mummbles74]

I have created the formula that I want using the following:

=CONCATENATE("=LET(a,UNIQUE('Maintenance Data'!$A$2:$A$",'Defined Information'!C17,"),b,MAXIFS('Maintenance Data'!$Y$2:$Y$",'Defined Information'!C17,",'Maintenance Data'!$A$2:$A$",'Defined Information'!C17,",a),c,SORTBY(a,b,-1),d,SEQUENCE(ROWS(c)),e,XLOOKUP('Maintenance Data'!$A$2:$A$",'Defined Information'!C17,",c,d),SORTBY('Maintenance Data'!$A$2:$Y$",'Defined Information'!C17,",e,,'Maintenance Data'!$Y$2:$Y$",'Defined Information'!C17,",-1))")

where Defined Information C17 has a row count in it but this obviously only returns a text string, is there a way to use the INDIRECT function to create a formula from this?

 

[JamesCanale]

Is this because you don't want to see a bunch of zeros at the end of your output?

 

[JamesCanale]

This should accommodate less than 20,000 (or any larger number) rows of data without pesty zeros.

MrExcelPlayground8-1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	Tag																								Risk Rank		Tag	Risk Rank
2	1																								11		2	19
3	2																								12		2	12
4	3																								13		1	15
5	4																								13		1	11
6	4																								14		4	14
7	3																								10		4	13
8	3																								10		3	13
9	2																								19		3	10
10	1																								15		3	10
Sheet20

Cell Formulas
Range	Formula
AA2:AB10	AA2	=LET(x,OFFSET(A2,0,0,SUM(--(A2:A20000<>"")),1),y,OFFSET(Y2,0,0,SUM(--(Y2:Y20000<>"")),1),z,INDEX(OFFSET(A2,0,0,SUM(--(A2:A20000<>"")),25),SEQUENCE(SUM(--(A2:A20000<>""))),{1,25}),a,UNIQUE(x),b,MAXIFS(y,x,a),c,SORTBY(a,b,-1),d,SEQUENCE(ROWS(c)),e,XLOOKUP(x,c,d),SORTBY(z,e,,y,-1))
Dynamic array formulas.

 

[mummbles74]

This should accommodate less than 20,000 (or any larger number) rows of data without pesty zeros.

Thank you James, the formula is entered on a sheet called Sorted Data but references the range on a separate tab called maintenance Data, the formula transposes the data in the spread sheet on the first tab to a sorted version on the Sorted Data tab. as with the previous posts the data is then remerged. to present the data to the client in there required format and yes I am just trying to get rid of all the zeros at the bottom