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.
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
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
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
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.
Go with Phuocam's formulaHowever, I need the formula to return the actual text
That's the badger! Thanks guys - I was tearing my hair out with combinations of MAX, INDEX, MATCH and LEN.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks