+ Reply to Thread
Results 1 to 10 of 10

Create unique list from multiple columns using UNIQUE

  1. #1
    Registered User
    Join Date
    09-08-2018
    Location
    Toronto, Canada
    MS-Off Ver
    Office 365
    Posts
    23

    Create unique list from multiple columns using UNIQUE

    I have a range of data with 5 columns, containing training courses. I want to create a single list of unique values. I would prefer to use UNIQUE if possible and not an array formula because there data set is very large and the array would be too slow. I don't mind if I need to use 5 different UNIQUE functions and then hide some columns to get my result. Is this possible?

    For reference, I have named each range as Course1, Course2, Course3, etc.

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,671

    Re: Create unique list from multiple columns using UNIQUE

    This can be achieved with Power Query. I am not sure which presentation you prefer.

    Excel 2016 (Windows) 32 bit
    B
    C
    D
    E
    F
    18
    Course 1 Course 2 Course 3 Course 4 Course 5
    19
    Python for Engineers Ground Improvement Training Airphoto Interpretation Flow3D GeoStudio Training
    20
    Facilitation Training Leadership Advanced Feedback Training Other
    21
    Televiewer Data Interpretation Drilling 201 Construction Monitor Training Project Management
    22
    Civil Geotech Training Drilling 101 Glacial Deposition
    23
    Speak Like a Leader Training
    24
    Global Mapper - Practical Skills Muck 3D Training
    25
    HEC-RAS 1D & 2D
    26
    Dams 101
    27
    Pipeline Lead Inspector Training
    28
    Pipeline New Inspector Training Hydrogeology 101
    29
    Structural Geology Pipeline Construction and Geotechnical Field Support
    30
    Leadership Foundation
    Sheet: Sheet1

    which is generated with this code

    Please Login or Register  to view this content.
    or

    Excel 2016 (Windows) 32 bit
    B
    C
    18
    Attribute Value
    19
    Course 1 Python for Engineers
    20
    Course 2 Ground Improvement Training
    21
    Course 3 Airphoto Interpretation
    22
    Course 4 Flow3D
    23
    Course 5 GeoStudio Training
    24
    Course 1 Facilitation Training
    25
    Course 2 Leadership Advanced
    26
    Course 3 Feedback Training
    27
    Course 4 Other
    28
    Course 2 Televiewer Data Interpretation
    29
    Course 3 Drilling 201
    30
    Course 4 Construction Monitor Training
    31
    Course 5 Project Management
    32
    Course 1 Civil Geotech Training
    33
    Course 2 Drilling 101
    34
    Course 3 Glacial Deposition
    35
    Course 1 Speak Like a Leader Training
    36
    Course 2 Global Mapper - Practical Skills
    37
    Course 4 Muck 3D Training
    38
    Course 1 HEC-RAS 1D & 2D
    39
    Course 2 Dams 101
    40
    Course 2 Pipeline Lead Inspector Training
    41
    Course 3 Pipeline New Inspector Training
    42
    Course 5 Hydrogeology 101
    43
    Course 2 Structural Geology
    44
    Course 4 Pipeline Construction and Geotechnical Field Support
    45
    Course 2 Leadership Foundation
    Sheet: Sheet1

    which is generated with this code

    Please Login or Register  to view this content.
    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.

  3. #3
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    1,264

    Re: Create unique list from multiple columns using UNIQUE

    You can use this:

    =FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,Course1,Course2,Course3,Course4,Course5)&"</m></x>","//m")
    EDIT: Sorry, I forgot the UNIQUE part of the request, so
    =UNIQUE(FILTERXML("<x><m>"&TEXTJOIN("</m><m>",,Course1,Course2,Course3,Course4,Course5)&"</m></x>","//m"))

    However, it doesn't like the Ampersands (&) in some of your descriptions. If you remove or replace them this will work
    Attached Files Attached Files
    Last edited by Gregb11; 11-27-2020 at 10:40 PM.

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    9,876

    Re: Create unique list from multiple columns using UNIQUE

    Another option if you have the new LET function.

    =LET(Rng,FullTable,Rws,ROWS(Rng),Seq,SEQUENCE(Rws*COLUMNS(Rng),,0),Ary,INDEX(Rng,MOD(Seq,Rws)+1,Seq/Rws+1),SORT(UNIQUE(FILTER(Ary,Ary<>0))))

    Pinched the formula from a thread on MrE

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,000

    Re: Create unique list from multiple columns using UNIQUE

    IN B19 and copied down for UNQUE list
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 11-28-2020 at 10:34 AM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  6. #6
    Registered User
    Join Date
    09-08-2018
    Location
    Toronto, Canada
    MS-Off Ver
    Office 365
    Posts
    23

    Re: Create unique list from multiple columns using UNIQUE

    Hi Gregb11,

    Thank you so much. This is exactly the solution I'm looking for. However, there is one problem.

    My actual data is 1219 rows (including the headers). When I make the named ranges that long, then it doesn't work If I do it for 200 rows, it works, but not for the full amount of rows. Do you know why that may be?

    Thanks,

    Sean

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

    Re: Create unique list from multiple columns using UNIQUE

    Please try

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


    or with LET function

    =LET(d,FullTable,r,ROWS(d),c,COLUMNS(d),s,SEQUENCE(r*c,,0),u,UNIQUE(INDEX(d,s/c+1,MOD(s,c)+1)),SORT(FILTER(u,u<>"")))
    Attached Files Attached Files
    Last edited by Bo_Ry; 11-30-2020 at 02:15 PM. Reason: Filter blank

  8. #8
    Registered User
    Join Date
    09-08-2018
    Location
    Toronto, Canada
    MS-Off Ver
    Office 365
    Posts
    23

    Re: Create unique list from multiple columns using UNIQUE

    @Bo_Ry

    Thank you so much Bo_Ry. That worked perfectly and was exactly what I was looking for.

    Now to do a 3D lookup

  9. #9
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,671

    Re: Create unique list from multiple columns using UNIQUE

    @ping
    No comment on the PQ solution?

  10. #10
    Valued Forum Contributor
    Join Date
    08-15-2020
    Location
    malaysia
    MS-Off Ver
    office2007
    Posts
    615

    Re: Create unique list from multiple columns using UNIQUE

    PQ is the best choice, I do not have 365 and 2019, and the formula can also solve this problem

    B19 cell , array formula , copy and drag down

    HTML Code: 
    =IFERROR(INDIRECT(TEXT(SMALL(IF($B$3:$F$14<>"",ROW($3:$14)/1%%+COLUMN($B:$F)),ROW(A1)),"r0c0000"),),"")
    If you want to remove the duplicate formula, you can do this

    B19 cell , array formula , copy and drag down

    HTML Code: 
    =INDIRECT(TEXT(MIN(IF((COUNTIF($B$18:B18,$B$3:$F$14)=0)*($B$3:$F$14<>""),ROW($3:$14)/1%+COLUMN(B:F),9999)),"r0c00"),)&""
    Last edited by wk9128; 11-30-2020 at 10:49 PM.

+ 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. Create a unique list with a count against the unique values
    By barber87 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-12-2017, 10:13 AM
  2. Create unique # of worksheets based on list count with unique names
    By Groovicles in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-24-2016, 09:51 AM
  3. VBA to create a unique list from two columns
    By The_Snook in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-23-2016, 01:57 AM
  4. [SOLVED] Create a unique list from multiple columns with blamks
    By JO505 in forum Excel General
    Replies: 14
    Last Post: 11-18-2015, 08:25 PM
  5. Create a Distinct Unique list for 2 Columns
    By boldcode in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-06-2014, 05:10 PM
  6. Replies: 7
    Last Post: 07-27-2013, 10:11 PM
  7. [SOLVED] create list of unique staff numbers with unique work codes
    By shaz0503 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-01-2013, 08:57 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