How get Excel 2007 to return highest column

  • Thread starter Thread starter virtualjustin
  • Start date Start date
V

virtualjustin

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!).
 
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
 
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.
Click to expand...


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 said:
If you want the column NUMBER, then:

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

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 said:
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)
Click to expand...
....

Or

=SUBSTITUTE(ADDRESS(1,LOOKUP(2,1/(LEN(1:1)>0),COLUMN(1:1)),4),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


...

Or

=SUBSTITUTE(ADDRESS(1,LOOKUP(2,1/(LEN(1:1)>0),COLUMN(1:1)),4),1,"")
Click to expand...


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
 
Back
Top