UDF for removing duplicate ignoring blank row

[melvinkoshy]

I have data from A1 to A15 in a sheet. I wish to create a user defined function with name and syntax as Unqval(data_range).
data_range is the range where the values are present

eg: If I use the formula in D1 as Unqval(A1:A15), it should return unique values of data from cell D1 downwards by checking, A1 to A15. It should ignore blank rows and also sort in descending order. The illustration is as shown

I know how to get the unique values by formula which is as follows.

Please Login or Register  to view this content.

But, the data returned in Col. D cannot be used further. Also, it does not ignore blank rows also. Hence, I desire a VBA solution.

[Kaper]

50 posts and still posting screenshot only? No workboiok as attachment?

So let me paste one of our canned replies:
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[melvinkoshy]

Workbook attached.

[Kaper]

Have a lok on such code:

Please Login or Register  to view this content.

Usage:

Select enough or more cells than needed for output (for instance F2:F10 - see what happens when too few are selected - orange cells)
With these few cells selected (not just one) write formula

Please Login or Register  to view this content.

and commit as array formula - confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[melvinkoshy]

This is much complex than I thought.

As Unqval is an array function, I am not able to use the output data for further processing. Also, selection of the output data range is cumbersome. How can we get the data type of the original data preserved in the final sorted output? Also, would it be better to modify the UDF as Unqval(datainput_range, dataoutput_range) so that the exercise of entering the formula as array can be avoided.

[Kaper]

UDF only returns value in cells it is called, so no way to do Unqval(datainput_range, dataoutput_range) working as you expect. :-(

Of course you can do it in VBA procedure, but works as standar excel function.

So you may try:

Please Login or Register  to view this content.

[Kaper]

Coming back to UDF concept:

How about regular (not array) formula in worksheet in F2:

Please Login or Register  to view this content.

to be copied down.
Second argument is range with already listed values so if there is nothing in F1 (or header, which is not in columnA it returns Highest value in A1:A15. Copied down to F3 it becomes

Please Login or Register  to view this content.

so lists second highest item (first is listed in F2) etc?

The code for such UDF is:

Please Login or Register  to view this content.