Pull distinct number from independant columns

[Legacy 505880]

Hello everybody. These days I've been trying to find a function that can pull a number from each column in a selected range so that all pulled numbers are different.
For example, if the Starting columns are A:[1,3,5], B[1,2,3], C[4,6], D[1], a possible solution could be [3,2,6,1], or [5,2,4,1]. Note that there are dependancies between columns: for example, 1 can't be pulled from column A because there would be no numbers to pull in D. So far the best answer i could get is to pull numbers randomly until you get a valid solution. I was wondering if there was a way to not use this recursive method, but a more deterministic approach, to always get distinct numbers (if not impossible).

 

[Legacy 143009]

Yes, it will produce results only the selected range. If you want one row below, you have to select from K2 to T2 and repeat the process. Also locking the referances may help =getRandom($A$1:$J$100)

 

[Legacy 505880]

I don't think i understand your answer. My question was related to this weird series of ones below the solution's row, and i wanted to know if there is a way to get rid of it

 

[Legacy 143009]

Hmm.. Now I get it. Try to chage this line:

results = Array(resultArr.keys, resultArr.items)

Like tihs:

results = Array(resultArr.keys)

 

[Legacy 143009]

I think your excel version supports array formulas. I think you don't have to select all range. Just write into one cell and it will spill results to all cells. Also you don't have to press Ctrl+Shift+Enter, I guess.

 

[Legacy 143009]

According to this post, you don't have to add Application.Volatile. Ctrl+Alt+F9 Should refresh UDF function.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Select different numbers from indipendent lists efficiently
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.