Data Sorting Two Colums:

[The Shadowman]

I have two columns that I would like to sort so that a best score matches the name achieving the best score. The first column is a simple list of names in text format, the second uses the RANK formula to list the scores taken from another part of the sheet. It works quite well, but the scores, for example, first place is not at the top, but down the list next to the name who achieved that score and so on for 2nd 3rd etc. What I would like is for Excel to place the best score at the top with the rest decending. At the same time the top scoring name should be moved up as well. Please see uploaded image.

No matter what I try I can not make it work. Is it possible, please?

Thanks in advance

Robert

 

[jasonb75]

It should be possible but we will not be able to do it from a screen capture, we would need the sample to be posted using XL2BB so that we can copy the data along with the existing formulas to excel for testing.

 

[jasonb75]

Without seeing what you have and knowing your exact criteria, this is just a best guess mock up of what I think you are trying to do.

Book1 (version 2).xlsb
	A	B	C	D	E	F
1	Name	Pos			Name	Score
2	e	6			a	38
3	a	5			b	35
4	b	4			c	33
5	c	2			d	33
6	d	2			e	44
7	f	1			f	31
Sheet6

Cell Formulas
Range	Formula
A2:A7	A2	=INDEX($E:$E,AGGREGATE(15,6,ROW($E$2:$E$7)/($F$2:$F$7=LARGE($F$2:$F$7,ROWS(A$2:A2))),COUNTIF(B$2:B2,B2)))
B2:B7	B2	=RANK(LARGE($F$2:$F$7,ROWS(B$2:B2)),$F$2:$F$7,1)

 

[The Shadowman]

Without seeing what you have and knowing your exact criteria, this is just a best guess mock up of what I think you are trying to do.

Book1 (version 2).xlsb
	A	B	C	D	E	F
1	Name	Pos			Name	Score
2	e	6			a	38
3	a	5			b	35
4	b	4			c	33
5	c	2			d	33
6	d	2			e	44
7	f	1			f	31
Sheet6

Cell Formulas
Range	Formula
A2:A7	A2	=INDEX($E:$E,AGGREGATE(15,6,ROW($E$2:$E$7)/($F$2:$F$7=LARGE($F$2:$F$7,ROWS(A$2:A2))),COUNTIF(B$2:B2,B2)))
B2:B7	B2	=RANK(LARGE($F$2:$F$7,ROWS(B$2:B2)),$F$2:$F$7,1)

Hi Jasonb

The formula does the job. However, it puts the best score at the bottom and the worst at the top. It’s a complicated formula for my level of expertise. Would you mind explaining what I need to change to get the best score at the top And so on.

Many thanks for your help, it’s appreciated

Robert

 

[jasonb75]

If you change LARGE to SMALL in both formulas it should reverse the order.

 

[The Shadowman]

If you change LARGE to SMALL in both formulas it should reverse the order.

Hi thanks I think this will work, but in A2 after ROW what does the 15, 6 represent. On my sheet I have 15 players ie 15 rows required

Robert

 

[jasonb75]

what does the 15, 6 represent.

Those are function parameters which do not need to be changed to match your data. If you read the excel help file information on the AGGREGATE function it will tell you what the options are and what they do.

 

[The Shadowman]

Hi Jasonb

I have managed to get the position column working, but the player column shows #REF! at the top and then #NUM! In the rest below. I’m not sure if I have done it correctly. I used your formula in the player column and referenced the column with the text player names in. Any idea what I am doing wrong.

thanks

Robert

 

[The Shadowman]

For info this is the formula I have entered C26 is the names column I use C26 to C40

=INDEX($C26:$C26,AGGREGATE(15,6,ROW($C26:$C$40)/($BD$26:$BD$40=SMALL($BD$26,ROWS(CP$26:CP26))),COUNTIF(CY$26:CY26,CY26)))

Robert

 

[The Shadowman]

Hi again.
I have managed to find an error in my formula which has now been corrected. Sorry it took so long to find it. It now works as it should

thank you for all your help

Robert