How can I pull the records with the highest totals?

gli

Board Regular
Joined
Jun 8, 2006
Messages
96
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!
Displays.xls
ABCDEF
1RtStoreProductCasesPriceDate
240#2159STOPANDGRABPRODUCTA4/6/12NR57.495/26/2006
34012/397ABCFOODSTORES2NDPRODUCT4/6/12NR205/05/2006
44012/397ABCFOODSTORESPRODUCTA2/12/12NR159.995/05/2006
54012/397ABCFOODSTORESPRODUCTA2/12/12NR5012.995/12/2006
64012/397ABCFOODSTORES2NDPRODUCT4/6/12NR56.495/18/2006
74012/397ABCFOODSTORESPRODUCTA4/6/12NR56.495/18/2006
84012/397ABCFOODSTORESPRODUCTA2/12/12NR59.995/18/2006
94012/397ABCFOODSTORESPRODUCTA2/12/12NR1012.995/26/2006
104020/398ABCFOODSTORESPRODUCTA2/12/12NR109.995/05/2006
114035/164ABCFOODSTORESPRODUCTA2/12/12NR209.995/05/2006
124035/164ABCFOODSTORESPRODUCTA2/12/12NR2512.995/12/2006
134038/195ABCFOODSTORESPRODUCTA2/12/12NR129.995/05/2006
144038/195ABCFOODSTORESPRODUCTA2/12/12NR1212.995/12/2006
154038/195ABCFOODSTORESPRODUCTA2/12/12NR612.995/19/2006
164038/195ABCFOODSTORESPRODUCTA2/12/12NR612.995/26/2006
May
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Hi:

Do you mean something like this in a pivot table
example-1.xls
ABCD
3Sum of Cases
4DateStoreTotal
55/05/200612/397 ABC FOOD STORES17
620/398 ABC FOOD STORES10
735/164 ABC FOOD STORES20
838/195 ABC FOOD STORES12
95/05/2006 Max20
105/12/200612/397 ABC FOOD STORES50
1135/164 ABC FOOD STORES25
1238/195 ABC FOOD STORES12
135/12/2006 Max50
145/18/200612/397 ABC FOOD STORES15
155/18/2006 Max5
165/19/200638/195 ABC FOOD STORES6
175/19/2006 Max6
185/26/2006#2159 STOP AND GRAB5
1912/397 ABC FOOD STORES10
2038/195 ABC FOOD STORES6
215/26/2006 Max10
22#2159 STOP AND GRAB Sum5
2312/397 ABC FOOD STORES Sum92
2420/398 ABC FOOD STORES Sum10
2535/164 ABC FOOD STORES Sum45
2638/195 ABC FOOD STORES Sum36
Sheet4


plettieri
 
Upvote 0
Hi, thanks for responding. Unfortunately, the way the data is arranged in the pivot table is not what I'm looking for. I need the highest total for each store, not each date. While the date field is not the primary field it is necessary for getting this information because the data is compiled from multiple dates and I'm looking for the one date, per each store, that has the highest total. I can't figure out how to do this myself because I don't know how to make it compare date fields to find the highest total when there will often be more than one product for each date. It will have to do some addition, then comparisons, then return the records with the highest value. I hope I'm making sense.

From my example I envisioned being able to get an extrapolated list that would look like what you see below. The date with the highest total for each store is the only data listed. The reason I'm thinking more of a list is because I still need to know the data in the other fields. All fields still need to be present.
Displays.xls
ABCDEF
1RtStoreProductCasesPriceDate
240#2159STOPANDGRABPRODUCTA4/6/12NR57.495/26/2006
34012/397ABCFOODSTORESPRODUCTA2/12/12NR5012.995/12/2006
44020/398ABCFOODSTORESPRODUCTA2/12/12NR109.995/05/2006
54035/164ABCFOODSTORESPRODUCTA2/12/12NR2512.995/12/2006
64038/195ABCFOODSTORESPRODUCTA2/12/12NR129.995/05/2006
74039/463ABCFOODSTORES2NDPRODUCT2/12/12NR505/12/2006
84039/463ABCFOODSTORES2NDPRODUCT4/6/12NR505/12/2006
94039/463ABCFOODSTORESPRODUCTA2/12/12NR2512.995/12/2006
104039/463ABCFOODSTORESPRODUCTA4/6/12NR156.495/12/2006
1140GETGO#19293PRODUCTA2/12/12NR611.995/05/2006
1240GETGO#62005PRODUCTA2/12/12NR611.995/05/2006
1340GETGO#63351PRODUCTA2/12/12NR611.995/05/2006
1440GETGO#69407PRODUCTA2/12/12NR611.995/26/2006
1540BIGCONGLOMERATE#11982NDPRODUCT4/6/12NR505/05/2006
1640BIGCONGLOMERATE#1198PRODUCTA2/12/12NR509.995/05/2006
1740BIGCONGLOMERATE#1198PRODUCTA4/6/12NR305.695/05/2006
184115/555ABCFOODSTORESPRODUCTA2/12/12NR5012.995/20/2006
194119/294ABCFOODSTORESPRODUCTA2/12/12NR1512.995/20/2006
May
 
