Hello there,
The company i'm interning for has a database wih the first column containing 'div item number' , essentially labelling thousands of items with unique id's. Columns E onwards (currently on BD, will keep going though), contains the value of how many units of that item were picked each week. Each column begins with a year//week (ex: 201014 would be year 2010, week 14).
I am trying to create a user friendly sheet that can display the top 10 biggest ticket items for a specific week entered in by the user (they enter in the week and year, and i CONCATENATE). Currently to find the top items, I am using =LARGE(Inbound_Receipts!BD:BD, 2), where inbound receipts is the sheet with the data, and column BD was the most recent week.
I would like to make it so the column can be automatically updated based upon what they enter in. I was thinking that hlookup might be needed, with it finding the year/week as the target value, however I wasnt sure how to get it to find the row_index_number. I assume both Large and Match would need to be nested, please help!
I know that this is a lot, and i'm not sure if I made it understandable, but please ask me any quetions if clarifications is needed.
Excel 2002
I hope this helps. D40 can be ignored, that was a test value. Currently, you can see that my LARGE and VLOOKUP reference BD, which happens to be the most up to date column of my data. I would like it so that instead of BD, it will use the column of the year/row entered in (see F40)
here is the page that it links to, so you can see how it is currently set up, ignore that fact that 2009 is labeled like 209, thats another issue i'll work on (and much easier)
Excel 2002
lastly, i'm not exactly a pro with excel, so the more thorough the explanation, the better
The company i'm interning for has a database wih the first column containing 'div item number' , essentially labelling thousands of items with unique id's. Columns E onwards (currently on BD, will keep going though), contains the value of how many units of that item were picked each week. Each column begins with a year//week (ex: 201014 would be year 2010, week 14).
I am trying to create a user friendly sheet that can display the top 10 biggest ticket items for a specific week entered in by the user (they enter in the week and year, and i CONCATENATE). Currently to find the top items, I am using =LARGE(Inbound_Receipts!BD:BD, 2), where inbound receipts is the sheet with the data, and column BD was the most recent week.
I would like to make it so the column can be automatically updated based upon what they enter in. I was thinking that hlookup might be needed, with it finding the year/week as the target value, however I wasnt sure how to get it to find the row_index_number. I assume both Large and Match would need to be nested, please help!
I know that this is a lot, and i'm not sure if I made it understandable, but please ask me any quetions if clarifications is needed.
| Excel Workbook | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 36 | * Insert the week and year | ||||||||||
| 37 | 2010 | Year | |||||||||
| 38 | |||||||||||
| 39 | 24 | Week | 52 | ||||||||
| 40 | 20926 | 201024 | |||||||||
| 41 | |||||||||||
| 42 | Top Ten Products | ||||||||||
| 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 | 941584 | SCREWDRIVER,1/4X6 IN | 3300 | |||||||
| 54 | 10 | 911383 | 18V DRILL, DRVR CRAFTSMAN | 3202 | |||||||
Inbound | |||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| F39 | =MATCH(Inbound!D40,Inbound_Receipts!E1:IU1) | |
| F40 | =CONCATENATE(D37,D39) | |
| D45 | =INDEX(Inbound_Receipts!$1:$65536,MATCH(H45,Inbound_Receipts!$BD:$BD,0),1) | |
| D46 | =INDEX(Inbound_Receipts!$1:$65536,MATCH(H46,Inbound_Receipts!$BD:$BD,0),1) | |
| D47 | =INDEX(Inbound_Receipts!$1:$65536,MATCH(H47,Inbound_Receipts!$BD:$BD,0),1) | |
| D48 | =INDEX(Inbound_Receipts!$1:$65536,MATCH(H48,Inbound_Receipts!$BD:$BD,0),1) | |
| D49 | =INDEX(Inbound_Receipts!$1:$65536,MATCH(H49,Inbound_Receipts!$BD:$BD,0),1) | |
| D50 | =INDEX(Inbound_Receipts!$1:$65536,MATCH(H50,Inbound_Receipts!$BD:$BD,0),1) | |
| D51 | =INDEX(Inbound_Receipts!$1:$65536,MATCH(H51,Inbound_Receipts!$BD:$BD,0),1) | |
| D52 | =INDEX(Inbound_Receipts!$1:$65536,MATCH(H52,Inbound_Receipts!$BD:$BD,0),1) | |
| D53 | =INDEX(Inbound_Receipts!$1:$65536,MATCH(H53,Inbound_Receipts!$BD:$BD,0),1) | |
| D54 | =INDEX(Inbound_Receipts!$1:$65536,MATCH(H54,Inbound_Receipts!$BD:$BD,0),1) | |
| 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(Inbound_Receipts!BD:BD, 2) | |
| H46 | =LARGE(Inbound_Receipts!BD:BD, 3) | |
| H47 | =LARGE(Inbound_Receipts!BD:BD, 4) | |
| H48 | =LARGE(Inbound_Receipts!BD:BD, 5) | |
| H49 | =LARGE(Inbound_Receipts!BD:BD, 6) | |
| H50 | =LARGE(Inbound_Receipts!BD:BD, 7) | |
| H51 | =LARGE(Inbound_Receipts!BD:BD, 8) | |
| H52 | =LARGE(Inbound_Receipts!BD:BD, 9) | |
| H53 | =LARGE(Inbound_Receipts!BD:BD, 10) | |
| H54 | =LARGE(Inbound_Receipts!BD:BD, 11) | |
I hope this helps. D40 can be ignored, that was a test value. Currently, you can see that my LARGE and VLOOKUP reference BD, which happens to be the most up to date column of my data. I would like it so that instead of BD, it will use the column of the year/row entered in (see F40)
here is the page that it links to, so you can see how it is currently set up, ignore that fact that 2009 is labeled like 209, thats another issue i'll work on (and much easier)
| Excel Workbook | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | |||
| 1 | DIVITEM | Div | Item | Desc | 20827 | 20828 | 20829 | 20830 | 20831 | 20832 | 20833 | 20834 | ||
| 2 | 338457 | 3 | 38457 | JVC CAM,GZMG330RUS HDD | 156 | 112 | 56 | |||||||
| 3 | 338514 | 3 | 38514 | DCRSX40,SNY SD FLASH MP2 | ||||||||||
| 4 | 356028 | 3 | 56028 | SON CAM,DCRHC52 MINIDV | 120 | 130 | 140 | 110 | 160 | 160 | 250 | |||
| 5 | 356038 | 3 | 56038 | SON CAM,DCRDVD610 | 100 | 280 | 150 | 60 | 510 | 381 | ||||
| 6 | 356039 | 3 | 56039 | DVD CAM,SNY | ||||||||||
| 7 | 356049 | 3 | 56049 | SON CAM,HDRUX10HD DVD | 85 | 18 | 8 | 17 | 32 | 27 | ||||
| 8 | 356070 | 3 | 56070 | MHSCM1/V,SNY MP4 CAM-EGPL | ||||||||||
| 9 | 356078 | 3 | 56078 | SON CAM,DCRSR45 HDD | 500 | 180 | 55 | 135 | 140 | 280 | 230 | |||
Inbound_Receipts | ||||||||||||||
lastly, i'm not exactly a pro with excel, so the more thorough the explanation, the better
