VBA to extract top 10 values based on criteria

[gwatch]

I am looking at a response from VBA Geek here
https://www.mrexcel.com/forum/excel...0-highest-values-1-column-onto-new-sheet.html



I am looking for something identical - BUT adding in a criteria lookup on a column


i.e. Top10 values IF column C = "X"


Original code

Sub getHigh10()
Dim lRow As Long
lRow = Sheets("Medium Rollup").Range("F" & Rows.Count).End(xlUp).Row
Sheets("Top 10").Range("A2:A11").Value = Evaluate("transpose(LARGE('Medium Rollup'!F4:F" & lRow & ",{1,2,3,4,5,6,7,8,9,10}))")
End Sub

I have been trying the following - but with no success

Sub getHigh10X()
Dim lRow As Long
lRow = Sheets("Medium Rollup").Range("F" & Rows.Count).End(xlUp).Row
Sheets("Top 10").Range("C2:C11").Value = Evaluate("(LARGE(IF('Medium Rollup'!A4:A" & lRow & "=""X"",'Medium Rollup'!F4:F" & lRow & ",{1,2,3,4,5,6,7,8,9,10})))")
End Sub

If i use an array formula I can get the 2nd largest value like this


{=LARGE(IF($A$2:$A$100="X",$F$2:$F$100),2)}


Or also like this


=AGGREGATE(14,6,$F$2:$F$100/($A$2:$A$100="X"),2)




Any suggestions to how to filter on column A in the original response




Ideally I would like to copy paste the entire row as in VBA Geek original response




Code:

Sub hi()
Dim lRow As Long
lRow = Sheets("Medium Rollup").Range("F" & Rows.Count).End(xlUp).Row
Range(Join(Filter(Evaluate(Replace("=IF(TRANSPOSE($F$4:$F$#)>=LARGE($F$4:$F$2#,10),TRANSPOSE(ADDRESS(ROW($F$4:$F$#),6,,,""Medium Rollup"")),""@@"")", "#", lRow)), "@@", 0), ",")).EntireRow.Copy _
Sheets("Top 10").Range("A2")




End Sub

Thanks

 

[ravishankar]

try {=LARGE(IF($A$2:$A$100="X",$F$2:$F$100),ROW())} IN Hi to H10
ravi