+ 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
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    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

    I recently started a new job 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,967

    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

  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, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    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.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

+ 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. [SOLVED] How I can print full text bigger than column, in repeat column
    By Prince in forum Excel General
    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