I have the following dummy table from which I need to determine two different sets of maximum value and their corresponding information.
The first set of values I need to find is the highest value in Column S "Store" of the table and return that value into Cell V2. If there are multiple entries of the same value, then it needs to be the most recent of those according to the date in Column A "Date" of the table. Cells V3 & V4 need to provide the corresponding Date and Day value from the row that the highest value is located in respectively. So for the example table below it needs to return the following:
Cell V2: 60
Cell V3: 12-Feb-59
Cell V4: Wednesday
The second set of values is a bit more complicated. I need to look at all of the tabled values in Columns D-R as a whole ("Freezer" - "Aisle 14") and find the highest single value out of all of them and return that value in Cell V6, again if there are multiple values all the same, then it needs to be the most recent value according to the date in Column A "Date". Similar to the first example, I then need to find the corresponding Date and Day value from the row that the highest value is located in and return those into Cell V7 & V8 respectively. On top of this I need to also find the corresponding column name from which the highest value came from to give the following information:
Cell V6:145
Cell V7: 28-Apr-09
Cell V8: Tuesday
Cell V9: Aisle 10
The table will eventually contain many more rows of data, so I cant work with just the range of information already in the table.
The first set of values I need to find is the highest value in Column S "Store" of the table and return that value into Cell V2. If there are multiple entries of the same value, then it needs to be the most recent of those according to the date in Column A "Date" of the table. Cells V3 & V4 need to provide the corresponding Date and Day value from the row that the highest value is located in respectively. So for the example table below it needs to return the following:
Cell V2: 60
Cell V3: 12-Feb-59
Cell V4: Wednesday
The second set of values is a bit more complicated. I need to look at all of the tabled values in Columns D-R as a whole ("Freezer" - "Aisle 14") and find the highest single value out of all of them and return that value in Cell V6, again if there are multiple values all the same, then it needs to be the most recent value according to the date in Column A "Date". Similar to the first example, I then need to find the corresponding Date and Day value from the row that the highest value is located in and return those into Cell V7 & V8 respectively. On top of this I need to also find the corresponding column name from which the highest value came from to give the following information:
Cell V6:145
Cell V7: 28-Apr-09
Cell V8: Tuesday
Cell V9: Aisle 10
The table will eventually contain many more rows of data, so I cant work with just the range of information already in the table.
DUMMY SHEET 2.xlsm | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | |||
1 | Date | Day | Load Type | Freezer | Dairy | Aisle 2 | Aisle 3 | Aisle 4 | Aisle 5 | Aisle 6 | Aisle 7 | Aisle 8 | Aisle 9 | Aisle 10 | Aisle 11 | Aisle 12 | Aisle 13 | Aisle 14 | STORE | |||||
2 | 12-Feb-59 | Wednesday | Double Sydney | 33 | 48 | 62 | 77 | 43 | 67 | 61 | 82 | 64 | 70 | 97 | 58 | 50 | 76 | 72 | 60 | MAXIMUM STORE VALUE (S) | ||||
3 | 26-Dec-62 | Friday | Triple | 29 | 46 | 75 | 81 | 47 | 68 | 71 | 83 | 39 | 65 | 82 | 51 | 44 | 63 | 68 | 58 | Corresponding Date | ||||
4 | 21-May-83 | Tuesday | No NDC | 36 | 68 | 36 | 70 | 63 | 56 | 86 | 40 | 33 | 39 | 37 | 20 | 49 | 32 | 111 | 51 | Corresponding Day | ||||
5 | 15-Oct-83 | Friday | No NDC | 35 | 63 | 44 | 110 | 44 | 66 | 85 | 44 | 48 | 43 | 45 | 33 | 65 | 66 | 131 | 58 | |||||
6 | 09-Jan-86 | Saturday | Double Sydney/Melbourne | 28 | 42 | 76 | 76 | 48 | 51 | 53 | 66 | 64 | 56 | 101 | 47 | 54 | 62 | 87 | 57 | MAXIMUM VALUE (D-R) | ||||
7 | 18-Jul-91 | Thursday | Single Sydney | 33 | 49 | 62 | 69 | 43 | 60 | 68 | 91 | 51 | 77 | 78 | 52 | 46 | 61 | 77 | 58 | Correspoding Date | ||||
8 | 28-Nov-96 | Thursday | Double Melbourne | 35 | 58 | 44 | 67 | 41 | 57 | 75 | 80 | 57 | 67 | 87 | 53 | 44 | 51 | 59 | 58 | Corresponding Day | ||||
9 | 28-Apr-09 | Tuesday | Single Melbourne | 23 | 34 | 66 | 84 | 41 | 48 | 94 | 67 | 57 | 56 | 145 | 53 | 45 | 64 | 60 | 52 | Corresponding Column Name | ||||
10 | 11-Jun-13 | Saturday | No NDC | 35 | 63 | 44 | 73 | 44 | 66 | 85 | 44 | 48 | 43 | 30 | 33 | 65 | 66 | 131 | 56 | |||||
11 | 10-Jan-19 | Wednesday | Single Sydney | 34 | 55 | 62 | 73 | 25 | 54 | 67 | 61 | 67 | 57 | 66 | 74 | 51 | 87 | 89 | 58 | |||||
12 | ||||||||||||||||||||||||
13 | ||||||||||||||||||||||||
TEST CARTON RATE |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D2:D11 | D2 | =CartonCount4[@Freezer]/AisleHours8[@Freezer] |
E2:E11 | E2 | =CartonCount4[@Dairy]/AisleHours8[@Dairy] |
F2:F11 | F2 | =CartonCount4[@[Aisle 2]]/AisleHours8[@[Aisle 2]] |
G2:G11 | G2 | =CartonCount4[@[Aisle 3]]/AisleHours8[@[Aisle 3]] |
H2:H11 | H2 | =CartonCount4[@[Aisle 4]]/AisleHours8[@[Aisle 4]] |
I2:I11 | I2 | =CartonCount4[@[Aisle 5]]/AisleHours8[@[Aisle 5]] |
J2:J11 | J2 | =CartonCount4[@[Aisle 6]]/AisleHours8[@[Aisle 6]] |
K2:K11 | K2 | =CartonCount4[@[Aisle 7]]/AisleHours8[@[Aisle 7]] |
L2:L11 | L2 | =CartonCount4[@[Aisle 8]]/AisleHours8[@[Aisle 8]] |
M2:M11 | M2 | =CartonCount4[@[Aisle 9]]/AisleHours8[@[Aisle 9]] |
N2:N11 | N2 | =CartonCount4[@[Aisle 10]]/AisleHours8[@[Aisle 10]] |
O2:O11 | O2 | =CartonCount4[@[Aisle 11]]/AisleHours8[@[Aisle 11]] |
P2:P11 | P2 | =CartonCount4[@[Aisle 12]]/AisleHours8[@[Aisle 12]] |
Q2:Q11 | Q2 | =CartonCount4[@[Aisle 13]]/AisleHours8[@[Aisle 13]] |
R2:R11 | R2 | =CartonCount4[@[Aisle 14]]/AisleHours8[@[Aisle 14]] |
S2:S11 | S2 | =CartonCount4[@TOTAL]/AisleHours8[@TOTAL] |