The Column_Large()code works, and is run on 300 rows.

Want to increment k value of the LARGE function in the formulas up to 20.

The Column_Large_20() code errors on the .Value in the first With statement.

Also, what can I expect the k value to do when it is run on 300 rows? Is that even feasible? Can it start over at MyArr(i) (i = 0)?

The general idea of the code is to find the LARGE value in the row range and return the Column Header to column A and the found LARGE value to column B.

The search data is on sheet1 the returns go to sheet2.

Thanks,
Howard


Sub Column_Large()

Dim lRowCount&
lRowCount = Sheets("Sheet1").Cells(Rows.Count, "K").End(xlUp).Row

With Sheets("Sheet2").Range("A2").Resize(lRowCount - 1)
.Formula = "=INDEX(Sheet1!$K$1:$KN$1,MATCH(LARGE(Sheet1!K2:KN 2,1),Sheet1!K2:KN2,0))": .Value = .Value
End With

With Sheets("Sheet2").Range("B2").Resize(lRowCount - 1)
.Formula = "=LARGE(Sheet1!K2:KN2,1)": .Value = .Value
End With

End Sub


Sub Column_Large_20()

Dim MyArr As Variant
Dim i As Long

MyArr = Array("1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,1 8,19,20")

Dim lRowCount&
lRowCount = Sheets("Sheet1").Cells(Rows.Count, "K").End(xlUp).Row


For i = LBound(MyArr) To UBound(MyArr)

Sheets("Sheet2").Range("A2").Resize (lRowCount - 1)
Sheets("Sheet2").Formula = "=INDEX(Sheet1!$K$1:$KN$1,MATCH(LARGE(Sheet1!K2:KN 2," & MyArr(i) & "),Sheet1!K2:KN2,0))": .Value = .Value

Sheets("Sheet2").Range("B2").Resize (lRowCount - 1)
Sheets("Sheet2").Formula = "=LARGE(Sheet1!K2:KN2" & MyArr(i) & ")": .Value = .Value

Next

End Sub

Hmm, a bit of a moving target here.

Clarification:

The goal is to NOT return the same column header name consecutively.

If the LARGE happens to be column "Header10" for the value .234 and the next row LARGE is also in "Header10" (but the value may be 1.33) then increment the k value to Large(Array,2) which will make it return a different header name.

Howard

Hi Howard,

Am Sat, 30 May 2015 03:34:53 -0700 (PDT) schrieb L. Howard:

The goal is to NOT return the same column header name consecutively.

If the LARGE happens to be column "Header10" for the value .234 and the next row LARGE is also in "Header10" (but the value may be 1.33) then increment the k value to Large(Array,2) which will make it return a different header name.

that is not so simple but you can handle it with another column:

Sub Test()
With Sheets("Sheet2")
.Range("A2").Formula = "=MAX(Sheet1!$K$2:$KN$2)"
.Range("A3:A21").Formula = _

"=IF(COUNTA($A$2:A2)<20,LARGE(Sheet1!$K$2:$KN$2,CO UNTIF(Sheet1!$K$2:$KN$2,""=""&A2)+1),"""")"
.Range("B2:B21").Formula = _
"=INDEX(Sheet1!$k$1:$KN$1,,MATCH(A2,Sheet1!$K$2:$K N$2,0))"
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Howard,

Am Sat, 30 May 2015 13:57:32 +0200 schrieb Claus Busch:

Sub Test()
With Sheets("Sheet2")

or try it this way (without helper column):

Sub Test2()
Dim i As Long
Dim varLarge(19) As Variant

With Application
varLarge(0) = .Max(Sheets("Sheet1").Range("K2:KN2"))
For i = 1 To 19
varLarge(i) = .Large(Sheets("Sheet1").Range("K2:KN2"), _
.CountIf(Sheets("Sheet1").Range("K2:KN2"), "=" & varLarge(i - 1)) + 1)
Next

For i = LBound(varLarge) To UBound(varLarge)
Sheets("Sheet2").Cells(i + 2, 1) =
..Index(Sheets("Sheet1").Range("K1:KN1"), _
.Match(varLarge(i), Sheets("Sheet1").Range("K2:KN2"), 0))
Next
End With
End Sub



Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Claus,

I had hoped the clarification would pretty much steer away from the 1 to 20 k value increment.


Say:

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.

So, the new k value for row 26 cannot be the Large for row 26, it has to be the second Large to avoid returning Header10 again.

Does that make sense?

So we only need a Large(array,1) and a Large(array,2) if my thinking is correct.

So if the a Header repeats the one above it, then look for the Large(array,2) header and return it.

Howard

Hi Howard,

Am Sat, 30 May 2015 05:30:05 -0700 (PDT) schrieb L. Howard:

I had hoped the clarification would pretty much steer away from the 1 to 20 k value increment.

Say:

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.

that is exactly what the code is doing. The inkrement is not 1 but
COUNTIF the previous value + 1.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Howard,

Am Sat, 30 May 2015 05:30:05 -0700 (PDT) schrieb L. Howard:

I had hoped the clarification would pretty much steer away from the 1 to 20 k value increment.

Say:

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.

please have a look:
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "Large"
If that is not the expected solution please post again.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

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