How can I pull the records with the highest totals?

[gli]

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
	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

 

[plettieri]

Hi:

Do you mean something like this in a pivot table

example-1.xls
	A	B	C	D
3	Sum of Cases
4	Date	Store	Total
5	5/05/2006	12/397 ABC FOOD STORES	17
6		20/398 ABC FOOD STORES	10
7		35/164 ABC FOOD STORES	20
8		38/195 ABC FOOD STORES	12
9	5/05/2006 Max		20
10	5/12/2006	12/397 ABC FOOD STORES	50
11		35/164 ABC FOOD STORES	25
12		38/195 ABC FOOD STORES	12
13	5/12/2006 Max		50
14	5/18/2006	12/397 ABC FOOD STORES	15
15	5/18/2006 Max		5
16	5/19/2006	38/195 ABC FOOD STORES	6
17	5/19/2006 Max		6
18	5/26/2006	#2159 STOP AND GRAB	5
19		12/397 ABC FOOD STORES	10
20		38/195 ABC FOOD STORES	6
21	5/26/2006 Max		10
22		#2159 STOP AND GRAB Sum	5
23		12/397 ABC FOOD STORES Sum	92
24		20/398 ABC FOOD STORES Sum	10
25		35/164 ABC FOOD STORES Sum	45
26		38/195 ABC FOOD STORES Sum	36
Sheet4

plettieri

 

[gli]

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
	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	PRODUCTA2/12/12NR	50	12.99	5/12/2006
4	40	20/398ABCFOODSTORES	PRODUCTA2/12/12NR	10	9.99	5/05/2006
5	40	35/164ABCFOODSTORES	PRODUCTA2/12/12NR	25	12.99	5/12/2006
6	40	38/195ABCFOODSTORES	PRODUCTA2/12/12NR	12	9.99	5/05/2006
7	40	39/463ABCFOODSTORES	2NDPRODUCT2/12/12NR	5	0	5/12/2006
8	40	39/463ABCFOODSTORES	2NDPRODUCT4/6/12NR	5	0	5/12/2006
9	40	39/463ABCFOODSTORES	PRODUCTA2/12/12NR	25	12.99	5/12/2006
10	40	39/463ABCFOODSTORES	PRODUCTA4/6/12NR	15	6.49	5/12/2006
11	40	GETGO#19293	PRODUCTA2/12/12NR	6	11.99	5/05/2006
12	40	GETGO#62005	PRODUCTA2/12/12NR	6	11.99	5/05/2006
13	40	GETGO#63351	PRODUCTA2/12/12NR	6	11.99	5/05/2006
14	40	GETGO#69407	PRODUCTA2/12/12NR	6	11.99	5/26/2006
15	40	BIGCONGLOMERATE#1198	2NDPRODUCT4/6/12NR	5	0	5/05/2006
16	40	BIGCONGLOMERATE#1198	PRODUCTA2/12/12NR	50	9.99	5/05/2006
17	40	BIGCONGLOMERATE#1198	PRODUCTA4/6/12NR	30	5.69	5/05/2006
18	41	15/555ABCFOODSTORES	PRODUCTA2/12/12NR	50	12.99	5/20/2006
19	41	19/294ABCFOODSTORES	PRODUCTA2/12/12NR	15	12.99	5/20/2006
May

 

[Fazza]

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

 

[Aladin Akyurek]

Care to post the expected results, based just on the first exhibit?

 

[Fazza]

I can't load the viewer, sorry. The results I obtained are,

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

 

[Yogi Anand]

Hi gli:

How about ...

Book1
	A	B	C	D	E	F	G	H	I	J
1	Rt	Store	P	Cases	Price	Date
2	40	#2159 STOP AND GRAB		5	7.49	05/26/2006		used AdvancedFilter to extract
3	40	12/397 ABC FOOD STORES		2	0	05/05/2006		unique records from column B
4	40	12/397 ABC FOOD STORES		15	9.99	05/05/2006
5	40	12/397 ABC FOOD STORES		50	12.99	05/12/2006		Store	Cases
6	40	12/397 ABC FOOD STORES		5	6.49	05/18/2006		#2159 STOP AND GRAB	5
7	40	12/397 ABC FOOD STORES		5	6.49	05/18/2006		12/397 ABC FOOD STORES	50
8	40	12/397 ABC FOOD STORES		5	9.99	05/18/2006		20/398 ABC FOOD STORES	10
9	40	12/397 ABC FOOD STORES		10	12.99	05/26/2006		35/164 ABC FOOD STORES	25
10	40	20/398 ABC FOOD STORES		10	9.99	05/05/2006		38/195 ABC FOOD STORES	12
11	40	35/164 ABC FOOD STORES		20	9.99	05/05/2006
12	40	35/164 ABC FOOD STORES		25	12.99	05/12/2006
13	40	38/195 ABC FOOD STORES		12	9.99	05/05/2006
14	40	38/195 ABC FOOD STORES		12	12.99	05/12/2006
15	40	38/195 ABC FOOD STORES		6	12.99	05/19/2006
16	40	38/195 ABC FOOD STORES		6	12.99	05/26/2006
17
Sheet1

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

this is then copied down.

 

[Fazza]

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

 

[gli]

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?

 

[Fazza]

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.