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

 

[Ingolf]

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

And this formula result will be incorrect.
If column C have the same value for two different people, but the same group, formula returns a false result.

[TABLE="width: 144"]
<colgroup><col width="64" style="width: 48pt;" span="3"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Group[/TD]
[TD="width: 64, bgcolor: transparent"]Name[/TD]
[TD="width: 64, bgcolor: transparent"]Value[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]Nashid[/TD]
[TD="bgcolor: transparent, align: right"]200145[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]Kashem[/TD]
[TD="bgcolor: transparent, align: right"]188516[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]Jabed[/TD]
[TD="bgcolor: transparent, align: right"]184198[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]Rubel[/TD]
[TD="bgcolor: transparent, align: right"]172569
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]Romel[/TD]
[TD="bgcolor: transparent, align: right"]168251[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]Eman[/TD]
[TD="bgcolor: transparent, align: right"]156622[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]Hasin[/TD]
[TD="bgcolor: transparent, align: right"]172569[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]Nasim[/TD]
[TD="bgcolor: transparent, align: right"]136357[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]Hasan[/TD]
[TD="bgcolor: transparent, align: right"]120410[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]Monem[/TD]
[TD="bgcolor: transparent, align: right"]104463[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]Rashed[/TD]
[TD="bgcolor: transparent, align: right"]88516[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]Nizam[/TD]
[TD="bgcolor: transparent, align: right"]72569
[/TD]
[/TR]
</tbody>[/TABLE]


and result will be:

[TABLE="width: 96"]
<colgroup><col width="64" style="width: 48pt;" span="2"> <tbody>[TR]
[TD="width: 64, bgcolor: yellow"]Group[/TD]
[TD="width: 64, bgcolor: #FFC000"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]Name[/TD]
[TD="bgcolor: yellow"]Value[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Kashem[/TD]
[TD="bgcolor: transparent, align: right"]188516[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Rubel[/TD]
[TD="bgcolor: transparent, align: right"]172569[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Rubel[/TD]
[TD="bgcolor: transparent, align: right"]172569[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

I have not noticed already PeterSS said the same thing.

As well as me raising this issue (on at least 3 occasions I think), perhaps you had also missed this from the OP?

In my data sheet there is no two people from the same group and same value.

If correct, it obviates the need to worry about the issue.

 

[Ingolf]

As well as me raising this issue (on at least 3 occasions I think), perhaps you had also missed this from the OP? If correct, it obviates the need to worry about the issue.

Yes, I saw the OP requirement but my response was to ravi 1986, in response to post number 20 "check Below the formula" ...

 

[Peter_SSs]

Yes, I saw the OP requirement but my response was to ravi 1986, in response to post number 20 "check Below the formula" ...

Fair enough, but your statement "And this formula result will be incorrect" is itself incorrect given the OPs statement of fact regarding duplicates, and that is all I was trying to point out - perhaps not directly enough.

Having said that, I think I would be implementing a solution that did allow for duplicates, just in case, hence my suggestions earlier in the thread.