Advanced Filtering: Find record with highest amount

How can I construct a criteria that finds the records containing the
highest amount for each month? Example dataset:

May, John, 100
May, Ted, 200
May, Beth, 210
Jun, John, 500
Jun, Ted, 100
Jun, Beth, 400

Desired filtered dataset:
May, Beth, 210
Jun, John, 500

I know about the MAX function, butI can't figure out how to find the
MAX at each change in the month value. Thanks for any suggestion!

0
5/16/2007 7:15:24 PM
excel 39879 articles. 2 followers. Follow

5 Replies
416 Views

Similar Articles

[PageSpeed] 55

I don't know if filtering can help, but here is a simple trick:

Sort the dataset by amount descending.  Then use VLOOKUP to find data 
related to May.  VLOOKUP will find the first MAY record, but because of the 
sort, the first record will also be the max for that month.
-- 
Gary''s Student - gsnu200722

0
GarysStudent (1572)
5/17/2007 2:16:01 AM
"John" askes...
> How can I construct a criteria that finds the records containing the
> highest amount for each month? <cut>

With Subtotals you can create a statistical value on a field for a group of 
records.
You want to find the max (statistical value or aggregate) amount (field) for 
each month (group of records).

First make sure that you have gouped the records. In your case, you need to 
sort ascending in the column with the month-names. All records of the same 
month are now grouped together. Now what you want is Excel to do is to find 
the hifghest amount when the first month changes int the second month and so 
on.
This is exactly what Subtotals does.

Choose menu Data -- Subtotals.

In the window choose: at each change in Month, uw function Max, add subtotal 
to Amount.

Good luck,

Sybolt 


0
5/17/2007 9:56:35 AM
Sybolt,

I've got the subtotals with Max working, but is there a way to show
the name with the max subtotal? For example, subtotal shows:
May, 210

I'd like to see:
May, Jane, 210

Is this possible using the subtotal function? Thank you...


> In the window choose: at each change in Month, uw function Max, add subtotal
> to Amount.
>

0
5/17/2007 4:56:00 PM
I finally got it.

In a helper column, say column D enter in D2:

=IF(C2=SUMPRODUCT(MAX(--(C$2:C$37)*(A$2:A$37=A2))),1,0)
and copy down.  Here is some sample data:

month	person	value	super
apr	larry	42	0
oct	moe	47	1
jul	curley	29	0
nov	manny	75	1
jan	moe	11	0
jul	jack	78	1
jun	rocky	44	0
jul	bullwinkle	56	0
jan	larry	20	0
mar	moe	67	1
jun	curley	90	1
oct	manny	14	0
set	moe	66	1
mar	jack	61	0
nov	rocky	71	0
nov	bullwinkle	43	0
may	larry	58	0
apr	moe	81	1
aug	curley	47	0
may	manny	73	1
feb	moe	86	1
oct	jack	11	0
apr	rocky	25	0
aug	bullwinkle	50	1
dec	larry	31	0
sep	moe	79	1
mar	curley	54	0
feb	manny	52	0
jun	moe	80	0
feb	jack	82	0
set	rocky	14	0
jan	bullwinkle	85	1
aug	larry	14	0
may	moe	22	0
dec	curley	40	0
dec	manny	63	1

As you see, the helper column has a 1 for the max for each month.  Just 
switch on AutoFilter and select 1 to see the complete rows with the max for 
each month.
-- 
Gary''s Student - gsnu200723
0
GarysStudent (1572)
5/19/2007 6:12:00 PM
Great work! Thanks, Gary's Student...


On May 19, 11:12 am, Gary''s Student
<GarysStud...@discussions.microsoft.com> wrote:
> I finally got it.
>
> In a helper column, say column D enter in D2:
>
> =IF(C2=SUMPRODUCT(MAX(--(C$2:C$37)*(A$2:A$37=A2))),1,0)
> and copy down.  Here is some sample data:
>
> month   person  value   super
> apr     larry   42      0
> oct     moe     47      1
> jul     curley  29      0
> nov     manny   75      1
> jan     moe     11      0
> jul     jack    78      1
> jun     rocky   44      0
> jul     bullwinkle      56      0
> jan     larry   20      0
> mar     moe     67      1
> jun     curley  90      1
> oct     manny   14      0
> set     moe     66      1
> mar     jack    61      0
> nov     rocky   71      0
> nov     bullwinkle      43      0
> may     larry   58      0
> apr     moe     81      1
> aug     curley  47      0
> may     manny   73      1
> feb     moe     86      1
> oct     jack    11      0
> apr     rocky   25      0
> aug     bullwinkle      50      1
> dec     larry   31      0
> sep     moe     79      1
> mar     curley  54      0
> feb     manny   52      0
> jun     moe     80      0
> feb     jack    82      0
> set     rocky   14      0
> jan     bullwinkle      85      1
> aug     larry   14      0
> may     moe     22      0
> dec     curley  40      0
> dec     manny   63      1
>
> As you see, the helper column has a 1 for the max for each month.  Just
> switch on AutoFilter and select 1 to see the complete rows with the max for
> each month.
> --
> Gary''s Student - gsnu200723


