+ Reply to Thread
Results 1 to 6 of 6

Unique formula with several columns

  1. #1
    Registered User
    Join Date
    04-25-2017
    Location
    Sweden
    MS-Off Ver
    Microsoft Office 365 ProPlus Version 2002
    Posts
    27

    Unique formula with several columns

    Hello,

    I have made an excel to illustrate my question.

    I have two columns that give information. I would like to in one column list the unique values/remove duplicates.

    The UNIQUE() Formula works well when there is only one column. However when I add another column my ouput also gets placed in another column.

    Is there an easy way to fix this?

    /Peter
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,773

    Re: Unique formula with several columns

    Please try

    =UNIQUE(SMALL(C3:D16;SEQUENCE(COUNT(C3:D16))))
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    04-25-2017
    Location
    Sweden
    MS-Off Ver
    Microsoft Office 365 ProPlus Version 2002
    Posts
    27

    Re: Unique formula with several columns

    Quote Originally Posted by Bo_Ry View Post
    Please try

    =UNIQUE(SMALL(C3:D16;SEQUENCE(COUNT(C3:D16))))
    Thank you for spending time on answering. It worked on this example sheet. However, in my real data set, I am using non-numerical values and this formula does not seem to work on that. Is it possible to adjuts it slightly to make it work for non-numerical? I've tied using the COUNTA function instead of COUNT but didnt do the job....

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    5,773

    Re: Unique formula with several columns

    Try this
    =UNIQUE(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,C3:D16)&"</m></x>","//m"))
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-25-2017
    Location
    Sweden
    MS-Off Ver
    Microsoft Office 365 ProPlus Version 2002
    Posts
    27

    Re: Unique formula with several columns

    Quote Originally Posted by Bo_Ry View Post
    Try this
    =UNIQUE(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,C3:D16)&"</m></x>","//m"))
    Didnt work

  6. #6
    Registered User
    Join Date
    11-21-2013
    Location
    zimbabwe
    MS-Off Ver
    Excel 2003
    Posts
    94

    Re: Unique formula with several columns

    Try:
    Please Login or Register  to view this content.
    Array formula, ended with Ctrl+Shift+Enter
    Hope this helps

+ 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. Formula to count unique data from two columns
    By royaljay01 in forum Excel General
    Replies: 5
    Last Post: 10-30-2020, 07:23 PM
  2. Replies: 8
    Last Post: 08-29-2020, 09:25 AM
  3. Help fix, almost perfect, formula that extracts unique list from two columns
    By carlito2002wgn in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-13-2020, 03:15 AM
  4. [SOLVED] Formula to count unique columns
    By rmlucas in forum Excel General
    Replies: 6
    Last Post: 05-15-2017, 09:51 AM
  5. Unique Values From All Columns Formula
    By nandkishorskale in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-10-2013, 09:09 AM
  6. Replies: 2
    Last Post: 07-17-2013, 10:42 AM
  7. Formula to check unique values based on data from 2 columns
    By notsobrilliant in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-10-2013, 04:15 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1