0

I have a list of 35k contacts, I need to sort unique after unique

I demonstrated in this example how I want to sort contacts:

Example

I tried by removing duplicates then to copy unique and to go back to compare with original file, but that process takes time and it's not easy.

Improve this question
2
  • You need to explain a lot clearer what you want to do. I thought I understood it at first glance, but then I noticed that “51” appears twice in the “1st Unique” sublist, and “3” doesn’t appear in the “3st Unique” sublist, although it appears three times in the “A” list.  And what order are the sublists in?  It looks like the “1st Unique” sublist is in the same order as the “A” list, with “2” and “4” after the two-digit numbers.  But why is “4” before “5”?  Why is “1” before “3” in the “2st Unique” sublist? … (Cont’d)
    – Scott - Слава Україні
    Commented Jun 6, 2017 at 3:30
  • P.S. Number 2 (the one after the first one) is called second and abbreviated “2nd”, and #3 is third (“3rd”).
    – Scott - Слава Україні
    Commented Jun 6, 2017 at 3:30

1 Answer 1

Reset to default
0

On cell B2, type this array formula (Ctrl+Shift+Enter):

=INDEX($A$2:$A$18,MATCH(0,COUNTIF($B$1:B18,$A$2:$A$18),0))

and copy it to the rest of the column. This will create a list with no duplicates on column B. To simplify, name this array list. Then, use this formula on column C:

=IF(COUNTIF($A$2:$A$20,list)>=2,list,"")

This creates the list with only the values that appear 2 times or more. Copy this formula to other columns and change the 2 to 3, 4 etc to create a list with the values that appear only 3, 4 etc or more times.

The final result should look like this:

enter image description here

Improve this answer
11
  • thank you very much, with these I don't know to try, I will send one of demo file, and if you can put the formula and I'll check that, I want to sort by unique "domain" colon and all from that colon to sort all of the contacts, as you did above, Hope you understand I don't need only one category I need all of them but if we sort by unique domain,1st unique than 2 unique under 1st one. So later I can filter them to show all of them, (all colons) Hope It's clear File - > drive.google.com/file/d/0ByNA7ShiFE7LRUp2anhPSHZKVHc/… Thank you
    – Edin Neziri
    Commented May 30, 2017 at 17:04
  • Actually, what you are asking is much simpler than the question you posted. You can just add filters to your table and sort them, first by the Domain column and then sort the Name columns
    – Thales
    Commented May 30, 2017 at 17:24
  • please can you help me that file to send me back if you can sort, I don't understand you, when you say that is much simpler, I'm trying more than a week to find a way, can you add me on fb.com/edin.neziri, I will be very shortly, please, thank you
    – Edin Neziri
    Commented May 30, 2017 at 18:07
  • Check this link and see how sorting data works. All you have to do is sort first by the "Domain" column and then by the "Name" column.
    – Thales
    Commented May 30, 2017 at 18:47
  • thank you for helping me, I put the filter table and now my question is how to filter the "domain" colon and then colon name? by A-Z or how? 1st the "domain"? then the "name"?
    – Edin Neziri
    Commented May 30, 2017 at 19:12

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .