Help for formula to find Top 3 value in excel

emran06 · Apr 20, 2014

Dear Experts,
I am facing problem to make summary sheet of top 3 value from large number of value of my excel sheet like-
Sheet1

Group	Name	Value
1	Nashid	200145
2	Kashem	188516
1	Jabed	184198
2	Rubel	172569
3	Romel	168251
1	Eman	156622
2	Hasin	152304
3	Nasim	136357
3	Hasan	120410
2	Monem	104463
1	Rashed	88516
3	Nizam	72569

Now I want to make summary in sheet2 top 5 value among the list of sheet1 separately from group 1, 2 & 3.
If group number is 1 in sheet1 then top 3 value will be like this-
Group1

Name	Value
Nashid	200145
Jabed	184198
Eman	156622

If group number is 2 in sheet1 then top 3 value will be like this-
Group2

Name	Value
Rubel	172569
Hasin	152304
Monem	104463

If group number is 3 in sheet1 then top 3 value will be like this-

Name	Value
Romel	168251
Nasim	136357
Hasan	120410

Please help me if anyone know the formula. Thank you in advance for your help.

Best Regards

Emran

DILIPandey · Apr 20, 2014

HI Emran,

Try looking at LARGE function where you can get top 1 /2 /3 values easily.

Regards,
DILIPandey

Peter_SSs · Apr 20, 2014

If your Values in sheet 1 are always in descending order like your sample, the problem is a bit easier, but I've assumed they may not be in order so I've changed the sample data a bit.
Notice that I have also allowed for ties within groups - see green cells.

Formula in E2 copied down as far as you might ever need. (Column E could then be hidden if you want)

Excel Workbook

A

B

C

D

E

1

Group

Name

Value

2

1

Nashid

200,145

1|1

3

2

Kashem

188,516

2|2

4

1

Jabed

184,198

1|2

5

2

Rubel

172,569

2|4

6

3

Romel

168,251

3|1

7

1

Eman

156,622

1|3

8

2

Hasin

188,516

2|3

9

3

Nasim

136,357

3|2

10

3

Hasan

120,410

3|3

11

2

Monem

504,463

2|1

12

1

Rashed

88,516

1|4

13

3

Nizam

72,569

3|4

14

Sheet1

Formula in A4 copied across and down.

Excel Workbook

A

B

1

Group

2

3

Name

Value

4

Monem

504,463

5

Kashem

188,516

6

Hasin

188,516

7

Sheet2

emran06 · Apr 21, 2014

Peter_SSs said:
If your Values in sheet 1 are always in descending order like your sample, the problem is a bit easier, but I've assumed they may not be in order so I've changed the sample data a bit.
Notice that I have also allowed for ties within groups - see green cells.

Formula in E2 copied down as far as you might ever need. (Column E could then be hidden if you want)

Sheet1

* A B C D E
1 Group Name Value * *
2 1 Nashid 200,145 * 1|1
3 2 Kashem 188,516 * 2|2
4 1 Jabed 184,198 * 1|2
5 2 Rubel 172,569 * 2|4
6 3 Romel 168,251 * 3|1
7 1 Eman 156,622 * 1|3
8 2 Hasin 188,516 * 2|3
9 3 Nasim 136,357 * 3|2
10 3 Hasan 120,410 * 3|3
11 2 Monem 504,463 * 2|1
12 1 Rashed 88,516 * 1|4
13 3 Nizam 72,569 * 3|4
14 * * * * *

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula
E2 =A2&"|"&COUNTIFS(A$2:A$13,A2,C$2:C$13,">"&C2)+COUNTIFS(A$2:A2,A2,C$2:C2,C2)

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Formula in A4 copied across and down.

Sheet2

* A B
1 Group 2
2 * *
3 Name Value
4 Monem 504,463
5 Kashem 188,516
6 Hasin 188,516
7 * *

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula
A4 =INDEX(Sheet1!B$2:B$13,MATCH($B$1&"|"&ROWS(A$4:A4),Sheet1!$E$2:$E$13,0))

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Dear Sir,
Thank you very much for your help. Actually I wanted formula for top 3 value (it may be top 10 or more) from each group. I would find name with vlookup formula. But I need top 3 value.
If group number is 1 then top 3 large value will be as follows-

