+ Reply to Thread
Results 1 to 7 of 7

Better way to list uniques

  1. #1
    Registered User
    Join Date
    04-06-2014
    Location
    DK
    MS-Off Ver
    Excel 2010
    Posts
    18

    Better way to list uniques

    Hi all

    I have found a way to list uniques from a long list.

    {=INDEX(*datalist*,MATCH(0,COUNTIF($B$1:B2,*datalist*),0))}

    It workes fine, but it is very slow. As soon as I get data lists with 50 uniques or so, it starts to take a lot of time for it to list the uniques. does anyone know of a better way?

    Best
    H
    Attached Files Attached Files

  2. #2
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Better way to list uniques

    Hey you can simply use the conditional formatting for this. You just need to select the column and now press Alt> H> L> H> D now all the duplicate will highlighted and make filter according color.

    Another way is select the data and press Alt> M and then OK all the duplicate will delete and unique list will only left.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Better way to list uniques

    Is it possible to sort your data. Then time can be reduced.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 09-18-2015 at 07:36 AM.

  4. #4
    Registered User
    Join Date
    04-06-2014
    Location
    DK
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Better way to list uniques

    Thanks for your suggestions, but

    shukla.ankur281190 - I need something more dynamic then that, and also I have a Danish versin of Excel, and the shortcuts you have described are not possible
    kvsrinivasamurthy - it is not possible to sort it. I ge a very big data dump, and will need to list the uniques from several columns, and sorting one of the columns does not make the others sorted.....

    Best
    H

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

    Re: Better way to list uniques

    I didn't download your file.

    The fastest way to get uniques is to use advanced filter>unique records only.

    However, this is not dynamic. You can use a macro to apply the filter and attach the macro to a button which would be "almost dynamic".
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Forum Contributor
    Join Date
    12-13-2013
    Location
    Calgary,AB,Canada
    MS-Off Ver
    2016,O365
    Posts
    236

    Re: Better way to list uniques

    personally, I don't think to use array formula in your case is a good idea, because of the row# in your file is huge.

    I have the similar task every week, to increase proceeding time, I prefer to use helper column

    1. copy entire column B then paste it to column I
    2. I4=IF(COUNTIF($B$1:B4,B4)=1,ROW(),"/"), then copy down
    3. J4=IFERROR(INDEX(B:B,SMALL(I:I,ROW(A1))),""), copy down until you see the blank.
    J4:J10 is what you need

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Better way to list uniques

    Here is a non-array version of your formula, it should be quicker...
    =IFERROR(INDEX($B$4:$B$20000,MATCH(0,INDEX(COUNTIF($E$3:E3,$B$4:$B$20000),),0)),"")

    Another option (manual) would be to copy all data from column B to column D, then use teh Remove Duplicate option under the Data tab. You could record a macro to do that for you
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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: 4
    Last Post: 01-29-2013, 04:25 PM
  2. Count Uniques
    By John Bates in forum Excel General
    Replies: 13
    Last Post: 10-12-2010, 01:57 PM
  3. Replies: 2
    Last Post: 05-07-2008, 08:17 PM
  4. count uniques
    By prasjohn in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-20-2008, 01:34 PM
  5. Return Uniques?
    By khaos in forum Excel General
    Replies: 10
    Last Post: 12-28-2006, 07:55 PM
  6. SUM uniques
    By Fin Fang Foom in forum Excel General
    Replies: 14
    Last Post: 07-21-2006, 04:13 PM
  7. [SOLVED] Count Uniques within a list based on value of cell...
    By MeatLightning in forum Excel General
    Replies: 3
    Last Post: 03-20-2006, 01:25 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