+ Reply to Thread
Results 1 to 9 of 9

Sorting unique data from more columns

  1. #1
    Registered User
    Join Date
    05-13-2008
    Posts
    6

    Exclamation Sorting unique data from more columns

    I need to sort out the uniques values from column A and column B.

    A B
    asdf 123
    asdf xyz
    asdf2 xyz


    This is how my columns look. I would like to keep only asdf2 since asdf is in duplicate. How do i do that?

    Thank you.

  2. #2
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    Assuming that A2:B10 contains the data, try...

    D2, confirmed with CONTROL+SHIFT+ENTER:

    =SUM(IF(FREQUENCY(IF(A2:A10<>"",MATCH("~"&A2:A10,A2:A10&"",0)),ROW(A2:A10)-ROW(A2)+1)=1,1))

    E2, confirmed with CONTROL+SHIFT+ENTER, and copied down and across:

    =IF(ROWS(D$2:E2)<=$D$2,INDEX(A$2:A$10,MATCH(1,COUNTIF($A$2:$A$10,IF(COUNTIF($E$1:$E1,$A$2:$A$10)=0,$A$2:$A$10)),0)),"")

    Hope this helps!

  3. #3
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    Further to your Private Message, the solution I offered should return the desired result. Have a look at the attached sample file.

    Hope this helps!

    P.S. Please keep all questions on the Board, thanks!
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    05-13-2008
    Posts
    6
    Sorry about that. I will post the PM sent to you here:

    thank you for answering my topic. D2 returns me the number of "real unique" values and E2 returns me the first "real unique" value. but i need to filter 2 columns containing 2 rows. is it possible to use it as criteria range? or how can i make it that from a huge list to filter only the "real unique" values? every 2 columns contain 2 rows too.

    i don't know if you understand me.

    A B
    asdf xyz
    asdf xyz2
    asdf2 qwe
    asdf3 qwe2
    asdf4 poi
    asdf4 plk
    asdf5 hhh


    I would like to keep asdf2 with column B as well. So after the filter to have:

    A B
    asdf2 qwe
    asdf3 qwe2
    asdf5 hhh

    Is it possible?

  5. #5
    Registered User
    Join Date
    05-13-2008
    Posts
    6
    Your example is perfect, but i still don't know how to apply that formula to a full 40k rows sheet.

  6. #6
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    With the data covering 40k rows, the solution I offered will be very slow. Try Advanced Filter instead. Assuming A1:B40000 contains the data, including the column headers, try...

    C1: leave empty

    C2:

    =COUNTIF($A$2:$A$40000,A2)=1

    Data > Filter > Advanced Filter

    List range: $A$1:$B$40000

    Criteria range: $C$1:$C$2

    Click Ok

    Hope this helps!

  7. #7
    Registered User
    Join Date
    05-13-2008
    Posts
    6
    How much time can it take with the first solution?

    I tried the second solution. It returns a TRUE value in C2 and if i select the rows with the header it returns the same list value. If i select the rows without the header it returns the unique values from the list, but it also returns the double value once.

    From the example given it returns this:

    A B
    asdf xyz2
    asdf2 qwe
    asdf3 qwe2

    I think i will do them manually. Thank you tho.

  8. #8
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    Quote Originally Posted by testaredescript
    How much time can it take with the first solution?
    Have a look at the first sheet in the attached sample file. Adjust the range to include the the actual range and you'll see that the calculation time will be very slow. Also, you'll notice I made a correction to the second formula. I replaced...

    ROWS(D$2:E2)

    with

    ROWS(E$2:E2)

    I tried the second solution. It returns a TRUE value in C2 and if i select the rows with the header it returns the same list value. If i select the rows without the header it returns the unique values from the list, but it also returns the double value once.

    From the example given it returns this:

    A B
    asdf xyz2
    asdf2 qwe
    asdf3 qwe2

    I think i will do them manually. Thank you tho.
    Have a look at the second sheet of the attached sample file. You'll see that Advanced Filter returns the desired result.

    Hope this helps!
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    05-13-2008
    Posts
    6
    Great! Got it now. Thank you very much. I didn't thought this is possible

+ 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