Project almost complete, help with top 10 from drop down box

rob36

Board Regular
Joined
Jul 12, 2010
Messages
57
First off, thank you everyone that's helped me with my project thus far, its coming along great, and i'm nearing completion (My last day of interning here is August 13th :))

The question I have now is similar to questions I have asked in the past. I need allow for the user to enter in a year/week (a little modified, for this example week 24, 2009 will be 20924). Based upon that, it will look at a column on my excel sheet, and take the top 10 highest inventory values for that week, listing the item number.
here is the code I currently use to identify top 10:

Excel Workbook
CDEFGH
43
44Div Item NumberItem DescReceipts
45171691395 INCH BRASS,TWIST NOZZLE9840
4622213370BAGS, COMPACTOR PLASTIC.7168
4737112885LAWN BAGS,CRAFTSMAN LEAF5400
4842080007KN,EF-1 EXHAU.FILTER4350
4957133181ENVIRO-FLO,W 1 GALLONENVI4000
506917816FILTER, RED3900
517941581SCREWDRIVER, 3/16X4 IN3400
528941584SCREWDRIVER,1/4X6 IN3300
5395757788DISH DTV, BOX DTVPALDISH3300
541091138318V DRILL, DRVR CRAFTSMAN3202
Inbound
Excel 2002
Cell Formulas
RangeFormula
C45=ROW(A1)
C46=ROW(A2)
C47=ROW(A3)
C48=ROW(A4)
C49=ROW(A5)
C50=ROW(A6)
C51=ROW(A7)
C52=ROW(A8)
C53=ROW(A9)
C54=ROW(A10)
E45=VLOOKUP(D45, Inbound_Receipts!A:D, 4, FALSE)
E46=VLOOKUP(D46, Inbound_Receipts!A:D, 4, FALSE)
E47=VLOOKUP(D47, Inbound_Receipts!A:D, 4, FALSE)
E48=VLOOKUP(D48, Inbound_Receipts!A:D, 4, FALSE)
E49=VLOOKUP(D49, Inbound_Receipts!A:D, 4, FALSE)
E50=VLOOKUP(D50, Inbound_Receipts!A:D, 4, FALSE)
E51=VLOOKUP(D51, Inbound_Receipts!A:D, 4, FALSE)
E52=VLOOKUP(D52, Inbound_Receipts!A:D, 4, FALSE)
E53=VLOOKUP(D53, Inbound_Receipts!A:D, 4, FALSE)
E54=VLOOKUP(D54, Inbound_Receipts!A:D, 4, FALSE)
H45=LARGE(INDEX(Inbound_Receipts!$F$2:$IV$65000,,MATCH($D$40,Inbound_Receipts!$F$1:$IV$1,0)), ROW(A1))
H46=LARGE(INDEX(Inbound_Receipts!$F$2:$IV$65000,,MATCH($D$40,Inbound_Receipts!$F$1:$IV$1,0)), ROW(A2))
H47=LARGE(INDEX(Inbound_Receipts!$F$2:$IV$65000,,MATCH($D$40,Inbound_Receipts!$F$1:$IV$1,0)), ROW(A3))
H48=LARGE(INDEX(Inbound_Receipts!$F$2:$IV$65000,,MATCH($D$40,Inbound_Receipts!$F$1:$IV$1,0)), ROW(A4))
H49=LARGE(INDEX(Inbound_Receipts!$F$2:$IV$65000,,MATCH($D$40,Inbound_Receipts!$F$1:$IV$1,0)), ROW(A5))
H50=LARGE(INDEX(Inbound_Receipts!$F$2:$IV$65000,,MATCH($D$40,Inbound_Receipts!$F$1:$IV$1,0)), ROW(A6))
H51=LARGE(INDEX(Inbound_Receipts!$F$2:$IV$65000,,MATCH($D$40,Inbound_Receipts!$F$1:$IV$1,0)), ROW(A7))
H52=LARGE(INDEX(Inbound_Receipts!$F$2:$IV$65000,,MATCH($D$40,Inbound_Receipts!$F$1:$IV$1,0)), ROW(A8))
H53=LARGE(INDEX(Inbound_Receipts!$F$2:$IV$65000,,MATCH($D$40,Inbound_Receipts!$F$1:$IV$1,0)), ROW(A9))
H54=LARGE(INDEX(Inbound_Receipts!$F$2:$IV$65000,,MATCH($D$40,Inbound_Receipts!$F$1:$IV$1,0)), ROW(A10))
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself


What I am attempting to do, is to to provide the user a drop down box with the options of NC, FC, BP, ALL and Other. If they select ALL, then the top 10 will be taken as is. If they select NC, FC or BP however, then it will display only the top 10 of that specific merchandise type (see below, column E). Likewise, if it isn't NC, FC or BP, then Other will display all of the rest (the Other feature isnt too important, but its a bonus if it isn't too hard)



Excel Workbook
ABCDEFGHIJ
1DIVITEMDivItemDescMDS Type2082720828208292083020831
296500499650049PILLOW, JUMBONC
396670079667007QUILT T EV,ASSORT 1NC6012
496670089667008QUILT F/Q EV,ASSORT 1FC10824
596670099667009QUILT K EV,ASSORT 1BP22210218
696757409675740FL SS TW, REDNC
796757419675741FL SS FL, REDFC144
896757429675742FL SS QN, REDBP82458
996757439675743FL SS KG, REDNC76
Inbound_Receipts
Excel 2002



Any idea how I can modify my code, either with a match, or if statement or something similar to get it to work? thanks!

edit: note that that is just a small piece of my data, I have around 12000 rows total, so there will be more than 10 items
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Watch MrExcel Video

Forum statistics

Threads
1,133,325
Messages
5,658,170
Members
418,430
Latest member
Chlwls808

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top