Filter based on Sub Criteria?

[mrmmickle1]

I would like to filter based on sub criteria.

I have 4 different possible field values in column A. I would like the filter to show only the highest (MAX) % for each of the four possibilities. Can someone please assist me in accomplishing this if it is possible?

The problem I can foresee is what if by getting rid of a low percent for one field value it gets rid of the highest % for another value.

For Example. If the CCC high was 95% but there was also a value for CCC that had 60% and AAA high is 60%..... then by eliminating the 60% for CCC it is no longer possible to display it for AAA.....


Start Data:

Excel 2012
	A	B
1	Filter Field	% Field
2	AAA	5%
3	AAA	55%
4	BBB	90%
5	BBB	95%
6	CCC	20%
7	CCC	25%
8	CCC	60%
9	DDD	50%
10	DDD	60%
11	DDD	23%
Sheet1

After Filter:

Excel 2012
	A	B
1	Filter Field	% Field
3	AAA	55%
5	BBB	95%
8	CCC	60%
10	DDD	60%
Sheet1

If there is a solution other then by filtering it would be much appreciated.

 

[RatExcel]

The only thing that comes to my mind is to add a helper column in column C with below formula (must be entered with CTRL + SHIFT + ENTER, not only ENTER):

=IF(B2=MAX(IF($A$2:$A$11=A2,$B$2:$B$11,0)),TRUE,FALSE)

And then just filter TRUEs in column C and you will get MAX values for each unique value in column A.

See sample workbook for reference:

https://app.box.com/s/1cix51c1h2nhz7yk0p6y0x02js3x7vms

 

[mrmmickle1]

This appears like it will work. For my purposes I am trying to enter the formula via VBA. When I run this line of code:

  Sheets(2).Range("J2:J" & ReportLRow).FormulaArray = "=IF(H2=MAX(IF($A$2:$A$" & ReportLRow & "=A2,$H$2:$H$" & ReportLRow & ",0)),TRUE,FALSE)"

It enters the same formula over and over again like this:

Excel 2012

	J

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #A9D08E"]MAX Value?[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]FALSE[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]FALSE[/TD]

</tbody>

Report​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]J2:J1081[/TH]
[TD="align: left"]{=IF(H2=MAX(IF($A$2:$A$1081=A2,$H$2:$H$1081,0)),TRUE,FALSE)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]




How can I fix this issue??? I need it to go H2, H3, H4 etc.... A2, A3, A4

Thank you so much for the assistance.

Besides putting it in a loop like this:

For x = 2 To ReportLrow

Sheets(2).Range("J"& x).FormulaArray = "=IF(H" & x & "=MAX(IF($A$2:$A$" & ReportLRow & "=A" & x & ",$H$2:$H$" & ReportLRow & ",0)),TRUE,FALSE)"

Next x

 

[RatExcel]

To be honest for me there are two solutions - either you go with formulas and you just apply it for your range manually or you go with VBA and the calculations are done within the code and only values not formulas are returned to the sheet. I not a fan of applying formulas using VBA and I don't know how to help you with that. If I would have to apply formulas via VBA I would go with the loop.

 

[mrmmickle1]

RatExcel,

Thanks so much for the help. The loop works. I will use it for the time being! I appreciate you helping me solve my issue.