Upvote 0
This is suitable for query tables: they are sophisticated and can be difficult when you start. Knowing a little SQL would make it easier, too. It can be done in Excel however I suspect it would be much easier in MS Access. I think a limitation in Excel is that MS Query can't do subqueries. I'm only just learning some SQL (my background is wholly within Excel) so my approach might be not very efficient.

Assume the file has been saved. Give the data a name - "Table1" via named ranges.

Then via menu Data, Import External Data, New Database Query, select the file and then (in the wizard) the table. Then fields Rt, Store, Cases & Date. Go to the end of the query wizard and then at the last step select MS Query, then finish. Select the cases field and then using the upper case sigma icon on the toolbar, make this field the Sum of Cases. Return the data to Excel - the "door" button on the toolbar or via the File menu. Give this result a name, say "Table2".

Similarly, create another query onto a worksheet based on this new table. Select fields Rt, Store & the sum of cases. Go into MS Query again and then for the cases column make it select the maximum value - repeatedly hit the sigma button until you have maximum. Back to Excel with this data set and call it Table3.

Finally, create another query, selecting this time all three tables. Skip over the warning message and within MS Query drag & join Table2 and Table3 on the field of cases (sum & max), join table1 to table2 on store and Rt, table1 to table3 on store and Rt, and table1 to table 2 on date. Return this to Excel to have the desired results.

I've not detailed the above to the nth degree. It worked OK for me.

Rows 13 & 14 per your original data are both returned. I didn't see a specification for what to do if there were multiple matches; if you wanted to distinguish between these, apply some similar methods.

Refresh query tables like pivot tables, BTW. When you're familiar with these, they are very handy.

HTH,
Fazza
 
Upvote 0
I can't load the viewer, sorry. The results I obtained are,
Code:
Rt  Store                       Product                      Cases   Price   Date
40  #2159 STOP AND GRAB         PRODUCT A 4/6/12NR              5   7.49     5/26/2006
40  12/397 ABC FOOD STORES      PRODUCT A 2/12/12NR             50  12.99    5/12/2006
40  20/398 ABC FOOD STORES      PRODUCT A 2/12/12NR             10  9.99     5/05/2006
40  35/164 ABC FOOD STORES      PRODUCT A 2/12/12NR             25  12.99    5/12/2006
40  38/195 ABC FOOD STORES      PRODUCT A 2/12/12NR             12  12.99    5/12/2006
40  38/195 ABC FOOD STORES      PRODUCT A 2/12/12NR             12  9.99     5/05/2006

Maybe these give the idea until the OP responds?

regards,
Fazza
 
Upvote 0
Hi gli:

How about ...
Book1
ABCDEFGHIJ
1RtStorePCasesPriceDate
240#2159 STOP AND GRAB57.4905/26/2006used AdvancedFilter to extract
34012/397 ABC FOOD STORES2005/05/2006unique records from column B
44012/397 ABC FOOD STORES159.9905/05/2006
54012/397 ABC FOOD STORES5012.9905/12/2006StoreCases
64012/397 ABC FOOD STORES56.4905/18/2006#2159 STOP AND GRAB5
74012/397 ABC FOOD STORES56.4905/18/200612/397 ABC FOOD STORES50
84012/397 ABC FOOD STORES59.9905/18/200620/398 ABC FOOD STORES10
94012/397 ABC FOOD STORES1012.9905/26/200635/164 ABC FOOD STORES25
104020/398 ABC FOOD STORES109.9905/05/200638/195 ABC FOOD STORES12
114035/164 ABC FOOD STORES209.9905/05/2006
124035/164 ABC FOOD STORES2512.9905/12/2006
134038/195 ABC FOOD STORES129.9905/05/2006
144038/195 ABC FOOD STORES1212.9905/12/2006
154038/195 ABC FOOD STORES612.9905/19/2006
164038/195 ABC FOOD STORES612.9905/26/2006
17
Sheet1


array formula in cell I6 is ... =MAX(IF(B2:B16=H6,D2:D16))

this is then copied down.
 
Upvote 0
Hi Yogi.

A clever use of the array formula. How do you complete the requirement of having the product, price & date data? There could be multiple lines with different products & prices...

There could also be more than one solution - for example original rows 13 & 14. By way of example, if rows 15 & 16 were the same date as each other, there would be three solutions for that store - row 13; row 14; rows 15 & 16.

The query table automatically gives the full solution, but I can't see how to get there via formulae.

regards,
Fazza
 
Upvote 0
Hello! Thank you for your efforts. I apologize that I didn't get to this sooner. Fazza, I have to confess that I got completely lost while trying to perform your solution. Your results look like what I want to do, but I'm not getting very far. I named the entire data range that I wanted to pull from "Table1". When I select the file I want to pull from the wizard tells me "This data source contains no visible tables." I'm sure I'm doing something wrong here. Am I missing a vital step in there somewhere?
 
Upvote 0
Every single field of "Table1" must have a field name in the header row. No blanks.

The table name covers the header row and the data.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top