Hi! I am having difficulty figuring a way to manipulate my data to my satisfaction. The below table shows the data in question.
I will discuss rows 3-9 to give an example of what I'm trying to do. Rows 3-9 show information for the same store. However, that information has 4 different dates related to it (column F). Out of the 4 dates shown, I need to know which one has the highest total for column D for the products listed. In this case the data associated with 5/12/2006 (row 5) has the highest total (50) even though other dates have more products shown. However, if the data associated with 5/18/2006 (rows 6-8) had a higher total then *that* data would be the pivotal data. I need this done for every store listed in the file. I'd love to have a list extropolated from this data that shows only the date records with the highest column D totals for each store. For instance, if the first case stated above were true, I'd get an extrapolated list including the one record for store 12/397 from 5/12/2006. If the second case were true I'd get a list including the three records for store 12/397 from 5/18/2006.
This data can run upwards of 1000 records, so finding a way to automate this task will really save me a lot of time. As it stands right now I have to sift through them all myself row by row. Is there any hope of automating this? Any help will be greatly appreciated!
I will discuss rows 3-9 to give an example of what I'm trying to do. Rows 3-9 show information for the same store. However, that information has 4 different dates related to it (column F). Out of the 4 dates shown, I need to know which one has the highest total for column D for the products listed. In this case the data associated with 5/12/2006 (row 5) has the highest total (50) even though other dates have more products shown. However, if the data associated with 5/18/2006 (rows 6-8) had a higher total then *that* data would be the pivotal data. I need this done for every store listed in the file. I'd love to have a list extropolated from this data that shows only the date records with the highest column D totals for each store. For instance, if the first case stated above were true, I'd get an extrapolated list including the one record for store 12/397 from 5/12/2006. If the second case were true I'd get a list including the three records for store 12/397 from 5/18/2006.
This data can run upwards of 1000 records, so finding a way to automate this task will really save me a lot of time. As it stands right now I have to sift through them all myself row by row. Is there any hope of automating this? Any help will be greatly appreciated!
| Displays.xls | ||||||||
|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | |||
| 1 | Rt | Store | Product | Cases | Price | Date | ||
| 2 | 40 | #2159STOPANDGRAB | PRODUCTA4/6/12NR | 5 | 7.49 | 5/26/2006 | ||
| 3 | 40 | 12/397ABCFOODSTORES | 2NDPRODUCT4/6/12NR | 2 | 0 | 5/05/2006 | ||
| 4 | 40 | 12/397ABCFOODSTORES | PRODUCTA2/12/12NR | 15 | 9.99 | 5/05/2006 | ||
| 5 | 40 | 12/397ABCFOODSTORES | PRODUCTA2/12/12NR | 50 | 12.99 | 5/12/2006 | ||
| 6 | 40 | 12/397ABCFOODSTORES | 2NDPRODUCT4/6/12NR | 5 | 6.49 | 5/18/2006 | ||
| 7 | 40 | 12/397ABCFOODSTORES | PRODUCTA4/6/12NR | 5 | 6.49 | 5/18/2006 | ||
| 8 | 40 | 12/397ABCFOODSTORES | PRODUCTA2/12/12NR | 5 | 9.99 | 5/18/2006 | ||
| 9 | 40 | 12/397ABCFOODSTORES | PRODUCTA2/12/12NR | 10 | 12.99 | 5/26/2006 | ||
| 10 | 40 | 20/398ABCFOODSTORES | PRODUCTA2/12/12NR | 10 | 9.99 | 5/05/2006 | ||
| 11 | 40 | 35/164ABCFOODSTORES | PRODUCTA2/12/12NR | 20 | 9.99 | 5/05/2006 | ||
| 12 | 40 | 35/164ABCFOODSTORES | PRODUCTA2/12/12NR | 25 | 12.99 | 5/12/2006 | ||
| 13 | 40 | 38/195ABCFOODSTORES | PRODUCTA2/12/12NR | 12 | 9.99 | 5/05/2006 | ||
| 14 | 40 | 38/195ABCFOODSTORES | PRODUCTA2/12/12NR | 12 | 12.99 | 5/12/2006 | ||
| 15 | 40 | 38/195ABCFOODSTORES | PRODUCTA2/12/12NR | 6 | 12.99 | 5/19/2006 | ||
| 16 | 40 | 38/195ABCFOODSTORES | PRODUCTA2/12/12NR | 6 | 12.99 | 5/26/2006 | ||
May | ||||||||
