Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

Top 10 items sold

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
Lawrence Salvucci
Asked:
Lawrence Salvucci
  • 4
  • 4
1 Solution
 
NBVCCommented:
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
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
That works but how do I get it to find all the top 10 items and not just the top item?
0
 
NBVCCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
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
 
NBVCCommented:
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
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
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
 
NBVCCommented:
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
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Works like a charm! Thank you for all your help! I greatly appreciate it!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now