0
5/23/2007 4:55:11 PM
Reply:

Similar Artilces:

OL2000 filtering junk by domain
Since a lot of junk seems to come from the same domains I am trying to set up my filters to reject the domain only. I can't find KB references to it. Assuming junk@garbage.com do I list @garbage.com or garbage.com (neither of these seem to work actually) I'm doing this in the Junk Mail Edit, is this the right place?? TIA Steve L. Steve Lenaghan wrote: > Since a lot of junk seems to come from the same domains I am trying to > set up my filters to reject the domain only. I can't find KB references > to it. > > Assuming junk@garbage.com do I list > @g...

Unique records in Pivot Tables
Excel 2000 I have data that stores Client Invitation to Tender and Supplier Contract details. Each Client ITT is uniquely identified. However we send out the same ITT to many suppliers so each request for service is given the same identifier, eg Reference Client/Supplier Contract Name Value Status AC/001 Client blah 4,000 pend AC/002 Client blah2 5,000 pend AS/001 Supplier blah3 3,000 pend AS/001 Supplier blah3 3,000 pend When I create my table by Client to get the total value pending ...

Find and Replace within an XML file
I was wondering if there is a simple method of replacing characters within the xml document. I'm looking at writing a Biztalk Pipeline that escapes illegal characters before they are processed by a HL7 assembler. I.E. replace all instances of &amp; or & with \T\ . I thought about converting to a string and using the replace function, but that would mean knownig all the HTML codes for characters (as the escape characters are to be dynamically chosen by the user). Where as I could via xpath ??? replace all instances of the original character such as &. Hope it makes sen...

Records will not open
I am experiencing a problem with certain records within CRM that will not open. The system will try and try to open the record (pinging out two processors) until it finally times out. This is only with certain records but not others... Any suggestions? We experienced a similar problem. We created those records again (Leads) and deleted the old ones... all worked fine then "Phillip Sperry" <phillip.sperry@mxsecure.com> wrote in message news:9FeSb.10804$QJ3.6760@fed1read04... > I am experiencing a problem with certain records within CRM that will not > open. The syst...

Reconcile Payables Outstanding Amounts
Our customer would like to have the Reconcile functionality where it verifies the transaction balances with the amounts applied to them. If the calculated transaction balance does not equal the amount for the transaction, the system sets the balances as calculated. For example, - Payment amount is $100 - Applied to Invoice of $95 - The unapplied amount shown is $15 and not $5. Running the Reconcile will set the Unapplied amount to $5 and it will then move the transaction to History as this is now fully applied. ---------------- This post is a suggestion for Microsoft, and Microsoft re...

Consecutive Filtering
Appreciate it if anyone can help me to resolve this problem. I am working with Proj 2007. Example I want to do a filter for all the sites I have in Region 1. Project will complete the filtering with no problems. However, if I wanted to further filter to where I want to find out what sites I have in Phase 1 MS project does not show all choices in the filter dropdown box. What can I do to be able to filter consecutively? Appreciate any assistance provided. -- Django Hello Django, Are you using autofilters on the field headings or are you creating the filter through the Proj...

Filtering data by custom fields in pivot tables from Visual Report
I have recently upgraded to MS Project 2007 and like the visual reporting feature, except I really need to filter and group the resulting pivot table in Excel by custom fields. For example, for task usage, I cannot filter my pivot table to show only the tasks that have costs > $0, thus my table ends up showing too many lines and a lot of 0's. I would also like to group by department/ function which I put in a custom text field. How do I do this? I saw related posts about saving the file as an Access database and then creating the pivot table by connecting to the datab...

Print employee record summary Inquiry report
How do i print the employee record summary Inquiry report? There's not a report you can print from the window. You can either do a screen shot of it or create a custom report. -- Charles Allen, MVP "Hazvi" wrote: > How do i print the employee record summary Inquiry report? ...

