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 2002
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 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
)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 | ||||||||
|---|---|---|---|---|---|---|---|---|
| C | D | E | F | G | H | |||
| 43 | ||||||||
| 44 | Div Item Number | Item Desc | Receipts | |||||
| 45 | 1 | 7169139 | 5 INCH BRASS,TWIST NOZZLE | 9840 | ||||
| 46 | 2 | 2213370 | BAGS, COMPACTOR PLASTIC. | 7168 | ||||
| 47 | 3 | 7112885 | LAWN BAGS,CRAFTSMAN LEAF | 5400 | ||||
| 48 | 4 | 2080007 | KN,EF-1 EXHAU.FILTER | 4350 | ||||
| 49 | 5 | 7133181 | ENVIRO-FLO,W 1 GALLONENVI | 4000 | ||||
| 50 | 6 | 917816 | FILTER, RED | 3900 | ||||
| 51 | 7 | 941581 | SCREWDRIVER, 3/16X4 IN | 3400 | ||||
| 52 | 8 | 941584 | SCREWDRIVER,1/4X6 IN | 3300 | ||||
| 53 | 9 | 5757788 | DISH DTV, BOX DTVPALDISH | 3300 | ||||
| 54 | 10 | 911383 | 18V DRILL, DRVR CRAFTSMAN | 3202 | ||||
Inbound | ||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| 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!
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 | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | DIVITEM | Div | Item | Desc | MDS Type | 20827 | 20828 | 20829 | 20830 | 20831 | ||
| 2 | 9650049 | 96 | 50049 | PILLOW, JUMBO | NC | |||||||
| 3 | 9667007 | 96 | 67007 | QUILT T EV,ASSORT 1 | NC | 60 | 12 | |||||
| 4 | 9667008 | 96 | 67008 | QUILT F/Q EV,ASSORT 1 | FC | 108 | 24 | |||||
| 5 | 9667009 | 96 | 67009 | QUILT K EV,ASSORT 1 | BP | 222 | 102 | 18 | ||||
| 6 | 9675740 | 96 | 75740 | FL SS TW, RED | NC | |||||||
| 7 | 9675741 | 96 | 75741 | FL SS FL, RED | FC | 144 | ||||||
| 8 | 9675742 | 96 | 75742 | FL SS QN, RED | BP | 82 | 458 | |||||
| 9 | 9675743 | 96 | 75743 | FL SS KG, RED | NC | 76 | ||||||
Inbound_Receipts | ||||||||||||
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
