Combinations to ignore blanks (combination match issue based on random variables)

[Excel_Query]

I am trying to solve combination query, based on specific criteria
Issue #
#1. The formula in Cell C15, is to choose random number based on Criteria A in cell P4 and W4, For example in (Table 1)if Cell "P4" (value A) = Column "W" (value A) give me a random value from Table B in this instance the values are (3,5), my question ~ is it possible to change values in the below formula highlighted in bold automatically, meaning instead of (1,2,3,4,5) give me only (3,5) and ignore blank cells (note these are not static numbers, they will change based on the sets below in "green", hence they need to be dynamic).

The same formula will apply to rest of the cells to be dynamic. I have been trying this and have asked quite a few people and no one has the solution, I am hoping to find an answer here. I hope the above explanation is clear, in case my apologies, please ask me to reiterate the question, I will do so. Thank you in advance

=IF(P4=$W$4,CHOOSE(RANDBETWEEN(1,COUNTIF($C$4:$M$11,$W$4)),B]1,2,3,4,5[/B]),IF(P4=$W$5,CHOOSE(RANDBETWEEN(1,COUNTIF($C$4:$M$11,$W$5)),6,7,8,9,10,11),IF(P4=$W$6,CHOOSE(RANDBETWEEN(1,COUNTIF($C$4:$M$11,$W$6)),12,13,14,15,16),IF(P4=$W$7,CHOOSE(RANDBETWEEN(1,COUNTIF($C$4:$M$11,$W$7)),17,18,19,20,21,22),IF(P4=$W$8,CHOOSE(RANDBETWEEN(1,COUNTIF($C$4:$M$11,$W$8)),23,24,25,26,27),IF(P4=$W$9,CHOOSE(RANDBETWEEN(1,COUNTIF($C$4:$M$11,$W$9)),28,29,30,31,32,33),IF(P4=$W$10,CHOOSE(RANDBETWEEN(1,COUNTIF($C$4:$M$11,$W$10)),34,35,36,37,38),IF(P4=$W$11,CHOOSE(RANDBETWEEN(1,COUNTIF($C$4:$M$11,$W$11)),39,40,41,42,43,44),0))))))))

I also have #NUM error and duplication of numbers in the same row issue (highlighted in gold) which is the later part of the query.

Thank you in advance.

[XLent]

I'll be honest and say I'm struggling to follow it, and I think the duplicate piece will make more complex etc but, in terms of the basic ask, the below:

Please Login or Register  to view this content.

would generate an INDEX of valid #s, e.g. {3;5} for C15/P4, {15;16;17} for D15/Q4 etc, and the RANDBETWEEN would pick a random item from that array of numbers

hopefully that helps (a little) but, I suspect, I have misunderstood

[Excel_Query]

Thank so much Xlent, you have been a great help. I guess Column A:A needs to be fixes correct, if I drag the formula?

#2. Duplication - I wanted to avoid duplicates value in the same row
#3. Is there a fix for #NUM error, is it possible to have any other number from the list that is not duplicated in place of #NUM error

I know this is quite complicated, however I cannot thank you enough for your help thus far.

[XLent]

I guess Column A:A needs to be fixes correct, if I drag the formula?

the A:A and A$1 references can be left as-is -- these are just being used (w/ROW) to build a dynamic array of 1 to n, to be used as k-array within the AGGREGATE call -- i.e. these references are completely agnostic of your data.

#2. Duplication - I wanted to avoid duplicates value in the same row

if I've understood, I think the below would address that?

Please Login or Register  to view this content.

the above, as it is copied across, removes from the 'possible' array any # already used and, adjusts the boundary of the RANDBETWEEN accordingly
so, in other words, the 2nd instance of a given condition in a row would see the upper threshold of the RANDBETWEEN reduced by 1 to account for fact one number must already be assigned, and which has been removed from the array

#3. Is there a fix for #NUM error, is it possible to have any other number from the list that is not duplicated in place of #NUM error

this one you might need to elaborate on, I'm afraid.
Would limiting the "any" number to those assigned to conditions NOT in use on that row work? using "any" unused number (irrespective of whether or not the condition is active on the row) is significantly more complicated

on a final aside, I would advise removing your CF rules on that matrix and replacing with one single rule -- with C15 the active cell, apply a CF rule (with same format) of:

=(COUNTIF($C15:$H15,C15)>1)

and apply to C15:H64

[Excel_Query]

Thank You Xlent, for your prompt reply, I have attached the file with the second formula, still there are duplicates, not sure If I have done it right?

Third - #NUM error, I am not sure if I follow correctly, I guess I will have to sort them out and use the one's with no errors. However if the duplicates are sorted, I guess I have achieved a milestone with your generous help.

[XLent]

so, the formula in my prior post should be pasted into C15 (note: not an Array), and then copied across the matrix C15:H64 -- without modification.
at that point, all dupes should disappear bar the #NUM!

re: #NUM! -- what I'm saying is, these will only appear where you have fewer available numbers (re: AB:AC) than the times a condition is being used on a row

I believe you want to assign a number to these, rather than #NUM!, so I'm suggesting you isolate the numbers (re: AB:AC) that are assigned to condition codes that you are not referencing in that particular row

Consider: 3 instances of "E" in Cols F:H and an "F" in Col C
if you don't restrict which numbers to use as an alternate for "F" (given it has none in AB:AC) you could, theoretically, assign one of the # assigned to "E" thus rendering the last "E" bereft of it's own number...
so, to avoid the recursive nature, it would be simplest to say - for #NUM! I cannot use any number that belongs to a condition that I am referencing on this row... as they must be ring-fenced

Hope that makes sense.

[Excel_Query]

Thank you once again, I have attached as you mentioned I have copied and pasted C15:H64 without any modifications, still duplicates are there, are you able to attach a version 3 of this file with the formula, please?

Re #NUM - I got What you mean, I should replace the Alphabets that are missing, that's perfect I can do that. Thank you for being patient.

[XLent]

See if the attached version does what you need -- inclusive of the #NUM! logic (assigns first available) -- note I've not been able to really spot check results so will need you to post back with anomalies etc.

Please Login or Register  to view this content.

there are some other changes in here, from previous iteration, to try and accommodate both valid/invalid scenarios, but also to simplify - a little.

[Excel_Query]

You are simply genius, thank you so much! Please let me know if I could be of any help in return.

[XLent]

You're welcome -- there are probably more elegant approaches but glad it worked.

In terms of help -- well, boards such as this need contributors - so if you see some questions you can help with, get stuck in...

[Bo_Ry]

Not sure if I get this right.

Please try at C15

Please Login or Register  to view this content.

[Excel_Query]

Sorry for the delay in my reply. Yes, the formula works and much simpler too.

Like Xlent said there may be more elegant approaches, which is what I think you delivered.

Thank you so much!