UDF to List Unique Values Unsorted within a table

[stu40]

Hi,

I have been using this UDF to get a list of sorted unique values from a field BATCH in a table TRANSACTIONS in a separate sheet -

Please Login or Register  to view this content.

In a separate sheet, I just copy this formula =FilterUniqueSort(Transactions[Batch],ROW(AD1)) down as many rows as necessary, and because unlike most UDFs, the formula doesn't need to be pasted inside an array, so I can use the results as another table (array formulas don't work in tables). It's been working great for the last 10 months, but if there are more than a few thousand records, it can be quite slow.

I want to speed up the processing by removing the sorting function, and I have tried to edit the VBA so it no longer sorts, but with no success. Does someone know how to remove the sorting function from the VBA? I would be really appreciative. I spent 3 hours last week trawling google (including this forum) for alternate code, but with no success.

Thanks in advance for your assistance,
Stuart

[jindon]

That might be the code from

http://www.ozgrid.com/forum/showthread.php?t=159113

If no need to sort then change to

Please Login or Register  to view this content.

[YasserKhalil]

May be

Please Login or Register  to view this content.

[jindon]

YasserKhalil

This is the second time you did such thing without referring to the original code.

That will never speed up the process. Don't do it according to your poor GUESS.

[YasserKhalil]

Mr. Jindon
Sorry for that but I just like to offer any help and I don't need to speed up process ..
It is just I was helped in the forum so I like to offer help to others .
Another point .. I already found your UDF function in my library so I posted it as a kind of help
As for the original code ..in fact I don't keep names I just keep the codes in my wallet
Sorry again if I disturbed you

[jindon]

I want to speed up the processing by removing the sorting function, and I have tried to edit the VBA so it no longer sorts, but with no success.

And the code was not posted in this forum.

[stu40]

Hi Jindon,
Thanks for your reply. Your code did more than double the processing speed, but unfortunately it outputs duplicates (ie. not just the unique values). Do you have any other ideas?

Hi YasserKhalil,
Thank you for your reply too, but your code results in an output of #NAME? down the column.

Kind regards,
Stuart

[jindon]

OOps

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

[jindon]

And let's see if this is faster

Please Login or Register  to view this content.

[stu40]

Hi Jindon,

That new code you posted is almost 4 times faster than my original code, and it only displays unique values unsorted. I really appreciate your assistance!

Thank you,
Stuart