Formula to Find Highest Values

[pwill]

Hi can anyone help with a Formula?

I am using column A1:A21 as a reference column and I have a list of values in column B1:B21.
I want to find the first 7 highest values in column B and have the results auto show in D1:J1 using the reference from column A

ie

the results in D1:J1 using the references from column A for the first 7 highest values in column B would be

	A	B	C	D	E	F	G	H	I	J	K	L
1	0	7		0	-1	6	-2	-3	-9	-10
2	1	2
3	-1	6
4	2	1
5	-2	4
6	3	0
7	-3	3
8	4	0
9	-4	0
10	5	1
11	-5	0
12	6	5
13	-6	0
14	7	0
15	-7	1
16	8	0
17	-8	1
18	9	0
19	-9	2
20	10	0
21	-10	2
22
23

<tbody>
</tbody>

Any help would be much appreciated

Regards
pwill

 

[pwill]

Just thought I would add that the same results could also be shown as

	A	B	C	D	E	F	G	H	I	J	K	L
1			0	-1	-2	-3	6	-9	-10
2
3

<tbody>
</tbody>

with the order following the references as in column B ie ref 6 has value 5 but could be placed as above

regards
pwill

 

[pwill]

Just thought I would add that the same results could also be shown as

	A	B	C	D	E	F	G	H	I	J	K	L
1			0	-1	-2	-3	6	-9	-10
2
3

<tbody>
</tbody>

with the order following the references as in column B ie ref 6 has value 5 but could be placed as above

regards
pwill

Sorry that should have been D1:J1

regards

 

[Peter_SSs]

Could we use some helper cells? I have used D2:J2. Both formulas copied across.

Book1
	A	B	C	D	E	F	G	H	I	J
1	0	7		0	-1	6	-2	-3	1	-9
2	1	2		7	6	5	4	3	2	2
3	-1	6
4	2	1
5	-2	4
6	3	0
7	-3	3
8	4	0
9	-4	0
10	5	1
11	-5	0
12	6	5
13	-6	0
14	7	0
15	-7	1
16	8	0
17	-8	1
18	9	0
19	-9	2
20	10	0
21	-10	2
High Values

Cell Formulas
Range	Formula
D1	=INDEX($A1:$A21,AGGREGATE(15,6,(ROW($A1:$A21)-ROW($A1)+1)/($B1:$B21=D2),COUNTIF($D2:D2,D2)))
D2	=LARGE($B$1:$B$21,COLUMNS($D2:D2))

 

[Aladin Akyurek]

In D1 control+shift+enter, not just enter, and copy across (as long as you need)...

=IFERROR(INDEX($A$1:$A$21,SMALL(IF($B$1:$B$21=LARGE($B$1:$B$21,COLUMNS($D$1:D1)),ROW($B$1:$B$21)-ROW($B$1)+1),
SUM(IF(LARGE($B$1:$B$21,COLUMN($D$1:D1)-COLUMN($D$1)+1)=LARGE($B$1:$B$21,COLUMNS($D$1:D1)),1)))),"")

 

[pwill]

Hi peter,

Thanks for your reply I will give this a try and let you know how I get on.

much appreciated

regards
pwill

 

[pwill]

In D1 control+shift+enter, not just enter, and copy across (as long as you need)...

=IFERROR(INDEX($A$1:$A$21,SMALL(IF($B$1:$B$21=LARGE($B$1:$B$21,COLUMNS($D$1:D1)),ROW($B$1:$B$21)-ROW($B$1)+1),
SUM(IF(LARGE($B$1:$B$21,COLUMN($D$1:D1)-COLUMN($D$1)+1)=LARGE($B$1:$B$21,COLUMNS($D$1:D1)),1)))),"")

Thanks Aladin Akyurek,

I will give this a try also and let you know how I get on.

much appreciated

regards
pwill

 

[pwill]

Thanks Peter and Aladin, both methods work fine,

much appreciated

regards
pwill

 

[Peter_SSs]

Thanks Peter and Aladin, both methods work fine,

much appreciated

regards
pwill

You are very welcome.

 

[Aladin Akyurek]

@pwill

You are welcome.