I have an array of data. I need a formula that will return the column number
of the highest column with content.


For example:
A B C D E F
1 3 2 3
2 3 4
3 2 4 5 1
4 2 3

Where the answers should be D, E, F, and B, respectively.

Any suggestions?
Thanks
Justin

=LOOKUP(10^10,1:1)


"virtualjustin" wrote:

I have an array of data. I need a formula that will return the column number
of the highest column with content.


For example:
A B C D E F
1 3 2 3
2 3 4
3 2 4 5 1
4 2 3

Where the answers should be D, E, F, and B, respectively.

Any suggestions?
Thanks
Justin

Where the answers should be D, E, F, and B, respectively.

Huh?

I think a better explanation is needed (at least, for me!).

--
Biff
Microsoft Excel MVP


"virtualjustin" wrote in message
...
I have an array of data. I need a formula that will return the column
number
of the highest column with content.


For example:
A B C D E F
1 3 2 3
2 3 4
3 2 4 5 1
4 2 3

Where the answers should be D, E, F, and B, respectively.

Any suggestions?
Thanks
Justin

Assume source data as posted is in Sheet1

In Sheet2,
Put this in any starting cell, say in B2, then array-enter the formula by
pressing CTRL+SHIFT+ENTER, instead of just pressing ENTER:
=IF(COUNT(Sheet1!1:1),MATCH(MAX(ISNUMBER(Sheet1!1: 1)*COLUMN(1:1)),(ISNUMBER(Sheet1!1:1))*(COLUMN(1:1 )),0),"")
B2 returns the required col number of row1 in Sheet1, ie: 4. Copy B2 down to
return correspondingly for the other data rows in Sheet1. You'd get the
stated results: 4,5,6,2 in B2:B5
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"virtualjustin" wrote:
I have an array of data. I need a formula that will return the column number
of the highest column with content.


For example:
A B C D E F
1 3 2 3
2 3 4
3 2 4 5 1
4 2 3

Where the answers should be D, E, F, and B, respectively.

Any suggestions?
Thanks
Justin

On Sun, 17 Feb 2008 12:33:02 -0800, virtualjustin
wrote:

I have an array of data. I need a formula that will return the column number
of the highest column with content.


For example:
A B C D E F
1 3 2 3
2 3 4
3 2 4 5 1
4 2 3

Where the answers should be D, E, F, and B, respectively.



Hmmm. The last I checked, "D" was not a number <g.

If you want the column NUMBER, then:


=LOOKUP(2,1/(LEN(1:1)0),ROW(INDIRECT("1:256")))
for Row 1

If you copy/fill down, the row parameter will adjust for subsequent rows.

and so forth.

If you want the column letter, then try:

=LEFT(ADDRESS(1,LOOKUP(2,1/(LEN(1:1)0),ROW(INDIRECT("1:256"))),4),
LEN(ADDRESS(1,LOOKUP(2,1/(LEN(1:1)0),ROW(INDIRECT("1:256"))),4))-1)

And if you want the contents of that cell, you can try:

=LOOKUP(2,1/(LEN(1:1)0),1:1)


--ron

Ron Rosenfeld wrote...
....
If you want the column NUMBER, then:

=LOOKUP(2,1/(LEN(1:1)0),ROW(INDIRECT("1:256")))
for Row 1
....

OP's subject line says Excel 2007, and Excel 2007 has more than 256
columns. Also an unnecessary use of the volatile INDIRECT. Better to
rewrite this as

=LOOKUP(2,1/(LEN(1:1)0),COLUMN(1:1))

or

=LOOKUP(2,1/(1:1<""),COLUMN(1:1))

If you want the column letter, then try:

=LEFT(ADDRESS(1,LOOKUP(2,1/(LEN(1:1)0),ROW(INDIRECT("1:256"))),4),
LEN(ADDRESS(1,LOOKUP(2,1/(LEN(1:1)0),ROW(INDIRECT("1:256"))),4))-1)
....

Or

=SUBSTITUTE(ADDRESS(1,LOOKUP(2,1/(LEN(1:1)0),COLUMN(1:1)),4),1,"")

On Sun, 17 Feb 2008 23:24:16 -0800 (PST), Harlan Grove
wrote:

Ron Rosenfeld wrote...
...
If you want the column NUMBER, then:

=LOOKUP(2,1/(LEN(1:1)0),ROW(INDIRECT("1:256")))
for Row 1
...

OP's subject line says Excel 2007, and Excel 2007 has more than 256
columns. Also an unnecessary use of the volatile INDIRECT. Better to
rewrite this as

=LOOKUP(2,1/(LEN(1:1)0),COLUMN(1:1))

or

=LOOKUP(2,1/(1:1<""),COLUMN(1:1))

If you want the column letter, then try:

=LEFT(ADDRESS(1,LOOKUP(2,1/(LEN(1:1)0),ROW(INDIRECT("1:256"))),4),
LEN(ADDRESS(1,LOOKUP(2,1/(LEN(1:1)0),ROW(INDIRECT("1:256"))),4))-1)
...

Or

=SUBSTITUTE(ADDRESS(1,LOOKUP(2,1/(LEN(1:1)0),COLUMN(1:1)),4),1,"")


Good suggestions.

I never realized, nor tested to see, that COLUMN (and ROW also, for that
matter) would return an array.

So far as 2007, I thought about that when writing my more complex formula to
parse out the column letter, but not when generating the column number itself.
One of these days I'll have to get 2007.
--ron