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

 

[rollis13]

It can be done from your menu bar 'Sort and Filter' by 'Custom Sorting' indicating two levels.
Select 'With Headings' then for the first level select column 'Tag', sort 'A-Z' then add second level with column 'Risk Rank', sort 'Z-A'.

 

[mummbles74]

Sorry, my crude example made it appear simpler than it is. The tag numbers may not always be in ascending order when sorted by risk rank.

So original data like this:

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

I would want it to look like this:

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

 

[C Moore]

How about this...sort by risk rank, pull unique tags, and then join all ranks.

scratchbook excel testing.xlsm
	A	B	C	D	E
1	Tag	Risk Rank		Tag	Risk Rank
2	2	20		2	20, 17, 12
3	3	19		3	19, 13
4	1	18		1	18, 11
5	2	17		4	14, 13
6	4	14
7	3	13
8	4	13
9	2	12
10	1	11
Sheet22

Cell Formulas
Range	Formula
D2:D5	D2	=UNIQUE($A$2:$A$10)
E2:E5	E2	=TEXTJOIN(", ",TRUE,IF($A$2:$A$10=D2,$B$2:$B$10,""))
Dynamic array formulas.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[rollis13]

The tag numbers may not always be in ascending order when sorted by risk rank.

Sorry, I have no idea how to handle it unless you use a macro, but what a problem would there be to have the 'Tag' column sorted ?

 

[JamesCanale]

If you have the 365 (see fluff above) - but I generally do this for my own amusement anyway.

MrExcelPlayground8-1.xlsx
	A	B	C	D	E
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
D2:E10	D2	=LET(a,UNIQUE(A2:A10),b,MAXIFS(B2:B10,A2:A10,a),c,SORTBY(a,b,-1),d,SEQUENCE(ROWS(c)),e,XLOOKUP(A2:A10,c,d),SORTBY(A2:B10,e,,B2:B10,-1))
Dynamic array formulas.

 

[mummbles74]

Sorry, I have no idea how to handle it unless you use a macro, but what a problem would there be to have the 'Tag' column sorted ?

I am happy to use a Macro if you know code that will work. All help appreciated

 

[mummbles74]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Updated thank you.

 

[mummbles74]

If you have the 365 (see fluff above) - but I generally do this for my own amusement anyway.

James Thank you for this I managed to use the formula above, modified it slightly to reflect the maximum range of data that I have and it gives me everything I needed. The only improvement I would like to make is to assist with the next stages of what the complete sheet does.
The hole reason for sorting the data this way is so that duplicate tag numbers can then be remerged reflecting the highest risk range so that clients can prioritise their repairs. All of this works fine, what I would like to do is be able to count the rows in Column A of the maintenance Data sheet that have a tag number (can be text or number) and replace this count in place of the 20000 row number in the formula. is this at all possible either with a formula or VBA?

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

Thanks for all the support so far everyone, I am just 1 step away, I think