Excel formula needed to return column letter

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

jha900

Thread Starter
Joined
Nov 29, 2011
Messages
1
Return column containing number 1
I have an excel file with 5 columns A1:E1 and 100 rows. Each row has numbers 1 to 5.
I need a formula in F1 that will return the column that has the number 1 in it. So in the example below, I need it to return A for row 1, E for row 2 and D for row 3.

A B C D E
1 5 4 3 2
5 4 3 2 1
2 5 4 1 3
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
Hi, welcome to the forum,
I've got just the vba code for you.
I use it all the time.
Unpack the zip file and insert the vba module in you VBA project,

The syntax is Ch2Rng(any number) and the result is the corresponding column letter

ie: Ch2Rng(65) = "A"

If the range is outside the Excel range it will return "---"

Have fun with it :) use it at will and if you have any questions ... just holler!
 

Attachments

WendyM

Retired Trusted Advisor
Joined
Jun 27, 2003
Messages
4,042
Or if you'd rather use a formula, you can try this in the first row and then drag it down all 100 rows. Just as an FYI, it doesn't work if you have more than 26 columns, but should be fine for A:E.

=CHAR(64+(SUMPRODUCT((A1:E1=1)*(COLUMN(A1:E1)))))
 
Joined
Nov 30, 2011
Messages
2
Hi jha900, its my first time on the forum.
If you type the following into cell F2 and copy it down, this should give you what your lookin for.
Hope this helps,

=MID("ABCDE",MATCH(1,C3:G3,0),1)
 
Joined
Jul 25, 2004
Messages
5,458
Hi, welcome to the board!

I went to post my formula, but it pales in comparison to Fletch's! That, is a thing of beauty.

Here is what I had, and I would still use Fletch's formula, even if only in lieu of eloquence...
Code:
=LEFT(ADDRESS(ROW(),MATCH(1,A1:E1,0),4),1)
 

WendyM

Retired Trusted Advisor
Joined
Jun 27, 2003
Messages
4,042
Awww, but what about me, Zack? I have SO many more parentheses. That IS the criteria for a good formula, right? Lots of parentheses? :p

jha900, I think the lesson here is that there are lots of ways to do it, but you should always use the least complex method you can find. Which may or may not be (ok, fine, it's not) the one with the most parentheses.
 
Joined
Jul 25, 2004
Messages
5,458
LMBO!! I knew there was a reason I liked you Wendy, your ubiquitous use of parenthesis. Touche. :)
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
... and then there's vba functions with two parenthesis () ??? :)
 
Joined
Nov 30, 2011
Messages
2
Thanks all. I was so worried about posting it, i didn't want to step on anyones toes. Anyway, thanks for the vote of confidence and your kind words. I dare say, we will cross paths again soon! Fletch
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,641
Those that have long toes should wear larger shoes.
We're all learning and new / different ideas are welcome, like the saying:

Minds are like parachutes, they only work when they're open.

:)
 
Joined
Jul 25, 2004
Messages
5,458
That's why I love this board. I learn something new every day!

@Hans: And some of us need that reserve chute! :D
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top