Help for formula to find Top 3 value in excel

emran06

New Member
Joined
Mar 13, 2011
Messages
27
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

GroupNameValue
1Nashid200145
2Kashem188516
1Jabed184198
2Rubel172569
3Romel168251
1Eman156622
2Hasin152304
3Nasim136357
3Hasan120410
2Monem104463
1Rashed88516
3Nizam72569

<tbody>
</tbody>

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
NameValue
Nashid200145
Jabed184198
Eman156622

<tbody>
</tbody>

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

<tbody>
</tbody>

If group number is 3 in sheet1 then top 3 value will be like this-
NameValue
Romel168251
Nasim136357
Hasan120410

<tbody>
</tbody>

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

Best Regards

Emran
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

DILIPandey

Well-known Member
Joined
Jul 25, 2013
Messages
1,336
HI Emran,

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


Regards,
DILIPandey
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,500
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1GroupNameValue
21Nashid200,1451|1
32Kashem188,5162|2
41Jabed184,1981|2
52Rubel172,5692|4
63Romel168,2513|1
71Eman156,6221|3
82Hasin188,5162|3
93Nasim136,3573|2
103Hasan120,4103|3
112Monem504,4632|1
121Rashed88,5161|4
133Nizam72,5693|4
14
Sheet1





Formula in A4 copied across and down.

Excel Workbook
AB
1Group2
2
3NameValue
4Monem504,463
5Kashem188,516
6Hasin188,516
7
Sheet2
 

emran06

New Member
Joined
Mar 13, 2011
Messages
27
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

*ABCDE
1GroupNameValue**
21Nashid200,145*1|1
32Kashem188,516*2|2
41Jabed184,198*1|2
52Rubel172,569*2|4
63Romel168,251*3|1
71Eman156,622*1|3
82Hasin188,516*2|3
93Nasim136,357*3|2
103Hasan120,410*3|3
112Monem504,463*2|1
121Rashed88,516*1|4
133Nizam72,569*3|4
14*****

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
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

*AB
1Group2
2**
3NameValue
4Monem504,463
5Kashem188,516
6Hasin188,516
7**

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
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-

NameValue
Nashid200145
Jabed184198
Eman156622

<tbody>
</tbody>

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

New Member
Joined
Apr 14, 2014
Messages
17

ADVERTISEMENT

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
Group2
NameValue
Kashem188516
Rubel172569
Hasin152304

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>
 

emran06

New Member
Joined
Mar 13, 2011
Messages
27
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

New Member
Joined
Mar 13, 2011
Messages
27

ADVERTISEMENT

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

Thanks again

Emran
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,500
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1GroupNameValue
21Nashid2001451|1
32Kashem1885162|1
41Jabed1841981|2
52Rubel1725692|2
63Romel1682513|1
71Eman1566221|3
82Hasin1523042|3
93Nasim1363573|2
103Hasan1204103|3
112Monem1044632|4
121Rashed885161|4
133Nizam725693|4
Sheet1




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

Excel Workbook
AB
1Group1
2
3NameValue
4Nashid200,145
5Jabed184,198
6Eman156,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).
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,500
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,500
Office Version
  1. 365
Platform
  1. Windows
Hi ravi 1986,
Thanks for your reply. Its working.
Your call, but I'd be careful using those formulas. :eek:

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
ABC
1GroupNameValue
21Nashid200,145
31Kashem188,516
41Jabed188,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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,579
Messages
5,637,209
Members
416,961
Latest member
sigrid6940

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top