more exlusion or inclusion criteria for random match

example2.xlsx

This is my second shot asking this question hopefully I can be more clear this time

in regard to the attached
1) for AS4:AU4, I need to further exclude the possible values based on AR4(=AR2). For whatever random value is in AR, I need each recalculation to not include for example, appliance_1-1 in AR4 means AS4:AU4 can anything OTHER than appliance_1-2,1-3,1-4. If AR4 was Appliance_3-3 then then AS4:AU4 could be anything other than 3-1,3-2, and 3-4.
2) As for AS3:AU3 they can ONLY INCLUDE, for example AR3 is 1-1 then AS3:AU3 has to be any random order of 1-2,1-3,and 1-4
3) as for AS2:AU2, appliance of any number has to be excluded ,but I'd like to not do this by simply changing the look up value and row references since I have a ton of these is a very large spreadsheet.
also, AV:AX are irrelevant in this example, so you can ignore them
Thank you for your time. I could really use some help with this, anything would help

Sorry but I just don't understand what you're getting at. Analysis isn't helped since you have a random factor involved which alters everything every time the sheet changes.

Can I suggest you offer a few examples where the AR2 Random index is fixed at some value and then manually add the results you expect in AR:AU for a number of rows. Do the same with another fixed index in AR2 and list those results too.

Knowing what you expect to see is is enormously helpful.

Results for row 3 and row 4.
For row 2, I afraid that I am not following your idea. Could you please give few desired outputs?

Hi,
Nice to hear it works for row 3 & 4
This attachment is updated for row 2
Hope it helps.

Originally Posted by anxiolydiot

Little bit of tweaking and got everything working. Thanks again for taking time to help me out!

Can you share it?

Nice!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

OK, to help you to expand it in future, I will go step by step:

INDEX($N$2:$N$13,LARGE(IF((COUNTIF($AR$3:AS$3,LEFT($N$2:$N$13,FIND("-",$AR3)-1)&"*")>0)*(COUNTIF($AR$3:AS$3,$N$2:$N$13)=0)=0,"",ROW(INDIRECT("1:"&ROWS($N$2:$N$13)))),RANDBETWEEN(1,COUNT(IF((COUNTIF($AR$3:AS$3,LEFT($N$2:$N$13,FIND("-",$AR3)-1)&"*")>0)*(COUNTIF($AR$3:AS$3,$N$2:$N$13)=0)=0,"",ROW(INDIRECT("1:"&ROWS($N$2:$N$13))))))))

Let start with case row 3, AS3 to AU3 must be same series as AR3.
First, let fix AR3 with 1-2.png, AS3 with 1-4.png. Star working in AT3
Initial though, using INDEX(N2:N13,n), with n is random choice from 1 to 12, but excluding: (A) 5 to 12 (Not appliance*) and (B) 2 and 4 (AR3 and AS3 wass previous chosen)
So, try to create 2 arrays with (A) and (B) criteria, in which 1/0 displays true/false, then multiply them to give a new combi-array (array C with 1/0 follow the two criterias).
1) Range A: COUNTIF($AR$3:AS$3,LEFT($N$2:$N$13,FIND("-",$AR3)-1)&"*")>0
count each item with prefixed "appliance_1" of N2:N13 in AR3:AS3, if >0, read 1, else, read 0.
In formula bar, evaluate range A by highlight it, hit F9:{TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}
with 5-12 is excluded by FALSE.
2) Range B: COUNTIF($AR$3:AS$3,$N$2:$N$13)=0
reads each item in N2:N13 is 1 (if not found in AR3:AS3), is 0 if found.
evaluate = {TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}
you can see 2 and 4 is excluded by FALSE
3) Range A * Range B = (COUNTIF($AR$3:AS$3,LEFT($N$2:$N$13,FIND("-",$AR3)-1)&"*")>0)*(COUNTIF($AR$3:AS$3,$N$2:$N$13)=0)
evaluate = {1;0;1;0;0;0;0;0;0;0;0;0}
4) Inteprete 0 to blank, 1 to oder number: (=IF(A*B=0,"",{1 to 12})
IF((COUNTIF($AR$3:AS$3,LEFT($N$2:$N$13,FIND("-",$AR3)-1)&"*")>0)*(COUNTIF($AR$3:AS$3,$N$2:$N$13)=0)=0,"",ROW(INDIRECT("1:"&ROWS($N$2:$N$13))))
(=IF(A*B=0,"",{1 to 12})
evaluate = {1;"";3;"";"";"";"";"";"";"";"";""}
Initia though, use LARGE(array, randbetween(1,2)), where 2 equals numbers counted in array.
LARGE(IF((COUNTIF($AR$3:AS$3,LEFT($N$2:$N$13,FIND("-",$AR3)-1)&"*")>0)*(COUNTIF($AR$3:AS$3,$N$2:$N$13)=0)=0,"",ROW(INDIRECT("1:"&ROWS($N$2:$N$13)))),RANDBETWEEN(1,COUNT(IF((COUNTIF($AR$3:AS$3,LEFT($N$2:$N$13,FIND("-",$AR3)-1)&"*")>0)*(COUNTIF($AR$3:AS$3,$N$2:$N$13)=0)=0,"",ROW(INDIRECT("1:"&ROWS($N$2:$N$13)))))))
randomly read 1 or 3
Finally, INDEX(N2:N13, LARGE(..) pick unique item randomly.
Hope it is clear enough. Any raising question, do not hesitate to ask here.