+ Reply to Thread
Results 1 to 6 of 6

Formula to only retrieve the first instance of something

  1. 11-04-2021, 05:39 AM #1
    oneblondebrow
    oneblondebrow is offline
    Forum Contributor
    Join Date
    03-17-2013
    Location
    London, England
    MS-Off Ver
    Microsoft 365 MSO (Version 2202 Build 16.0.14931.20648) 32-bit
    Posts
    155

    Formula to only retrieve the first instance of something

    Hi all,

    I am looking for a formula that will only list the first instance of a name, so for instance i have a list of names like below in Column A, what i want in in Column C is a formula to only list the first instances of the names, so with the below it would only list Jeff, Gary, Angela and Claire, is this even possible in a formula?

    Jeff
    Jeff
    Jeff
    Gary
    Gary
    Jeff
    Gary
    Angela
    Angela
    Gary
    Claire
    Claire
    Jeff
    Claire

    Many thanks all
    Reply With QuoteRegister To Reply
  2. 11-04-2021, 05:45 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. 2405 (Windows 11 23H2 64-bit)
    Posts
    81,434

    Re: Formula to only retrieve the first instance of something

    Administrative Note:

    Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

    If you have upgraded to Excel 2021 (or MS365), then you'll have the UNIQUE function:

    =UNIQUE(A:A)

    or:

    =FILTER(UNIQUE(A:A),UNIQUE(A:A)<>"","")
    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. 11-04-2021, 05:46 AM #3
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,621

    Re: Formula to only retrieve the first instance of something

    See: https://exceljet.net/formula/extract...ms-from-a-list
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  4. 11-04-2021, 05:55 AM #4
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,621

    Re: Formula to only retrieve the first instance of something

    If it is 365, then
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Or
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 11-04-2021, 06:01 AM #5
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,621

    Re: Formula to only retrieve the first instance of something

    Based on the link provided, in cell C2 and copied down:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    committed with Carl-Shift-Enter rather than just Enter
    Reply With QuoteRegister To Reply
  6. 11-04-2021, 06:13 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. 2405 (Windows 11 23H2 64-bit)
    Posts
    81,434

    Re: Formula to only retrieve the first instance of something

    Thanks for the rep.

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

    NOTE: If UNIQUE works for you, then your forum profile needs updating!!!
    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] Formula to Return Each Instance of a Result
    By hmltnangel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-12-2018, 11:31 AM
  2. Finding last instance and second last instance in a list
    By CRMORE in forum Excel General
    Replies: 3
    Last Post: 05-07-2016, 04:48 PM
  3. Retrieve the Text which may have prefix/suffix in the database & retrieve the highestvalue
    By Sivashanmugam in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-10-2015, 12:58 PM
  4. [SOLVED] Formula to find last instance in list
    By NS4Excel in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-07-2015, 02:18 AM
  5. Simplify: Can I even copy a worksheet from one instance of excel into another instance?
    By pglufkin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-15-2014, 09:50 AM
  6. finding the nth instance in a formula
    By ammartino44 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-22-2014, 05:12 AM
  7. formula to highlight every instance apart from first
    By Blake 7 in forum Excel General
    Replies: 2
    Last Post: 11-08-2010, 05:58 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1