Automatic Find and Populate
Ok, I have several spreadsheets that represent bills and have tabs that represent the months. So, when I recieve a new bill for the month, I copy last month's bill onto a new tab, and rename it using the billing cycle. Each year, for each account number is contained in its own workbook- ie: account 245-9999 has a workbook for 2002, 2003, 2004, and 2005; within that workbook are tabs for Jan, Feb, Mar, etc. Ok, on a single month's tab, there are anywhere from 10 to 900 individual line items representing circuits. What I want to do is create a separate spreadsheet that lists unique c...

HELP! Record Macro in TOOLS > MACRO is gone!
I was in Customize function trying to enable the Stop Recording toolbar and now in TOOLS > MACRO the Record Macro option is gone. I'm working on a project and I need this back. Can someone help? I'm using Excel 2003. Thanks, Joe M. Try this: In the customize window > click the Options tab > push the reset toolbars & menus button. Exit excel and re-load it Micky "Joe M." wrote: > I was in Customize function trying to enable the Stop Recording toolbar and > now in TOOLS > MACRO the Record Macro option is gone. I'm working on a ...

How to record sell of asset & see true cost?
I had bought a car 4 years ago on loan and some downpayment. And recently I sold it. I had the following accounts setup in Money 2004 Delux: 1. Car Asset with only Opening Balance of $30,000 2. Car Loan account which tracked Principal and Interest amounts (the loan is fully paid) 3. Auto category as Expense category with sub categories such as Purchase Cost (downpayment), Fuel costs, Insurance, etc. All car expenses were tracked under one of these sub categories. I would like to record the sell of the car. Where would I record it and how would I categorize so that I can get...

Auto Filter 03-10-10
When I use Auto Filter and select the criteria I want to filter by and then go back to select all criteria certain rows are hidden, it does show all rows. Anyone know why? Did you mean "it does NOT show all rows"? If yes, then I'm guessing that you have more than one field/column filtered. In xl2003 menus, I'd use: Data|Filter|show all On the xl2007 ribbon, I think it's: Data tab|Sort & Filter group|Clear GV wrote: > > When I use Auto Filter and select the criteria I want to filter by and then > go back to select all criteria cer...

Tax Amount Not Transferring
We currently have our salespeople working in orders, and once the order is complete, we transfer it to an invoice. When we perform these transfers, if there is any value entered in the Tax field, this value is not transferred onto the invoice. We always check the box labeled "Include Totals and Deposits," but that doesn't seem to do the trick. Also, we noticed that whenever the shipping method on an invoice is changed, the value in the tax field changes to 0. Is there any way to prevent either of these things from happening? ...

Continuous Form, Window Top Record Ordinal
On continuous view forms, there should be a form property that has the recordset ordinal of the top-most record currently displayed in the form’s window. For example, if a recordset contains 100 records and the CurrentRecord is 5 and the user scrolls down until the CurrentRecord disappears off the top of the screen, what is the recordset ordinal for the record that is now at the top of the window? Left clicking on the scrollbar slider will display “Record: x of y”, but there should be a property that contains the value of x for code purposes. Perhaps call it “WindowTopRecord...

Extracting records from a list
I would like to extract records from a long list of 500 rows. The list is already sorted by the first column. There is a maximum of four possible values for the first column with multiple values for the other ones. I would like to create four different lists, each with only one of the possible value in its first column by extracting them from the "master" list. Any help or pointer will be gratefully appreciated. Here is some very simple generic code Code: -------------------- Set SourceSht = Sheets("Sheet1") Set DestSht = Sheets("Sheet1") ...

No records!! No errors!! after Sync.
We installed the sales for outlook client on a laptop and then replicated with the central database without any issues. The new user is a part of the business unit that owns all the records in the central database. After the replication there are no records being transferred to this laptop. We tried replicating a few times with no errors provided but no records being transferred. Any ideas appreciated. Thanks, Nathan ------=_NextPart_0001_6228563F Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi Nathan~ Check and see if you received any errors within the subscriptions of...

