+ Reply to Thread
Results 1 to 3 of 3

Extracting unique values from a list in multiple columns into a single column (by formula)

  1. #1
    Registered User
    Join Date
    06-16-2016
    Location
    Cape Town
    MS-Off Ver
    2013
    Posts
    3

    Extracting unique values from a list in multiple columns into a single column (by formula)

    Hey all

    My name is Mukun and im looking for help retrieving a unique set of text values from a list of 16 columns (80 rows). Column A contains the text values and Column B contains numeric values. This continues for 16 columns. there are duplicate text values and blanks in the list as well.

    I would like to use a formula (or array formula) that retrieves only the unique text values (not unique number value) from the list into a single column. does not need to be alphabetical.

    Is this possible?

    I see there is an array formula for 3 columns

    =IFERROR(IFERROR(IFERROR(INDEX($A$2:$A$20, MATCH(0, COUNTIF($D$1:D1, $A$2:$A$20)+($A$2:$A$20=""), 0)), INDEX($B$2:$B$7, MATCH(0, COUNTIF($D$1:D1, $B$2:$B$7)+($B$2:$B$7=""), 0))), INDEX($C$2:$C$12, MATCH(0, COUNTIF($D$1:D1, $C$2:$C$12)+($C$2:$C$12=""), 0))), "")

    but how to expand to 8 columns?

    Thanks!

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Extracting unique values from a list in multiple columns into a single column (by form

    An easy way to do it is to use three columns of formulas.

    Let's say that your table is in A1:P81, with headers in row 1 (so 80 rows of values)

    In Q1, enter "All Values". In Q2, enter

    =IFERROR(INDEX($A$2:$O$81,MOD(ROW(A1)-1,80)+1,INT((ROW(A1)-1)/80)*2+1),$A$2)

    In R1, enter "Index" and in R2 enter

    =IF(COUNTIF($Q$2:Q2,Q2)=1,ROW(),"")

    and in S1 Enter Unique Values, and in S2, enter

    =IFERROR(INDEX(Q:Q,MATCH(LARGE(R:R,COUNTIF(R:R,">0")-ROW(A1)+1),R:R,FALSE)),"")

    Copy all three columns down for 640 rows, and the list in S will be the unique values.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Extracting unique values from a list in multiple columns into a single column (by form

    See this...

    https://www.excelforum.com/showthread.php?t=961201
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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. Replies: 6
    Last Post: 05-26-2016, 11:12 PM
  2. List of Unique Values from Multiple Columns
    By filkod in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-06-2015, 03:57 AM
  3. [SOLVED] Extracting Unique Values From a List...using formula
    By szejtan99 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-24-2014, 07:42 PM
  4. Replies: 9
    Last Post: 10-14-2013, 07:55 PM
  5. [SOLVED] Formula extracting unique column values
    By labogola in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-10-2013, 02:34 AM
  6. [SOLVED] Compare two columns and list the unique values in 3rd column
    By jewellove in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-10-2013, 10:40 AM
  7. Replies: 5
    Last Post: 04-21-2011, 05:22 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1