Remove duplicates and sort array

[abousetta]

Hi,

I have a one dimensional array and I want to remove all duplicates from the array so that each value in the array is unique. Then I need to sort all the 'unique' values in the array. What is the most efficient way to accomplish these tasks?

Thanks.

abousetta

[Richard Buttrey]

Hi,

Probably the simplest way is to use Data Filter Advanced Unique and copy to another range.
Then sort the unique range.
Delete your original array and cut and paste the unique range back to the top of the original array.

Regards
Regards

[abousetta]

Hi Richard,

Sorry I should have been more clear about the source of my array. It is a string converted into an array, actually two strings.

Please Login or Register  to view this content.

What I am doing is comparing the two strings for similarities but there is no reason to go through repeated words since they will either be found or not.

abousetta

[Richard Buttrey]

Hi,

Does Chip Pearson's article http://www.cpearson.com/excel/distinctvalues.aspx help.

I'm sure what you want is in there somewhere.

[Leith Ross]

Hello Ahmed,

I think a better approach would to be to use a Dictionary object. You can easily create a 1-D array of unique values. You could also do this with a Collection object. I prefer the Dictionary object as you can test if an element already exists without generating an error. If you need help coding this, let me know.

[abousetta]

Hi Leith,

Hope all is well. An example would be great. I know that you posted some examples a while back when we were both responding to another thread where the OP wanted to remove duplicates from a large list, but I can't find the link to it.

Thanks for all your help.

abousetta

[Leith Ross]

Hello Ahmed,

Here is an example macro.

Please Login or Register  to view this content.

[abousetta]

Perfect. Thanks Leith. I am trying to go the Array way these days (no pun intended). Arrays are said to be faster and more efficient and I am trying to learn to code using them but still learning the ropes.

abousetta

[abousetta]

Hi Leith,

I had to just make one small change to your code (Key as Variant instead of String), but it works great. Thanks a lot for your help.

By any chance would you know how to solve the second half of the riddle (how to sort the array)? I am still searching but no obvious answer in sight.

abousetta

[snb]

Please Login or Register  to view this content.

[abousetta]

Thanks snb. Unfortunately, I can't seem to get it to work. The array seems to be empty (or contains null values) because even the Msgbox is not showing me anything within the newly created sn array. I will keep testing it and hopefully get it going.

abousetta

[snb]

Something has to be in S1 and S2.
If not than use:

Please Login or Register  to view this content.

[abousetta]

Sorry. Now I know what I was doing wrong. c01 is a string. Therefore I needed to convert it back to an array to see the elements of the array.

I am going to mark this thread as solved for now and work on trying to sort the elements within the array.

Thanks everyone.

abousetta

[snb]

and now the sorting part: resulting in a sorted array 'sp'

Please Login or Register  to view this content.

[abousetta]

Thanks snb. As usual, I will need a big mug of coffee to try and decipher the advanced coding techniques, but I can always say that I am gaining a great amount of experience studying your approaches.

abousetta

[abousetta]

I have modified the code that snb provided to compensate for the nature of the data, but have one final problem that I can't figure out.

Please Login or Register  to view this content.

Now this works fine on small passages (a couple of sentences), but larger paragraphs the results are not accurate with some duplicates still being present and some out of alpha-numeric order. The weird thing is that when I rerun it again, it works just fine giving accurate results. For now I have put in a loop to go through the deduplicate and sort process twice, but this of course is inefficient.

Any thoughts?

abousetta

[nilem]

As an option. There is a NoDups ZVI's function. Here's an example using this function.

Please Login or Register  to view this content.

[abousetta]

Thanks nilem. I will test tomorrow and post back. Just out of curiosity what's a ZVI function (never came across this term before).

[snb]

If this is your goal:

' Remove first (empty) element from Array

you should use
CitationArray = Split(mid(NewString,2), "|")

If you provide the string you are testing with we can have a look too.

[nilem]

... Just out of curiosity what's a ZVI function (never came across this term before).

