How to increment the k in LARGE(array,k)
Hi Howard,
Am Sat, 30 May 2015 05:30:05 -0700 (PDT) schrieb L. Howard:
Large value in row 25 is in column Header10
Large value in row 26 is in column Header10
We now need to find a different value for row 26 to prevent Header10 from repeating.
I think I got it.
Try it again with a helper column in Sheet2:
Sub Test2()
Dim i As Long
With Application
For i = 1 To 20
.Cells(i + 1, 1) = .Large(Sheets("Sheet1").Range("K2:KN2"), i)
Next
End With
Range("B2").FormulaArray = "=INDEX(Sheet1!$K$1:$KN$1," _
& "SMALL(IF(Sheet1!$K$2:$KN$2=A2,ROW(A1:A20)),COUNTI F($A$2:A2,A2)))"
Range("B2").AutoFill Range("B2:B21")
End Sub
Or look again in OneDrive and find out if Test or Test2 is the code you
need.
Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
|