+ Reply to Thread
Results 1 to 9 of 9

Summary column, highest value of text in row

  1. 10-23-2019, 11:20 AM #1
    adamkimber
    adamkimber is offline
    Registered User
    Join Date
    10-23-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    4

    Question Summary column, highest value of text in row

    Hi, I have cobbled together a formula (shown below). I am not completely understanding how excel is getting the result but it currently works, so if you could explain your changes to the formula to me that would be great! (Thank you in advance!)

    I am creating a drawing issue sheet with a summary column and I would like for the following to display in the summary column,

    1. If the row is blank, to display nothing
    2. If the row has a “/” in it then to display “/”
    3. If the row has “Deleted” in it then to display “Deleted” (I cannot get this bit to work at the moment)
    4. For a data range of alphabet characters ranging from A-Z, then AA, AB etc. show the highest value always

    So the first question is with the following rather lengthy formula whilst it does the trick for above requirements (apart from 3) it is rather long , is there a way of compacting or simplifying it?

    e.g. for summary column F45 and row data range G45:HH45.

    =IF((INDEX(G45:HH45,MATCH(MAX(INDEX(COUNTIF(G45:HH45,"<"&G45:HH45),0)),
    INDEX(COUNTIF(G45:HH45,"<"&G45:HH45),0),0))=0),IFERROR(LOOKUP(2,1/(NOT(ISBLANK(G45:HH45))),G45:HH45),""),INDEX(G45:HH45,MATCH(MAX(INDEX(COUNTIF(G45:HH45,"<"&G45:HH45),0)),
    INDEX(COUNTIF(G45:HH45,"<"&G45:HH45),0),0)))


    Second question this formula does not work for the following system of drawing numbers under requirement 4, drawing numbers P1-100, T1-100, C1-100 where P1, P2, P3 etc. is comes first, then T1, T2, T3, then C1, C2, C3 etc.. Currently the formula selects T1 etc. as the highest (which makes sense). I expect a different formula is required?

    Many thanks in advance

    Adam
    Reply With QuoteRegister To Reply
  2. 10-23-2019, 11:22 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,023

    Re: Summary column, highest value of text in row

    There's no way that i could retain that in my head, while working out what it does... Think "IQ of a demented goldfish...." and that's me.

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please DO NOT attach a picture of an Excel sheet (I do not have the patience to re-type any/all your stuff before starting).

    1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

    2. Make sure that your sample data are truly REPRESENTATIVE of your real data. For example, don't show text in a column if it's really a number. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    3. Make sure that your desired solution is also shown (mock up the results manually). To be honest, I am not interested in seeing a non-working formula... or a pile of blank cells. However, I am very interested in seeing your EXPECTED results in their EXPECTED location.

    4. Try not to use merged cells. They cause lots of problems and are DEFINITELY best avoided!

    Unfortunately the attachment icon doesn't work at the moment. So,... instead click on Go Advanced (below the Edit Window) while composing your reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 10-24-2019, 04:19 AM #3
    adamkimber
    adamkimber is offline
    Registered User
    Join Date
    10-23-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    4

    Re: Summary column, highest value of text in row

    Hi Glenn,

    Many thanks for your message. Please find attached (without formulas) the two types of results I am looking to achieve.

    I hope this makes sense?

    Adam
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 10-24-2019, 05:05 AM #4
    XLent
    XLent is offline
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Summary column, highest value of text in row

    some options

    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    I suspect with more time / thought this could be greatly simplified...

    edit: simplified the above from first post to keep a little more consistent.
    Last edited by XLent; 10-24-2019 at 05:35 AM.
    Reply With QuoteRegister To Reply
  5. 10-24-2019, 05:40 AM #5
    menem
    menem is offline
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Summary column, highest value of text in row

    Apologize in advance.
    I try to do my best for shortest formula but....T_T

    In this file.
    0000-Issue Sheet-P1-T1-C1.xlsx‎

    AN18
    Please Login or Register  to view this content.
    E18
    Please Login or Register  to view this content.

    Regards.


    Finally I've got a shorter formula. ^_^

    E18 (Remove formula in AN18 away)

    Please Login or Register  to view this content.
    Press Ctrl-Shift-Enter for Array formula
    Last edited by menem; 10-24-2019 at 06:20 AM. Reason: Add new formula
    My English is very poor, so please be patient >_<"

    My Dynamic Related Dropdown post:
    https://www.excelforum.com/excel-for...ml#post5657376
    https://www.excelforum.com/excel-for...ml#post5655711
    https://www.excelforum.com/excel-for...ml#post5655762
    Reply With QuoteRegister To Reply
  6. 10-24-2019, 07:25 AM #6
    adamkimber
    adamkimber is offline
    Registered User
    Join Date
    10-23-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    4

    Re: Summary column, highest value of text in row

    Oh wow, oh wow. You guys are amazing! Thank you for getting back so fast with such awesome formulas!

    Adam
    Reply With QuoteRegister To Reply
  7. 10-24-2019, 07:39 AM #7
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,719

    Re: Summary column, highest value of text in row

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

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  8. 10-25-2019, 01:28 AM #8
    menem
    menem is offline
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Summary column, highest value of text in row

    Just find a formula that fits both file after SOLVED. T_T

    E18
    Please Login or Register  to view this content.
    Press Ctrl-Shift-Enter for ARRAY formula


    Regards.
    Reply With QuoteRegister To Reply
  9. 10-25-2019, 03:16 AM #9
    adamkimber
    adamkimber is offline
    Registered User
    Join Date
    10-23-2019
    Location
    London
    MS-Off Ver
    2016
    Posts
    4
    Oh wow. That's really cool. Thank you so much for this!

    Quote Originally Posted by menem View Post
    Just find a formula that fits both file after SOLVED. T_T

    E18
    Please Login or Register  to view this content.
    Press Ctrl-Shift-Enter for ARRAY formula


    Regards.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Find highest text value in column based on criteria
    By hamroid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-12-2019, 01:49 AM
  2. [SOLVED] Summary page showing the highest scorer from a number of other tabs within the same WB
    By robbielaybourn in forum Excel General
    Replies: 6
    Last Post: 08-06-2017, 08:56 AM
  3. [SOLVED] Returning the highest text value from column per row
    By raphide in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-14-2017, 12:16 PM
  4. Excel 2010 ... I need to high the highest, second highest & third highest values in column
    By NosajTrah in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-09-2016, 12:51 PM
  5. [SOLVED] Summary section, of a column of seperate text
    By richard11153 in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 01-10-2016, 06:03 PM
  6. [SOLVED] find the highest text value in a row, and return the corresponding column header
    By bumbling-idiot in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-09-2012, 07:09 AM
  7. [SOLVED] 2 rows, highest No in row 1, then highest number in row 2 relating to that column, possible duplicates
    By John in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-10-2006, 11:40 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1