Name	Value
Nashid	200145
Jabed	184198
Eman	156622

I have used large formula and it working great but problem is, it find top value among the all data but my requirement is top 3 within the group.

Regards

Emran

ravi 1986 · Apr 21, 2014

in sheet2 put in Cell A1 Group and in Cell B1 put data validation of 1,2 and 3
then in A2 type "Name", in B2 type "Value"
and in cell A3 put the formula : =INDEX(Sheet1!$B$2:$C$13,MATCH(LARGE(($B$1=Sheet1!$A$2:$A$13)*(Sheet1!$C$2:$C$13),ROW(A1)),Sheet1!$C$2:$C$13,0),MATCH(A$2,Sheet1!$B$1:$C$1,0)) with CSE

Group	2
Name	Value
Kashem	188516
Rubel	172569
Hasin	152304

emran06 · Apr 21, 2014

DILIPandey said:
HI Emran,

Try looking at LARGE function where you can get top 1 /2 /3 values easily.

Regards,
DILIPandey

Thanks for your comments, Actually I used large function and it working great but I need large to small from each group.

emran06 · Apr 21, 2014

Hi ravi 1986,
Thanks for your reply. Its working.

Thanks again

Emran

Peter_SSs · Apr 21, 2014

emran06 said:
Dear Sir,
Thank you very much for your help. Actually I wanted formula for top 3 value (it may be top 10 or more) from each group. I would find name with vlookup formula. But I need top 3 value.

With your original data ..
(remember column E can be hidden)

Excel Workbook

A

B

C

D

E

1

Group

Name

Value

2

1

Nashid

200145

1|1

3

2

Kashem

188516

2|1

4

1

Jabed

184198

1|2

5

2

Rubel

172569

2|2

6

3

Romel

168251

3|1

7

1

Eman

156622

1|3

8

2

Hasin

152304

2|3

9

3

Nasim

136357

3|2

10

3

Hasan

120410

3|3

11

2

Monem

104463

2|4

12

1

Rashed

88516

1|4

13

3

Nizam

72569

3|4

Sheet1

.. and searching for Group 1 my results are ...

Excel Workbook

A

B

1

Group

1

2

3

Name

Value

4

Nashid

200,145

5

Jabed

184,198

6

Eman

156,622

7

Sheet2

Could you explain in what way this is not what you asked for so that I can modify where necessary?

If you want more than 3, copy the formulas down further.

If you want all the results from the given group say so (but it isn't what you asked for).

Peter_SSs · Apr 21, 2014

Peter_SSs said:
If your Values in sheet 1 are always in descending order like your sample, the problem is a bit easier ...

You also didn't comment on this part of my earlier post, which could affect the simplest way to get the required results.

Peter_SSs · Apr 21, 2014

emran06 said:
Hi ravi 1986,
Thanks for your reply. Its working.

Your call, but I'd be careful using those formulas.

a) Is it ever possible that two people from the same group could have the same value? Check the results for top 3 for Group 1 with this data in Sheet1.

Excel Workbook

A

B

C

1

Group

Name

Value

2

1

Nashid

200,145

3

1

Kashem

188,516

4

1

Jabed

188,516

Sheet1

b) It may not happen, but what if somebody later decides to insert a new row 1 into Sheet2, say for some extra headings, when using Ravi's formula. Try it and look at the results.

Help for formula to find Top 3 value in excel

emran06

New Member

Excel Facts

DILIPandey

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

emran06

New Member

ravi 1986

New Member

emran06

New Member

emran06

New Member

Peter_SSs

MrExcel MVP, Moderator

Peter_SSs

MrExcel MVP, Moderator

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Help for formula to find Top 3 value in excel

New Member

Excel Facts

Well-known Member

MrExcel MVP, Moderator

New Member

New Member

New Member

New Member

MrExcel MVP, Moderator

MrExcel MVP, Moderator

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock