Turn macro into UDF with variable range

[Legacy 505880]

Hello. Lately i've been trying to set up an udf which pulls numbers from indipendent lists so that all pulled numbers are different. An example might be A:[1,3], B:[3], C:[5,6,2], D:[1,2] and E[3,4,5,6]. a possible solution could be [1,3,5,2,6], or [1,3,6,2,4]. There can be more or less than 5 lists, this is just an example. Note that there are dependancies between lists. For example, 3 can't be pulled from A because otherwise it won't be possible to pull a number from B. So far i found a couple functions that fit my needs, while also being fairly fast, considering this can be a really long task for a machine. However, these are macros and i'd need them to be udfs for a series of reasons. first, because i need the function to recalculate whenever i press f9 on the spreadsheet, i don't want the solution to stay the same. I also need to change the range whenever i want to (in the case of the example i gave you, the udf input would be A1:E4). The third reason is that i need the solution to be an array with each number in a different cell. In the second macro i will give you, the code also returns the amount of seconds it takes to calculate, which is completely irrelevant to me, so you can remove it.

EXAMPLE, second VBA.xlsm and 1 more file

2 files sent via WeTransfer, the simplest way to send your files around the world

we.tl

Thank you in advance, feel free to ask me questions for more clarity on the problem

 

[Fluff]

Duplicate to: Pull distinct number from independant columns

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.