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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,298
Messages
5,510,471
Members
408,791
Latest member
bwirth

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top