Help for formula to find Top 3 value in excel

[emran06]

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

[TABLE="width: 205"]
<tbody>[TR]
[TD]Group[/TD]
[TD]Name[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Nashid[/TD]
[TD]200145[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Kashem[/TD]
[TD]188516[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Jabed[/TD]
[TD]184198[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Rubel[/TD]
[TD]172569[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Romel[/TD]
[TD]168251[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Eman[/TD]
[TD]156622[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Hasin[/TD]
[TD]152304[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Nasim[/TD]
[TD]136357[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Hasan[/TD]
[TD]120410[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Monem[/TD]
[TD]104463[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Rashed[/TD]
[TD]88516[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Nizam[/TD]
[TD]72569 [/TD]
[/TR]
</tbody>[/TABLE]

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
[TABLE="width: 141"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Nashid[/TD]
[TD]200145[/TD]
[/TR]
[TR]
[TD]Jabed[/TD]
[TD]184198[/TD]
[/TR]
[TR]
[TD]Eman[/TD]
[TD]156622[/TD]
[/TR]
</tbody>[/TABLE]

If group number is 2 in sheet1 then top 3 value will be like this-
Group2
[TABLE="width: 141"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Rubel[/TD]
[TD]172569[/TD]
[/TR]
[TR]
[TD]Hasin[/TD]
[TD]152304[/TD]
[/TR]
[TR]
[TD]Monem[/TD]
[TD]104463[/TD]
[/TR]
</tbody>[/TABLE]

If group number is 3 in sheet1 then top 3 value will be like this-
[TABLE="width: 141"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Romel[/TD]
[TD]168251[/TD]
[/TR]
[TR]
[TD]Nasim[/TD]
[TD]136357[/TD]
[/TR]
[TR]
[TD]Hasan[/TD]
[TD]120410[/TD]
[/TR]
</tbody>[/TABLE]

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

Best Regards

Emran

 

[DILIPandey]

HI Emran,

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


Regards,
DILIPandey

 

[Peter_SSs]

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
2
3	Name	Value
4	Monem	504,463
5	Kashem	188,516
6	Hasin	188,516
7
Sheet2

 

[emran06]

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
Name	*	*
Nashid	*	1|1
Kashem	*	2|2
Jabed	*	1|2
Rubel	*	2|4
Romel	*	3|1
Eman	*	1|3
Hasin	*	2|3
Nasim	*	3|2
Hasan	*	3|3
Monem	*	2|1
Rashed	*	1|4
Nizam	*	3|4
*	*	*	*	*

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]Group[/TD]

[TD="align: right"]Value[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]200,145[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #99cc00, align: right"]2[/TD]

[TD="bgcolor: #99cc00, align: right"]188,516[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]184,198[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]172,569[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]168,251[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]156,622[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="bgcolor: #99cc00, align: right"]2[/TD]

[TD="bgcolor: #99cc00, align: right"]188,516[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]136,357[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]120,410[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]504,463[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]88,516[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]72,569[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

</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
*	*
Name	Value
Monem
Kashem
Hasin
*	*

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Group[/TD]
[TD="align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]504,463[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]188,516[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]188,516[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

</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-

[TABLE="class: cms_table, width: 141"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Nashid[/TD]
[TD]200145[/TD]
[/TR]
[TR]
[TD]Jabed[/TD]
[TD]184198[/TD]
[/TR]
[TR]
[TD]Eman[/TD]
[TD]156622[/TD]
[/TR]
</tbody>[/TABLE]

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]

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
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]Group[/TD]
[TD="class: xl67, width: 64"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"]Name[/TD]
[TD="class: xl65"]Value[/TD]
[/TR]
[TR]
[TD]Kashem[/TD]
[TD="align: right"]188516[/TD]
[/TR]
[TR]
[TD]Rubel[/TD]
[TD="align: right"]172569[/TD]
[/TR]
[TR]
[TD]Hasin[/TD]
[TD="align: right"]152304[/TD]
[/TR]
</tbody>[/TABLE]

 

[emran06]

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]

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

Thanks again

Emran

 

[Peter_SSs]

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]

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]

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.