+ Reply to Thread
Results 1 to 9 of 9

Extract unique values from multiple columns using "UNIQUE" dynamic array function

  1. #1
    Registered User
    Join Date
    09-18-2012
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    5

    Extract unique values from multiple columns using "UNIQUE" dynamic array function

    I am trying to use the new dynamic array formulas to return a list of unique values from multiple columns. I've seen how to do this with the "ctrl+shift+enter" arrays, but I was hoping to achieve it with a more efficient formula. I access to the new formulas, so it is a just a question of how to use the formula, not access it. Simply using the =UNIQUE(A1:B3) returns each unique row, not each unique value.

    apple | banana | pear
    cherry | apple | banana
    mango | cherry | grape

    to:

    apple
    banana
    grape
    mango
    pear
    cherry


    Thank you,
    Adam

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,168

    Re: Extract unique values from multiple columns using "UNIQUE" dynamic array function

    Hi Adam and welcome to the forum,

    I think you only get a single column for each column you do Unique on. See:
    https://www.excelcampus.com/function...-spill-ranges/
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    09-18-2012
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    5

    Re: Extract unique values from multiple columns using "UNIQUE" dynamic array function

    Does this mean there is no way to combine the "UNIQUE" function with other functions to get the desired result?

    Thank you,
    Adam

  4. #4
    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: Extract unique values from multiple columns using "UNIQUE" dynamic array function

    Try this:
    Enter formula in E2 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B C D E
    1 Unique List
    2 apple banana pear apple
    3 cherry apple banana banana
    4 mango cherry grape pear
    5 cherry
    6 mango
    7 grape
    8
    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

  5. #5
    Registered User
    Join Date
    09-18-2012
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    5

    Re: Extract unique values from multiple columns using "UNIQUE" dynamic array function

    Thank you AIKey, this does dynamically update, which was my main goal in wanting to use the UNIQUE function. One issue that I still have is that if I add incomplete rows to the list, it doesn't count the value. For example, if I add "peach" to cell A5, it isn't added to the list unless there are values in B5 and C5 as well. Is there a way to include partial rows?

    Thank you,
    Adam

  6. #6
    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,903

    Re: Extract unique values from multiple columns using "UNIQUE" dynamic array function

    UNIQUE is new - is your user profile up-to-date? It says you are using Excel 2010, which might explain the responses you are getting.
    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.

  7. #7
    Registered User
    Join Date
    09-18-2012
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    5

    Re: Extract unique values from multiple columns using "UNIQUE" dynamic array function

    Okay, thank you. I've updated my profile to show the correct version.

    Adam

  8. #8
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Extract unique values from multiple columns using "UNIQUE" dynamic array function

    Please try at E1
    =IFERROR(SORT(UNIQUE(INDEX(A1:C9,CEILING.MATH(SEQUENCE(50)/ROWS(A1:C9)),MOD(SEQUENCE(50,,0),ROWS(A1:C9))+1)))&"","")
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    09-18-2012
    Location
    United States
    MS-Off Ver
    Excel 365
    Posts
    5

    Re: Extract unique values from multiple columns using "UNIQUE" dynamic array function

    Awesome! Thank you....

+ 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. Dynamic array - unique values from multiple columns
    By istank in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 02-04-2021, 02:42 AM
  2. countif to return "x" for unique & "xx" for duplicate values in a column
    By Shruder in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-27-2018, 05:31 PM
  3. Extract data for "failed" status only with unique application by month
    By suchetherrah in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-15-2016, 06:52 AM
  4. Extract unique values from multiple columns
    By potejam in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 12-16-2015, 08:26 PM
  5. Replies: 0
    Last Post: 11-22-2013, 01:36 PM
  6. [SOLVED] Extract unique values in a column that end in "ago"
    By blinks58 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-06-2013, 06:00 PM
  7. looking up value in table (multiple "unique id" columns for no vlookup)
    By kuraitori in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-31-2009, 02:50 AM

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