PDA

View Full Version : [SOLVED] Filtering data up to a predetermined cumulative total



K. Georgiadis
02-19-2010, 12:50 PM
Long time no see! This may or not be a VBA issue:

I have a 6 column data table as follows

Column A: Name of crop
Column B: Name of Product
Column C: Net $ Sales of Product in that crop
Column D: Net Margin $ of Product in that crop

1) At bottom of Column D I sum up the net margin and list as total US $
2) I sort Column D so that the products with the highest margins are on top and those with the lowest at the bottom of the list
3) I add column E showing the % of total margin represented by each row ($ value of row divided by the sum total of Column D).
4) I add Column F showing the cumulative % of margin contribution, adding up to 100% at the last data row

Now here is my challenge: I want to have a variable criterion cell in which I can add a percentage, say 80%, with the intent being that no more data will be displayed once the cumulative margin contribution in column F reaches 80%, the idea being to filter out products that only contribute 20% of the total margin. The user should be able to set the criterion cell to any other percentage to display only the data set that meets the criteria.

I did not find this variable filtering option in Excel 2007's standard functions. Do you have any idea how this might be accomplished?

Thanks!

mbarron
02-19-2010, 01:16 PM
Can you post a sample of your set up?

mbarron
02-19-2010, 02:00 PM
You maybe able to do this with a Worksheet_Change event.

Something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
Set myRange = Range("a1").CurrentRegion
If Not Intersect(Range("h1"), Target) Is Nothing Then
myRange.AutoFilter Field:=2, Criteria1:="<=" & Range("h1")
End If
End Sub
The AutoFiltered table would have have a cell in A1 and the filter Criteria is in H1.

K. Georgiadis
02-19-2010, 02:36 PM
I have added a proforma worksheet that illustrates the possible design. Note that the real sheet will have over 500 rows of data.
In this dummy model there is a variable input cell in I1 (which isn't doing anything at this point) setting the cut-off point at 85%. If my design worked, the gray-shaded rows would not be displayed at all because the cumulative 85% target will have been reached on row 5.

In a sheet with over 500 rows of data, the goal would be reached more gradually. One problem I foresee is that almost certainly the cut-off is not going to be clean right at the target number. For example, the cumulative percentage could jump from 79.9% in one row to 80.2% in the next and the formula must have a leeway (e.g. cut-off will be at 79.9% if the next row down exceeds the cut-off target by more than 0.5%)

I hope that I have explained this adequately.

Thank you for your help.

mikerickson
02-19-2010, 08:11 PM
Wouldn't custom auto filtering F (the cumulative %) to show those entries <80% work?

If you want the percentage controlable from the sheet, you could use Advanced Filter.

K. Georgiadis
02-19-2010, 08:28 PM
Wouldn't custom auto filtering F (the cumulative %) to show those entries <80% work?

If you want the percentage controlable from the sheet, you could use Advanced Filter.
I'll take a closer look but I haven't yet figured out if it is possible to display the entire data set making up the targeted cumulative total while hiding all data below it (using the example in the "dummy" worksheet that I provided, I would want to show all data adding up to 85% of all profit, while hiding the data set that accounts for the remaining 15% ). :confused4

mbarron
02-19-2010, 08:51 PM
Change the value in I1 to the percentage you want to show. The autofilter is set to update its criteria using this workbook Change event. It will display results that are I1's value plus 0.999. Entering 80 will display everything 80.999% or less. The criteria calculation can be made more precise or made to follow specific rules based on the values in the F column.



Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range
Set myRange = Range("a1").CurrentRegion
If Not Intersect(Range("i1"), Target) Is Nothing Then
myRange.AutoFilter Field:=6, Criteria1:="<=" & Range("i1") + 0.00999
End If
End Sub

K. Georgiadis
02-19-2010, 09:00 PM
Thank you very much mbarron! I'll give it a spin tomorrow morning and I will let you know.

PS: I tried it on the sample worksheet and it works fine. I'll adapt it ASAP for the "big" worksheet with the 500+ rows. Many thanks!

mikerickson
02-22-2010, 07:32 AM
In the attached, K1 is blank and K2 holds the formula

=(COUNTIF($F$2:F2, ">"&$I$2) < 2)

AdvancedFiltering the with K1:K2 as the criteria range will hide the grey rows and show the others.

K. Georgiadis
02-22-2010, 07:47 AM
Thank you! problem solved.