+ Reply to Thread
Results 1 to 6 of 6

Filter unique Values without macro/advanced filter/remove duplicates

  1. #1
    Registered User
    Join Date
    10-10-2013
    Location
    Rakovnik
    MS-Off Ver
    Excel 2007
    Posts
    25

    Filter unique Values without macro/advanced filter/remove duplicates

    Hello everyone,

    Is there any way to filter unique values from a column without using a macro/advanced filter/remove duplicates/pivot table?
    In excel 2013 they introduced DISTINCT(<column>). I would need sth similar for Excel 2010/2007.
    EG:
    A: B:
    ana ana
    apa apa
    aca aca
    ala ala
    apa asa
    aca
    asa

    Thank you!

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Filter unique Values without macro/advanced filter/remove duplicates

    Assuming your data start in A1, in B1 put this.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In B2 and copy down this one.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    8,020

    Re: Filter unique Values without macro/advanced filter/remove duplicates

    Data starts in A2
    Array formula (remember about Ctrl+Shift+Enter) in B2:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    10-10-2013
    Location
    Rakovnik
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: Filter unique Values without macro/advanced filter/remove duplicates

    Thank you guys!

    And what if I have the column A with data and I want unique values in one single cell like text?
    eg:
    A:
    ana
    apa
    ala
    ica
    apa
    ian


    cell B1: "ana,apa,ala,ica,ian"


    any ideea?

  5. #5
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Filter unique Values without macro/advanced filter/remove duplicates

    Use Column B as helper and hidden column and use something like this in C1...

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


    Or you have to use VBA.

  6. #6
    Valued Forum Contributor
    Join Date
    09-15-2011
    Location
    Chennai, India
    MS-Off Ver
    Excel 2010
    Posts
    436

    Re: Filter unique Values without macro/advanced filter/remove duplicates

    Please try this VBA UDF (Not mine)

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Please click 'Add reputation', if my answer helped you.

+ 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. Remove Duplicates from Advanced Filter Results
    By joedrummer in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-24-2013, 11:56 AM
  2. [SOLVED] Run-time error 1004 when running Advanced Filter for Unique Values in macro
    By jacobadger in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-17-2012, 02:17 PM
  3. Advanced filter to get unique values
    By brumbarchris in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-13-2012, 12:21 PM
  4. Advanced Filter - Unique Values Macro Returns 1st Value Twice
    By r101 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-11-2011, 05:53 PM
  5. [SOLVED] Advanced Filter - Unique Values
    By EstherJ in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-22-2005, 09:05 AM

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