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

 

[emran06]

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.

Sheet1

*	A	B	C
Name
Nashid
Kashem
Jabed

<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="align: right"]1[/TD]

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

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

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

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4






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.

Sir,
In my data sheet there is no two people from the same group and same value. Group 1 value is
[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]

and so ravi's formula is working.

 

[Peter_SSs]

Sir,
In my data sheet there is no same value.

and so ravi's formula is working.

OK, as I said it is your call. So long as that will never happen, and so long as no rows are ever inserted.


You also never answered the question that I asked twice about whether the values in column B of Sheet1 are always in descending order? If they are, then there is a simpler formula available.
(I'm not trying to stop you using ravi's formula but to provide the best/simplest solution for you.)

 

[ravi 1986]

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.

Sheet1

*	A	B	C
Name
Nashid
Kashem
Jabed

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:56px;"><col style="width:73px;"><col style="width:65px;"></colgroup><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="align: right"]1[/TD]

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

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

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

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4






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.

Thanks Mr. Peter,

if someone want to insert new row in sheet2 than we can use below formula:
=INDEX(Sheet1!$B$2:$C$13,MATCH(LARGE(($B$1=Sheet1!$A$2:$A$13)*(Sheet1!$C$2:$C$13),ROW(INDIRECT("A"&COUNTA($A$2:$A2)))),Sheet1!$C$2:$C$13,0),MATCH(A$2,Sheet1!$B$1:$C$1,0)) with CSE

 

[ravi 1986]

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

Thanks again

Emran

Your welcome friend

 

[Peter_SSs]

if someone want to insert new row in sheet2 than we can use below formula:
=INDEX(Sheet1!$B$2:$C$13,MATCH(LARGE(($B$1=Sheet1!$A$2:$A$13)*(Sheet1!$C$2:$C$13),ROW(INDIRECT("A"&COUNTA($A$2:$A2)))),Sheet1!$C$2:$C$13,0),MATCH(A$2,Sheet1!$B$1:$C$1,0)) with CSE

We could, or I think we could use the less volatile & simpler ..

=INDEX(Sheet1!B$2:B$13,MATCH(LARGE(IF(Sheet1!$A$2:$A$13=$B$1,Sheet1!$C$2:$C$13),ROWS(A$3:A3)),Sheet1!$C$2:$C$13,0)) with CSE

.. but in both cases we still have to hope equal values in a group never exist.

(.. or we can use the helper column with even simpler, non-array, formulas )

 

[ravi 1986]

Yaa, there are many way to solve the same problem

 

[Fazza]

Indeed. Non-formula solutions even - such as pivot table.

 

[Ingolf]

@ravi,

result of your formula is wrong. See the situation when displayed for group 2
if value in column 2 is equal
Group 2

Name Value
Monem 504,463
Kashem 188,516
Kashem 188,516


would be

Group 2

Name Value
Monem 504 463
Kash 188 516
Hasin 188 516

 

[Ingolf]

I apologize for the previous post. I have not noticed already PeterSS said the same thing.

 

[ravi 1986]

check the below formula
=INDEX(Sheet1!$B$2:$C$13,MATCH(LARGE(($B$1=Sheet1!$A$2:$A$13)*(Sheet1!$C$2:$C$13),COUNTA($A$3:$A3)),Sheet1!$C$2:$C$13,0),MATCH(A$3,Sheet1!$B$1:$C$1,0))