+ Reply to Thread
Results 1 to 6 of 6

Return cell with longest character limit based on additional criteria

  1. #1
    Forum Contributor
    Join Date
    08-17-2009
    Location
    West Midlands
    MS-Off Ver
    Excel 2016
    Posts
    213

    Return cell with longest character limit based on additional criteria

    Hi all -

    I am looking for some help creating a formula.

    Column A1:A20 has a satisfaction score between 1 and 10
    Column B1:B20 has text (customer comments)

    What I need:

    Column G1:G10 contains the descending numbers 1 to 10

    I would like column H1:H20 to return the longest text from column B based on the satisfaction score (column A to match column G).

    Apologies for the long-winded description but my business firewall doesn't allow image or file uploads.

    Thanks for any help
    SA
    Last edited by SAsplin; 04-12-2018 at 09:07 AM.

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,239

    Re: Return cell with longest character limit based on additional criteria

    In cell H1 entered as an array formula copied down
    =MAX(LEN(B:B)*(A:A=G1))

    an array formula is confirmed with {CTRL}{SHIFT}{ENTER}

    The formula in the cell will then look like this {=MAX(LEN(B:B)*(A:A=G1))}
    - typing { around a formula } does not work

    see attached workbook
    Attached Files Attached Files
    Last edited by kev_; 04-12-2018 at 06:30 AM.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Forum Contributor
    Join Date
    08-17-2009
    Location
    West Midlands
    MS-Off Ver
    Excel 2016
    Posts
    213

    Re: Return cell with longest character limit based on additional criteria

    Thanks for the reply kev_

    However, I need the formula to return the actual text rather than the character length value, e.g. if the longest text for the customer score 1 was 'test test test' the formula result should be 'test test test'.

    Thanks
    SA

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2010
    Posts
    4,462

    Re: Return cell with longest character limit based on additional criteria

    Try this in H1:

    =INDEX($B$1:$B$20,MATCH(MAX(IF($A$1:$A$20=G1,LEN($B$1:$B$20))),IF($A$1:$A$20=G1,LEN($B$1:$B$20)),0))

    Array formula, enter with Ctrl+Shift+Enter.

  5. #5
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,239

    Re: Return cell with longest character limit based on additional criteria

    However, I need the formula to return the actual text
    Go with Phuocam's formula

  6. #6
    Forum Contributor
    Join Date
    08-17-2009
    Location
    West Midlands
    MS-Off Ver
    Excel 2016
    Posts
    213

    Re: Return cell with longest character limit based on additional criteria

    That's the badger! Thanks guys - I was tearing my hair out with combinations of MAX, INDEX, MATCH and LEN.

+ 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. Need formula for character limit based on Width of W
    By MindyK in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-16-2016, 08:14 PM
  2. [SOLVED] Character Limit in a Cell
    By Biplab1985 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-19-2015, 11:31 AM
  3. additional value to a cell based on criteria from another cell
    By JohnnyBoyxxx in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-24-2014, 07:39 AM
  4. Replies: 1
    Last Post: 11-27-2013, 04:53 PM
  5. Display Active Character Count + Limit Character Per Cell
    By euronail in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-22-2011, 04:59 AM
  6. Character limit per cell
    By BrianR in forum Excel General
    Replies: 3
    Last Post: 05-19-2005, 12:06 PM
  7. [SOLVED] Adding additional rows in VBA based on character strings
    By Mike in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-08-2005, 03:06 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