Random Pair Generator Explanation

By Columns
B: dog names (it is assumed that these are not duplicated, add 0,1,2,.../whatever to make this assumption true)
C: dog gender
D: handler names
E: paired dog name from K
F: random number aka bingo ball

H: dog names from B re-written in the order of F
I: the gender of H
J: the handler of H
K: the first dog in H with the same gender from I and a different handler in J but hasn't been used previously in K

Notes on K:
These are pairs because of the "first" property of the lookup, the first with a different handler prevents the dog from matching itself as well as another dog by the same handler; then later on when you get to that dog in the list its "first" which hasn't previously been used is the dog to which paired with it earlier on in the list.

When only f gender dogs can match with f dogs, and you have three of them, then one will always be #N/A; pair her with the #N/A m dog. Similar situations can happen by handler name as well depending on who gets paired first(just recalculate the spreadsheet to get a new order until this problem goes away).

One resource to use is the Evaluate Formula feature on the Formulas tab.
Let us know if you have questions about any of the steps in the evaluation of the formula.

Formula:

Please Login or Register  to view this content.

The random part is in F and H
F is the pick a random number, which H can then sort smallest to largest.

The INDEX() function does basically what its name suggests, it gives you back the item in a list or a table that you asked for.
Index(ListOfStuff,15) gives you the 15th item from ListOfStuff
Index(TableOfStuff,14,2) gives you the 14th item in the 2nd column from TableOfStuff

The MATCH() function finds what you're looking for and tells you where it is in the list
Match(FindThis,ListOfStuff,0) will tell you the Nth spot in ListOfStuff where it found FindThis (note we used 0 so that we get an #N/A when it isn't in the list)

putting these two together
Index(ListOfStuff,Match(FindThis,ListOfStuff,0),AnotherColumn) will give you the adjacent data in AnotherColumn when you don't know where FindThis is in the list.

so for the H formula it's a little trickier because the two lists are different and there's two more players SMALL() and ROW()

ROW(address) gives you the row number of the first cell in address
Row(1:1) is the row number for the whole row of row one aka 1
likewise Row(2:2) is 2, you'd get the same row number with A2, but 2:2 is probably a little more monkey proof.

SMALL(ListOfStuff,N) is a cool one that I had to lookup, it gives you the Nth smallest one in the list
Small(ListOfStuff,Row(1:1)) gives you the smallest in the ListOfStuff
likewise Small(ListOfStuff,Row(2:2)) gives you the second smallest

pulling it togeather
Index(ListOfNames,Match(Small(ListOfRandom,Row(N:N)),ListOfRandom,0)) would give you the entry from ListOfNames which aligns with the Nth smallest entry in ListOfRandom

That being said taking a look at the other use of Index/Match as in seen in K

Formula:

Please Login or Register  to view this content.

the tricky part here is you have something like
Index(ListOfNames,Match(FindAOne,ListOfOnesAndZeros,0))
where the ListOfOnesAndZeros are true/false statements based on:
(same gender) and (different handler) and (hasn't been used yet)
evaluated for each item in the list, then you match to the first One aka true entry to get the Nth match that fits the criteria, then index into the ListOfNames to get the name.