Solved

Top 10 items sold

Posted on 2013-06-17
8
188 Views
Last Modified: 2013-06-17
I'm trying to create a list of the top 10 items with this largest sales $$$ in my file. I know a pivot table would be the best approach but I don't want to utilize that for this file. I have a worksheet called "Database" where all my detail is stored. I'm using this formula to find the list but I also need to add a variable to it and only find the top 10 where column D = the value in cell A1. So when I need it to only look at the rows that the value in column D is the same in cell Al. So I need to modify this formula to account for this variable. Any ideas?

Column B = Item
Column I = Total Sales $$$

INDEX(B:B,MATCH(LARGE(I:I,ROW()-2),I:I,0))

Open in new window

0
Comment
Question by:Lawrence Salvucci
  • 4
  • 4
8 Comments
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
Perhaps?

=INDEX(B:B,MATCH(1,INDEX((I:I=LARGE(I:I,ROW()-2))*(D:D=A1),0),0))

but you may find it more efficient not to use whole column references, and instead limit the column sizes (e.g. B$1:B$1000).... as this is really an array formula.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
That works but how do I get it to find all the top 10 items and not just the top item?
0
 
LVL 23

Expert Comment

by:NBVC
Comment Utility
Maybe something like:

=INDEX($B$1:$B$10,MATCH(1,INDEX(($I$1:$I$10=LARGE($I$1:$I$10,ROWS($A$1:$A1)))*($D$1:$D$10=$A$1),0),0))

copied down.

adjust ranges to suit.

If still not right, please post sample workbook....
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
Ok let me try this new formula first. What is ROWS($A$1:$A1) represent? I didn't have that column in my original formula. Just not sure which column to use for that to try your new formula
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 23

Expert Comment

by:NBVC
Comment Utility
It's used as an incrementer for the k value in the LARGE() function.

e.g.  ROWS($A$1:$A1) is equal to 1 (so Largest)
 as you copy down, ROWS($A$1:$A2) is equal to 2 (so 2nd largest), etc...
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
Comment Utility
Ok I've attached a sample worksheet for you. I changed the columns around a bit so I could just create this sample file without all the other stuff that's in the file. But as you can see columns A, B, & C is where the data is. The match for column B is in cell G2. And then I need to list the top 10 items and sales $$$ in columns K & L.
Sample-File.xls
0
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
Comment Utility
Ok,

Try, in L2:

=LARGE((INDEX(($B$14:$B$182=$G$2)*($C$14:$C$182),0)),ROWS($L$2:$L2))

copied down

Then in K2:

=INDEX($A$14:$A$182,MATCH(1,INDEX(($B$14:$B$182=$G$2)*($C$14:$C$182=L2),0),0))

copied down.
0
 
LVL 1

Author Closing Comment

by:Lawrence Salvucci
Comment Utility
Works like a charm! Thank you for all your help! I greatly appreciate it!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now