+ Reply to Thread
Results 1 to 7 of 7

Extract the list of unique values from a Table

  1. #1
    Registered User
    Join Date
    11-03-2020
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2019
    Posts
    18

    Post Extract the list of unique values from a Table

    Hello there,

    I have a table with 3 columns. The 1st column simply concatenates the next two columns, so it can be ignored if it doesn't help.

    I want to obtain the list of unique values in the 3rd column (except one particular phrase), if the 2nd column matches a specific cell reference.

    An alphabetical output is preferred, but not required.

    Please see the attached file for a clearer overview. Any help is greatly appreciated.

    I'm somewhat experienced with Excel, but I simply cannot get this working. This is my first post on Excelforum. Thank you.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: Extract the list of unique values from a Table

    Array formula is needed for sorting:

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


    see sheet.
    Attached Files Attached Files
    Glenn



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

    Re: Extract the list of unique values from a Table

    An alternative solution is to employ Power Query also called Get & Transform found on the Data Tab.

    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.
    Attached Files Attached Files

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,841

    Re: Extract the list of unique values from a Table

    Employ a helper column D, to avoid using array formula:

    D2:

    Please Login or Register  to view this content.
    Then first cell in result list:

    Please Login or Register  to view this content.
    Drag down
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-03-2020
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2019
    Posts
    18

    Re: Extract the list of unique values from a Table

    Quote Originally Posted by bebo021999 View Post
    Employ a helper column D, to avoid using array formula:

    D2:

    Please Login or Register  to view this content.
    Then first cell in result list:

    Please Login or Register  to view this content.
    Drag down
    Hi bebo021999,

    Thank you so much for this. It does exactly what I asked, but I now face a new problem as my requirement has changed slightly.

    Is it possible to make these two amendments please:
    1. Instead of matching the value in F2 (formerly E2), can in match only the first 4 characters (i.e. only the year)? My sample data initially only showed 2020 values, but in reality, I have a large set of data from 2012.
    2. Instead of removing "TOTAL" from the list, is there a way to always include that at the end of the alphabetical list?

    Thank you again for all your help, and sorry for the late reply.
    Attached Files Attached Files
    Last edited by Rehman5000; 11-05-2020 at 03:30 AM.

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,841

    Re: Extract the list of unique values from a Table

    Try again in D2:
    Please Login or Register  to view this content.
    In I9:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-03-2020
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2019
    Posts
    18

    Re: Extract the list of unique values from a Table

    Quote Originally Posted by bebo021999 View Post
    Try again in D2:
    Please Login or Register  to view this content.
    In I9:
    Please Login or Register  to view this content.
    Thanks buddy. This is beyond my comprehension, and almost magic to me. Cheers for all the help.

+ 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. Extract Unique Values from Filtered List
    By kruss777 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-13-2019, 02:09 PM
  2. [SOLVED] extract unique values from list (UDF style)
    By aarona in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-16-2019, 01:22 AM
  3. Extract Unique Values from List using VBA
    By musicman1985 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-03-2018, 05:24 AM
  4. Extract unique values in a column from a table
    By gandreso in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-17-2017, 11:21 AM
  5. Replies: 4
    Last Post: 01-04-2017, 07:29 PM
  6. [SOLVED] automatically extract unique values from a list
    By labogola in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-07-2014, 10:54 AM
  7. [SOLVED] Extract unique values from a table and list along a row
    By doctorblyth in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-05-2013, 04:33 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