1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel formula needed to return column letter

Discussion in 'Business Applications' started by jha900, Nov 29, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. jha900

    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
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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!
     

    Attached Files:

  3. WendyM

    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)))))
     
  4. fletch_1960

    fletch_1960

    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)
     
  5. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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)
     
  6. WendyM

    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.
     
  7. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    LMBO!! I knew there was a reason I liked you Wendy, your ubiquitous use of parenthesis. Touche. :)
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    ... and then there's vba functions with two parenthesis () ??? :)
     
  9. fletch_1960

    fletch_1960

    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
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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.

    :)
     
  11. Zack Barresse

    Zack Barresse

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

    @Hans: And some of us need that reserve chute! :D
     
  12. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1028970

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice