Get unique values sorted

[YasserKhalil]

Hello everybody
I have an ttachment which contains a UDF function that extracts non blank values
The results in Result sheet
I want to edit that function to be able to get unique values and to be sorted ..

Note : I want to deal with the whole rows ..I mean if in A1-B1-C1 (11 - 111 - 1111) and in A19-B19-C19 (11 - 111 - 1111) so the both rows are the same .. when getting unique values one of the rows are to be deleted

The sort should be based on Column A

[jindon]

UDF

Use in cell like
=IFERROR(UniqNonBlank($A$1:$C$19,ROW(A1),COLUMN(A1)),"")
then copy to the right and down (non array formula, see attached)

Please Login or Register  to view this content.

[Vikas_Gautam]

Here is the code you are looking for.
I have used a totally different approach i.e. the inbuilt AdvancedFilter Object

Please Login or Register  to view this content.

Check the attached file for further instruction.

[karedog]

Hi Yasser,

To get unique, non blank, non error items is an easy job.
The problem is since Excel VBA doesn't provide internal sorting mechanism inside a function (I mean inside function, not inside procedure like Range.Sort()), so we must provide the sorting function by ourself.
Here, I give 2 alternatives sorting method : Bubble Sort and QuickSort (QuickSort code provided by CPearson)

Bubble Sort :
- Very small code
- Work faster for small data (about 1 to 100 data will be good)
- Work faster if the data is has already/almost sorted

Quick Sort :
- Big code
- Work faster for huge data (over 10.000 data)
- Work faster for unsorted data

You can read them here :
http://www.quora.com/Which-is-faster...or-bubble-sort
http://vinayakgarg.wordpress.com/201...d-bubble-sort/


So I give you 2 functions :
- UniqueNonblankSorted() --> using bubble sort
- UniqueNonblankQSorted() --> using quick sort (QSort code by CPearson)

Please look at the attached file.
The long CPearson's QSort Code is located at module CPearson_QSort, so if you want to use the UniqueNonblankQSorted() function in another project, you must also include this module to your project.

Regards

[Vikas_Gautam]

I forgot consider the sort request in the Post #3, but I have now.
Here is the code:-

Please Login or Register  to view this content.

Check the attached file:-

[jindon]

I want to edit that function to be able to get unique values and to be sorted ..

The sort should be based on Column A

Use in cell like
=IFERROR(UniqNonBlank($A$1:$C$18,1,ROW(A1),COLUMN(A1)),"")
then copy right and down.
2nd argument, 1, is the key column for sort.

Please Login or Register  to view this content.

[YasserKhalil]

Thanks a lot everybody
All the solutions are very good
But I liked Jindon's last post it's fantastic ..
Just the sort process : How does it work? I tried the argument you told but I didn't notice sort at row 8 and 9 in results?

[jindon]

Correction....
Code should be

Please Login or Register  to view this content.

[jindon]

After revision...
=UniqNonBlank($A$1:$C$19,1,ROW(A1),COLUMN(A1),1)
Where 2nd argument is for Sort key col
Last argument for sort order, 1 for ascending, 0 for descending.

Please Login or Register  to view this content.

[YasserKhalil]

thanks a lot Mr. jindon
You are perfect