+ Reply to Thread
Results 1 to 5 of 5

Write name of column when value in cells are bigger than 0

  1. #1
    Registered User
    Join Date
    05-29-2017
    Location
    Prague, Czech Republic
    MS-Off Ver
    10
    Posts
    13

    Write name of column when value in cells are bigger than 0

    Hello,

    Example:
    A B C D E
    0 0 4 0 C

    In other words I need formula which will display in E column name of the column in case that cell of that column was bigger than 0. In above example Column E displays C because cell had number 4 .

  2. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,350

    Re: Write name of column when value in cells are bigger than 0

    Either of these will return the column letter of the first greater-than-zero number in the range A1:A4:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The first will start to break down if your A-D range extends beyond Z, the second should be okay. Neither of them will return more than one column result, though - only the first.

    Edit: if your range starts in column B, the formulae would become:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and so on if you start in C, etc.
    Last edited by Aardigspook; 05-29-2017 at 03:34 PM. Reason: Add amendments needed if range starts in col B
    Regards,
    Aardigspook

    Recently moved house, internationally, during COVID (!) so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  3. #3
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,966

    Re: Write name of column when value in cells are bigger than 0

    Try

    =CHAR(LOG(SIGN(A1)+SIGN(B1)*2+SIGN(C1)*4+SIGN(D1)*8,2)+65)
    Martin

    If my solution has saved you time and/or money, please consider sponsoring my run in the 2020 London Marathon in aid of Cancer Research UK.

    https://uk.virginmoneygiving.com/MartinRice

  4. #4
    Registered User
    Join Date
    05-29-2017
    Location
    Prague, Czech Republic
    MS-Off Ver
    10
    Posts
    13

    Re: Write name of column when value in cells are bigger than 0

    Do you not what? I did it in two steps:
    STEP 1) I put filter on each column and selected non zero data
    STEP 2) I copied name of the column to the first empty field in E a dragged down to all empty fields in E

    Thank you for efforts

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,446

    Re: Write name of column when value in cells are bigger than 0

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. creating vba script to write semi complex formula across 1000+cells in 1 column
    By dvault101 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-18-2015, 04:27 PM
  2. [SOLVED] If column range is bigger than a Value, to unhide the same row on another sheet
    By dreddster in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-27-2015, 12:24 PM
  3. [SOLVED] Countif 1 column value is bigger than others
    By jdjp298 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-03-2014, 01:32 PM
  4. Highlight rows if value is bigger than 428 in Column (J)
    By Tmc2159 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-12-2013, 09:57 AM
  5. Replies: 0
    Last Post: 11-06-2012, 11:16 AM
  6. Replies: 2
    Last Post: 03-14-2006, 02:04 AM
  7. Replies: 0
    Last Post: 08-11-2005, 03:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1