+ Reply to Thread
Results 1 to 2 of 2

Multiple Unique Distinct Values from Multiple Columns of Data

  1. #1
    Registered User
    Join Date
    03-02-2011
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    2

    Multiple Unique Distinct Values from Multiple Columns of Data

    Hello
    I have a biggish spreadsheet of 150 columns and 14000 rows (with blanks in many cells). What I need is a summary sheet for each column showing what unique and distinct (I get the terms mixed up so sorry) values for each column. I know I could use advanced filter on each column but I don't relish this idea as this is just the first sheet of many.

    I have seen the following array formula but it only works on my first column leaving me with 149 to go - it doesn't like moving to new columns and hates being on a different sheet.

    {=INDEX(List (A$2:A$10),MATCH(0,COUNTIF($B$1:B1,List (A$2:A$10)),0))}

    I have attached a very simple excel file to simply show what I have and what I want to get on two separate sheets.

    Help pretty please.
    Attached Files Attached Files

  2. #2
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Multiple Unique Distinct Values from Multiple Columns of Data

    Try this
    Using an intermediate sheet for all helper columns ("NoDuplicates")
    In Sheet "NoDuplicates" A2
    Please Login or Register  to view this content.
    Drag Across as required, then Down
    This, as the sheet name suggests, should remove duplicate values from your source sheet, and also convert all numbers to text.

    This then means that we can now use one formula for Text and Numeric columns to "Remove Blanks" (Not strictly true, it removes duplicates, all of which now should be blanks, hence row 2 is blank.)

    In Sheet "NoBlanks" A2
    Please Login or Register  to view this content.
    Drag Across as required, then Down

    You can now copy this sheet and Paste Special > Values to your desired sheet, and convert the "numeric" columns back to numbers (Use Text to columns and then format the column "General").

    There must be a way to do this without the helper sheet, but I can't see an easier way.
    Maybe some of the formula cavalry will provide a better answer....

    Hope this helps
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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