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

Hi Claus,

Here in this workbook I have a very small sample.

On sheet 1 the yellow is where two LARGE values are in the same consecutive column so will return duplicate headers as seen on sheet 2. The lite blue is the second LARGE to avoid the dupe header.

The results on sheet 2 column A & B are what my little formula code does, which produces the format correctly but you can see it duplicates Header1.

The correct result is shown in column E & F as an example..

If there are only five rows, then it should only show five rows, although there will probably be 300 rows all the time.

Hope this helps, because I know you can do the code if I can just get my act together and explain it properly. Sorry for the sloppy info stream.

Howard


https://www.dropbox.com/s/bcpva2gjju...lumn.xlsm?dl=0

Crap!! I linked the wrong workbook.

I may have deleted the one I wanted to send so will be a delay for the example.

Dang, Sorry!

Howard

Hi Howard,

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

The correct result is shown in column E & F as an example..

please put the correct result into the workbook and upload it again.


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

On Saturday, May 30, 2015 at 7:57:48 AM UTC-7, Claus Busch wrote:
Hi Howard,

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

The correct result is shown in column E & F as an example..

please put the correct result into the workbook and upload it again.


Regards
Claus B.

Here it is.

https://www.dropbox.com/s/bcpva2gjju...lumn.xlsm?dl=0

Hi Howard,

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

https://www.dropbox.com/s/bcpva2gjju...lumn.xlsm?dl=0

look he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "Large Rev2"


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

Hi Claus,

I hand entered the correct returns on sheet 1 columns F and G.

The only row needing to use LARGE,2 is row 6.

In the sample data the largest value in row 5 and row 6 are both under Header1, numbers 20 and 25. So row 6 must use the second largest number 24, which is under Header2.

The fact that there are two Header5 and two Header1 is okay as long as they are not in consecutive rows.

The values of the large function needs to be returned also. So the header name in column A and the large number in column B on sheet 2.

https://www.dropbox.com/s/59ov9ceqvs...0LHK.xlsm?dl=0

Howard

Hi Howard,

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

https://www.dropbox.com/s/59ov9ceqvs...0LHK.xlsm?dl=0

now I got it ;-)

Look he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "Large Rev3"


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

now I got it ;-)

Look he
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "Large Rev3"

Yes, indeed. That nails it.

Appreciate it.

Sort of hard to explain the concept. Thanks for your patience too.

Howard

Hi Howard,

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

Sort of hard to explain the concept. Thanks for your patience too.

it is also for me esier when I see the data.
Do you use the latest version with the array? It is a bit faster.


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

Hi again,

Am Sat, 30 May 2015 22:38:11 +0200 schrieb Claus Busch:

it is also for me esier when I see the data.

sorry typo. Should be easier


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

On Saturday, May 30, 2015 at 1:38:28 PM UTC-7, Claus Busch wrote:
Hi Howard,

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

Sort of hard to explain the concept. Thanks for your patience too.

it is also for me esier when I see the data.
Do you use the latest version with the array? It is a bit faster.


Regards
Claus B.

Hi Claus,

This code from "Large Rev3" is what I'm using.

Sure seems fine to me.

Howard


Sub Test()
Dim i As Long, j As Long, LCol As Long
Dim varOut() As Variant
Dim valL As Long, valH

LCol = Application.CountA(Sheets("Sheet1").Range("K2:KN2" ))

With Sheets("Sheet2")
ReDim Preserve varOut(LCol - 1, 1)
varOut(0, 1) = Evaluate("=Max(Sheet1!K2:KN2)")
varOut(0, 0) = Evaluate("=INDEX(Sheet1!$K$1:$KN$1,MATCH(Max(Sheet 1!K2:KN2),Sheet1!K2:KN2,0))")

For i = 1 To LCol - 1
For j = 1 To LCol + 1
valL = Evaluate("=LARGE(Sheet1!K" & i + 2 & ":KN" & i + 2 & "," & j & ")")
valH = Evaluate("=INDEX(Sheet1!$K$1:$KN$1,Match(" & valL & ",Sheet1!K" & i + 2 & ":KN" & i + 2 & ",0))")
If valH < varOut(i - 1, 0) Then
varOut(i, 0) = valH
varOut(i, 1) = valL
Exit For
End If
Next j
Next i
.Range("A2").Resize(LCol, 2) = varOut
End With
End Sub