Filter option not working
I have been passed a spreadsheet with multiple columns with the headings showing that filtering is available, but when I click ony of the column headings it only shows Column Name: (Showing All). with no option to change anything. The ribbon bar sort and filter option is showing the Filter button as highlighted orange, but looks to be greyed out with clear and reapply both greyed. Can anyone tell me what I need to do to get the filter working? Sorry if this is a simple one, I have just never seen this before. Cheers Leep If you remove the filter and then select the range (don&...

Displaying Large Amount of Data in a Cell
Exporting an Access table to excel - one of the fields is a memo filed that has a large amount of text in it. Two questions: 1. What is the limit on the amount of characters that a cell can hold? and 2. How can I view that text in the Excel field it was exported to? -- Paul Mazzola General Manager-High Speed Data Services Time Warner Cable Eastern Carolina Division Wilmington NC Ph: 910-772-5793 Fax: 910-772-5796 Hi, 1. A cell can only display 1024 characters in a cell. 2. I'm not sure there's a way to get to the rest... Any way you can export to a text file and break it up...

List records that do not include specified value
I have a very basic select query that list all the records from a table SELECT tbl_cngu.cngu_no, tbl_cngu_recalls.recall_no FROM tbl_cngu LEFT JOIN tbl_cngu_recalls ON tbl_cngu.cngu_no = tbl_cngu_recalls.cngu_no ORDER BY tbl_cngu.cngu_no, tbl_cngu_recalls.recall_no DESC; Now I need to apply a twist and do no know how. The [recall_no] field is a numeric field 1,2,3,4,5,... and what I would like to do is supply the query with a [recall_no] value and have the query return those records for which there is no such [recall_no] for a each given [cngu_no]. How would I do this? T...

Create or find "Favorite Folders" pane
I can't find the "Favorite Folders" pane above "Folders List", anymore. If you're using the Folder List then the Favorite Folders section won't appear. "ChristiA" <ChristiA@discussions.microsoft.com> wrote in message news:CEC82608-6F53-4269-9799-61D3DF7374E1@microsoft.com... >I can't find the "Favorite Folders" pane above "Folders List", anymore. ...

Variable Pass to new record
Can somebody help me to pass a variable to one record which is search and selected. For Example on one record I have startCoupon = 101 then add 5 on this StartCoupon then it will become startCoupon will be 106 Then we search another record and select the record I want the StartCoupon value automatically show in textbox as 106 Please help me to make it possible. On the next record, set the DefaultValue property to: = DMax("FieldName", "TableName") -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com &qu...

apostrophe in record
I inherited a database and figured out that the following code will not work if there is an apostrophe in the record. Any suggestions? Private Sub Combo55_AfterUpdate() On Error GoTo Err_Combo55_AfterUpdate Dim rs As Object If IsNull(Combo55) = False Then DoCmd.GoToRecord , , acFirst Do If Site = True Then Call AddContact DoCmd.GoToRecord , , acNext Loop End If Me.Combo55 = "" Exit_Combo55_AfterUpdate: Exit Sub Err_Combo55_AfterUpdate: Me.Combo55 = "" Resume Exit_Combo55_Aft...

Filters and CFileDialog
Hi, I've been trying a number of suggestions about filters in the MFC File Open dialog, but I just can't seem to get them to work. I can get the default "*.*" filter to work all right, but not my own file extension. What am I doing wrong: CString strFilter = _T("Fun Functions (*.fun)\x0*.fun\x0\x0"); CFileDialog dlgBrowse(TRUE, NULL, NULL, OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT | OFN_ENABLESIZING); dlgBrowse.m_ofn.lpstrTitle = "Open a Fun Function"; dlgBrowse.m_ofn.lpstrFilter = strFilter; if (dlgBrowse.DoModal() == IDOK) { AfxMessageBox(_T("F...

new rules filter
Can anyone please tell me if there is a way to create a Rule in Outlook 2000, that will send all email from people in the Address Book only to the Inbox, all others get sent to the Delete box? If there is a way, I could not find an easy solution. Thanks! -- marx404 No. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding no answer: marx404 <404@404.com> asked: | Can anyone please tell me if there is a way to create a Rule in | Outlook 2000, that will send all email from people in the Address ...

Advanced Footnote Formatting
In legal documents it is common to insert a slash before the footnote number. Is there any way to do that kind of formatting in Word? Well, you can do it manually. You cannot include text with the actual footnote reference number, though. -- Stefan Blom Microsoft Word MVP <faceman28208@yahoo.com> wrote in message news:c7a407f8-6501-4ce2-aa6e-35a2f4b39d94@r1g2000yqb.googlegroups.com... > In legal documents it is common to insert a slash before the footnote > number. > > Is there any way to do that kind of formatting in Word? To clarify: If you...