+ Reply to Thread
Results 1 to 6 of 6

Find if a cell has a value then return left most with text

  1. 10-21-2022, 04:02 AM #1
    bob112233
    bob112233 is offline
    Forum Contributor
    Join Date
    12-03-2020
    Location
    australia
    MS-Off Ver
    365
    Posts
    108

    Find if a cell has a value then return left most with text

    Hi,

    I have a simple table:

    Screenshot 2022-10-21 190252.png

    It outlines peoples profession and their years of experience. Eg John has been a pilot for 8 years, Lawyer for 6 and Teacher for 3.

    I want to use this table to create a new table that simply outlines their profession such as.

    John Adam Blake
    Pilot Lawyer Pilot
    Lawyer Sales Teacher
    Teacher

    I've tried first to use

    Please Login or Register  to view this content.
    but I didn't know how to locate cells with value and then obtain left most cell text.

    I've attached the workbook for your convivence. Any solutions/direction would be greatly appreciated.

    Thanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-21-2022, 04:06 AM #2
    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,830

    Re: Find if a cell has a value then return left most with text

    First, empty your results table, then this in F2 copied across:

    =FILTER($A$2:$A$5,B$2:B$5<>"","")
    Attached Files Attached Files
    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
  3. 10-21-2022, 04:07 AM #3
    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,053

    Re: Find if a cell has a value then return left most with text

    Try:
    =IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW(B$2:B$5)/(B$2:B$5>0),ROWS(F$2:F2))),"")

    in F2, copied across & down
    Attached Files Attached Files
    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
  4. 10-21-2022, 04:11 AM #4
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,466

    Re: Find if a cell has a value then return left most with text

    In F2:
    Please Login or Register  to view this content.
    Fill to other cells.

    Is it are you looking for?
    Quang PT
    Reply With QuoteRegister To Reply
  5. 10-21-2022, 04:13 AM #5
    bob112233
    bob112233 is offline
    Forum Contributor
    Join Date
    12-03-2020
    Location
    australia
    MS-Off Ver
    365
    Posts
    108

    Re: Find if a cell has a value then return left most with text

    Awesome!

    Thanks AliGW and Glenn!

    Extra points for the new function, filter!
    Reply With QuoteRegister To Reply
  6. 10-21-2022, 04:14 AM #6
    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,830

    Re: Find if a cell has a value then return left most with text

    Glad to have helped.

    FILTER is one of many new goodies.

    If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.
    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. Trying to find max/min but return cell to the left
    By chris.28.uk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-27-2017, 03:50 PM
  2. Find value in range, return value from left adjacent cell
    By lukestkd in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-28-2016, 06:01 AM
  3. Return text in cell to the left of search term
    By taniwha in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-10-2016, 05:53 AM
  4. [SOLVED] Find word in text string and return word adjacent to the left
    By eadamquinn in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-06-2014, 03:22 PM
  5. Find Text and display cell to left
    By Austin0896 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-13-2014, 08:13 PM
  6. [SOLVED] Find first left condition and return a corresponding cell value
    By JohannesFust in forum Excel General
    Replies: 10
    Last Post: 04-10-2012, 06:27 AM
  7. [SOLVED] Find text within cell then display text to left
    By Jambruins in forum Excel General
    Replies: 5
    Last Post: 04-17-2006, 05:10 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