ZVI - is the nickname of Vladimir Zakharov, superprofessional in programming, MVP Excel

[abousetta]

Thanks nilem. I am unfortunately a fish out of water when it comes to Excel .

In fact, I have heard of ZVI, but haven't ever studied his techniques... maybe its time to start.

snb, I have uploaded a sample of ten rows that show the problem. In the first sheet, I ran the code for deduping and sorting once, while in the second sheet, I ran the code twice. As you can see, the second returns 100% for all three columns since they are all identical. All the code I used is in the upload (didn't strip it down since the problem may be lying somewhere else in the code that I don't see).

Thanks.

abousetta

[abousetta]

nilem, the code works great! It does the deduping and sorting at the same time. I'm impressed

Question though, what does the "&" sign after the variable mean?

Please Login or Register  to view this content.

Thanks again.

abousetta

[nilem]

This is the abbreviation for types of variables:
& means Long
% Integer
# Double
$ String
! Single
And I still give the original code of ZVI

Please Login or Register  to view this content.

There is a faster way to extract unique values with sorting.

[abousetta]

Thanks nilem. I found this great thread and discussion between ZVI and pgc01. I am going to test the two three approaches (Instr, Dict, Collection). From what I have read, the resutls with the Dictionary approach should prove to be the fastest, but I want to see what the differences will be with my data sets.

[DaveU]

Hi nilem,
Thanks for this, just a question about this line:
arr = Intersect(rng.Parent.UsedRange, rng).Value

Why not just:
arr = rng.Value

I must be missing something,

Thanks,

Dave

[jimmalk]

My compliments to everyone on this thread. Great stuff. Thank you

[nilem]

Hi, Dave
You can select the entire column, for example Set rng = Range("A:B"), and with an expression of arr = rng.Value you will have 1,048,576 * 2 elements in your array.
But the expression of arr = Intersect(rng.Parent.UsedRange, rng).Value restricts the selected columns only used range on the sheet.

[mikerickson]

This does both in one routine

Please Login or Register  to view this content.

[DaveU]

Hi, Dave
You can select the entire column, for example Set rng = Range("A:B"), and with an expression of arr = rng.Value you will have 1,048,576 * 2 elements in your array.
But the expression of arr = Intersect(rng.Parent.UsedRange, rng).Value restricts the selected columns only used range on the sheet.

Hi nilem,

Oh yes, of course, I hadn't considered that possibility (entire columns). Thanks for the reply and great explanation.

Regards,

Dave

[abousetta]

Thanks again nilem and Mike. Mike's approach in itself is ingenious as it opens up a whole new approach that I had never thought of, which is having a function keep calling itself (looping) until it completes the task. I used to think this can only be accomplished by a sub calling a function with Do... Loop while approach and Public variables.

I'm going to mark this thread as solved for now and want to thank everyone for the wealth of knowledge in this discussion.

abousetta

[mikerickson]

Thanks again nilem and Mike. Mike's approach in itself is ingenious as it opens up a whole new approach that I had never thought of, which is having a function keep calling itself (looping) until it completes the task. I used to think this can only be accomplished by a sub calling a function with Do... Loop while approach and Public variables.

I'm going to mark this thread as solved for now and want to thank everyone for the wealth of knowledge in this discussion.

abousetta

A function that calls itself is called a "recursive function".

[abousetta]

Thanks Mike. I currently remove duplicates with the scripting dictionary and then sort using one of the standard vb methods (e.g. bubble sort, etc.).

[Kyle123]

Why not use one object to sort and remove duplicates? Consider:

Please Login or Register  to view this content.

[jindon]

See the disadvantage of use of arraylist/sortedlist.

Please Login or Register  to view this content.

[Kyle123]

True, but that's only a limitation of mixed types. For all strings or all numbers, it is perfectly valid - still something to bear in mind

[jindon]

True, but that's only a limitation of mixed types. For all strings or all numbers, it is perfectly valid - still something to bear in mind

That is critical....