+ Reply to Thread
Results 1 to 6 of 6

List unique values from a list

  1. 12-22-2014, 08:11 PM #1
    klturi421
    klturi421 is offline
    Registered User
    Join Date
    06-15-2011
    Location
    Converse, TX
    MS-Off Ver
    Excel 2016
    Posts
    72

    List unique values from a list

    I have a formula that looks at an existing list of people and outputs the unique values. However, one snag I am running into is that the first value after the last unique value ends up being 0. I've tried using an IFERROR formula but that does not seem to work properly.

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


    This is an array formula requiring CTRL+SHIFT+Enter for it to calculate.

    Example of what it ends up looking like:

    Agent Unique
    Bob Bob
    Bob Ashley
    Ashley Stacy
    Ashley Jennifer
    Jennifer John
    Stacy 0
    John
    John
    Stacy
    Reply With QuoteRegister To Reply
  2. 12-22-2014, 08:15 PM #2
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: List unique values from a list

    try this one

    =IFERROR(INDEX($A$2:$A$20,MATCH(0,IF(ISBLANK($A$2:$A$20),"",COUNTIF(B$1:$B1, $A$2:$A$20)), 0)),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    Row\Col
    A
    B
    1
    		 Agent Unique
    2
    		 Bob Bob
    3
    		 Bob Ashley
    4
    		 Ashley Jennifer
    5
    		 Ashley Stacy
    6
    		 Jennifer John
    7
    		 Stacy
    8
    		 John
    9
    		 John
    10
    		 Stacy
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  3. 12-22-2014, 08:20 PM #3
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: List unique values from a list

    alternatively
    use a named range to adjust your length of array to look at
    see attached example
    Attached Files Attached Files
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  4. 12-23-2014, 12:43 AM #4
    klturi421
    klturi421 is offline
    Registered User
    Join Date
    06-15-2011
    Location
    Converse, TX
    MS-Off Ver
    Excel 2016
    Posts
    72

    Re: List unique values from a list

    Both work perfectly! Thank you for the help with both options!
    Reply With QuoteRegister To Reply
  5. 12-23-2014, 01:03 AM #5
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,946

    Re: List unique values from a list

    Here is a non-array version...
    =IFERROR(INDEX($B$49:$B$73,MATCH(0,INDEX(COUNTIF($E$48:E48,$B$49:$B$73),),0)),"")

    adjust ranges as needed
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  6. 12-23-2014, 04:57 AM #6
    Aladin Akyurek
    Aladin Akyurek is offline
    Forum Contributor
    Join Date
    12-23-2003
    Posts
    179

    Re: List unique values from a list

    Let A1:A10 of Sheet1 house the data of interest.

    Define DATA as referring to;
    Please Login or Register  to view this content.
    Define Ivec as refereing to:
    Please Login or Register  to view this content.
    B2, control+shift+enter, not just enter, and copy down:
    Please Login or Register  to view this content.
    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] make list of values corresponding to unique values in parallel list
    By johnandrews in forum Excel General
    Replies: 3
    Last Post: 11-18-2014, 09:46 AM
  2. List of unique text values from a list with duplicates
    By canoeron in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 03-11-2014, 11:27 PM
  3. 3 columns: None contain unique values, but I need a list of every unique set
    By mathematician in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-15-2012, 04:47 PM
  4. List unique values
    By Yamaha Rider in forum Excel General
    Replies: 3
    Last Post: 04-20-2011, 09:54 AM
  5. Data Validation List - Unique Values from a List
    By kwsmith in forum Excel General
    Replies: 0
    Last Post: 07-17-2007, 04:56 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