How to get a top 3 amount from a list that have several order

**lazyserv** · 08-11-2014, 03:22 AM

Hi there,

I need help for to get sorting out top 3 clients with the total amount.
Please refer the table below for clearer picture.

A	B	C
1	Company Name	Value
2	Apple	2100
3	Mango	4000
4	Melon	1000
5	Apple	600
6	Melon	1200
7	Strawberry	500

How do I show this in top 3 like this:

Top 3 Buyer
Mango	4000
Apple	2700
Melon	2200

I found that =LARGE only work for single cell name with single value instead of multiple cell that may contain same name but different value and add them up.
Maybe I'm wrong, as I'm not so proficiency with Excel. Kindly advise me which way I need to do to reach the end result.

Thank you so much for your help.

**martindwilson** · 08-11-2014, 03:32 AM

a pivot table would be ideal for this

**lazyserv** · 08-11-2014, 03:37 AM

Originally Posted by martindwilson

a pivot table would be ideal for this

Do you mind to share some example, but I still prefer excel coding as I got many types need to sort, if pivot table.. there may be a lot of table need to display

**samba_ravi** · 08-11-2014, 03:38 AM

=INDEX($B$2:$C$7,MATCH(LARGE(INDEX(($C$2:$C$7)-ROW($C$2:$C$7)*0.1^10,0),ROWS($1:1)),INDEX(($C$2:$C$7)-ROW($C$2:$C$7)*0.1^10,0),0),COLUMNS($A:A))
Try this and copy across

**lazyserv** · 08-11-2014, 03:50 AM

Originally Posted by nflsales

=INDEX($B$2:$C$7,MATCH(LARGE(INDEX(($C$2:$C$7)-ROW($C$2:$C$7)*0.1^10,0),ROWS($1:1)),INDEX(($C$2:$C$7)-ROW($C$2:$C$7)*0.1^10,0),0),COLUMNS($A:A))
Try this and copy across

it doesn't seems to be working.. please refer attached file.

is it I miss out any steps?

and I just got figure but without the name on the side.

thank you for assisting me

sample report.xlsx

**samba_ravi** · 08-11-2014, 03:52 AM

=INDEX($A$2:$B$7,MATCH(LARGE(INDEX(($B$2:$B$7)-ROW($B$2:$B$7)*0.1^10,0),ROWS($1:1)),INDEX(($B$2:$B$7)-ROW($B$2:$B$7)*0.1^10,0),0),COLUMNS($A:A))
Try this formula

**samba_ravi** · 08-11-2014, 03:53 AM

=INDEX($A$2:$B$7,MATCH(LARGE(INDEX(($B$2:$B$7)-ROW($B$2:$B$7)*0.1^10,0),ROWS($1:1)),INDEX(($B$2:$B$7)-ROW($B$2:$B$7)*0.1^10,0),0),COLUMNS($A:A))
Try this formula and copy across

**martindwilson** · 08-11-2014, 03:55 AM

i dont have 2003 to try and diferent pivot table options/ways of doing things are slightly different in excel versions

**lazyserv** · 08-11-2014, 04:02 AM

Originally Posted by nflsales

=INDEX($A$2:$B$7,MATCH(LARGE(INDEX(($B$2:$B$7)-ROW($B$2:$B$7)*0.1^10,0),ROWS($1:1)),INDEX(($B$2:$B$7)-ROW($B$2:$B$7)*0.1^10,0),0),COLUMNS($A:A))
Try this formula and copy across

But it doesn't add up Apple which have two amount, the correct amount for Apple should be 2100 + 600 = 2700.
Same goes to Melon. It just show top 3 highest value but doesn't add up other cells that have the same name but different value as mention above.

Need your advise.

Thank you.

Originally Posted by martindwilson

i dont have 2003 to try and diferent options/ways of doing things are slightly different in excel versions

I just upgraded to Excel 2007, therefore using Excel 2007 do we able to do it without pivot table?

Thank you.

**martindwilson** · 08-11-2014, 04:04 AM

why? a pivot will take up exactly the same space as a formula ,well maybe a row extra
you can place it anywhere you want.

**lazyserv** · 08-11-2014, 04:15 AM

Originally Posted by martindwilson

why? a pivot will take up exactly the same space as a formula ,well maybe a row extra
you can place it anywhere you want.

Do you mind to show me some example how to achieve the end result?

Thank you.

**martindwilson** · 08-11-2014, 05:01 AM

ok heres how you do a basic pivot it looks complicated but once you get used to it it takes about a minute

**samba_ravi** · 08-11-2014, 05:17 AM

see the attached file

**lazyserv** · 08-11-2014, 06:48 AM

Originally Posted by martindwilson

ok heres how you do a basic pivot it looks complicated but once you get used to it it takes about a minute

it's a bit confusing at first but nevertheless your detail explanation helps me out for the problem using pivot table.
+rep for you and thank you for assisting in my problem.

Originally Posted by nflsales

see the attached file

your solutions works great but the code makes me confusing, but nevertheless your solution solve my problem without using pivot table.
+rep for you as well for giving me a solution and assist in my problem.

Thank you to both of you.

**samba_ravi** · 08-11-2014, 07:03 AM

it's my pleasure and thanks for reputation

How to get a top 3 amount from a list that have several order

LinkBack

Thread Tools

Rate This Thread

Display

How to get a top 3 amount from a list that have several order

Re: How to get a top 3 amount from a list that have several order

Re: How to get a top 3 amount from a list that have several order

Re: How to get a top 3 amount from a list that have several order

Re: How to get a top 3 amount from a list that have several order

Re: How to get a top 3 amount from a list that have several order

Re: How to get a top 3 amount from a list that have several order

Re: How to get a top 3 amount from a list that have several order

Re: How to get a top 3 amount from a list that have several order

Re: How to get a top 3 amount from a list that have several order

Re: How to get a top 3 amount from a list that have several order

Re: How to get a top 3 amount from a list that have several order

Re: How to get a top 3 amount from a list that have several order

Re: How to get a top 3 amount from a list that have several order

Re: How to get a top 3 amount from a list that have several order

Thread Information

Users Browsing this Thread

Similar Threads

change filter list menu from alphabetical order to order of original list

Sum charge amount every time the code and the order number change

[SOLVED] Quantity in column D based on order amount of times the order no appears

[SOLVED] Probably a SUM formula for order no. to get the total amount of order

If order amount specified on product line list, pull line information to purchase order

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions