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

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

<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

Name	Value
Nashid	200145
Jabed	184198
Eman	156622

<tbody>
</tbody>

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

Name	Value
Rubel	172569
Hasin	152304
Monem	104463

<tbody>
</tbody>

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

Name	Value
Romel	168251
Nasim	136357
Hasan	120410

<tbody>
</tbody>

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
1	Group	Name	Value
2	1	Nashid	200,145
3	1	Kashem	188,516
4	1	Jabed	188,516

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

Name	Value
Nashid	200145
Jabed	184198
Eman	156622

<tbody>
</tbody>

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
1	Group	Name	Value
2	1	Nashid	200,145
3	1	Kashem	188,516
4	1	Jabed	188,516

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:56px;"><col style="width:73px;"><col style="width:65px;"></colgroup><tbody>
</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))