Randomly pair values exclusively but only if one is less than the other

[kbrownk]

Hi,

I have two columns of data, Col A and Col B. I want to randomly pair them exclusively so I can subtract A from B. But I only want to pair values where A is less than B. Any ideas?

I can get as far as randomly pairing exclusively, though making sure A is less than B I can't figure out. So far, I assign a rand() value to all A values, then I rank those values in another column. I do the same for Col B. Then I just pair those w/ identical rank values.

I was toying w/ multiplying the rand() value by the actual value in Col A and likewise for B, and then ranking them, or perhaps using randbetween(0,givenvalue) but of course these ideas aren't complete and probably bias the results to not being random.

Note that I do need the pairings to be exclusive (1 Col A value per 1 Col B value).

I'm also clueless w/ VBA, though I'm willing to give it a shot if it's the only way.

Thanks,
kbrownk
Excel 2007

 

[DebugGalpin]

Can you use an IF formula?

=If(A1<B1,B1-A1,0)

So if A1 is less than B1, if true then B1 minus A1, if false then 0

Obviously you can change the 0 to "No" or whatever you like?

 

[DebugGalpin]

Formula didn't seem to come up there...

"=IF(A1<B1,B1-A1,0)"

 

[DebugGalpin]

Formula didn't seem to come up there...


"=IF(A1<B1,B1-A1,0)"[ p QUOTE]<>
Or there not sure what is going on there

*****IF(A1<B1,B1-A1,0)*****

 

[DebugGalpin]

If
(A1 < B1,
B1 - A1,
0)

 

[kbrownk]

The problem is, the if() would have to be iterative until it found a value in Col A that could return a non-negative value when being subtracted from the given value in Col B. That is, the if(A<B1, A-B1, otherwise 0, would return a 0 if the condition is not met, but it needs to keep trying values in Col A until the condition is met.

What I really need is an Excel equivalent to a WHILE loop. Say Col A has male ages and Col B has female ages. Col C has randomly assigned rank values from 1 to =count(A:A), providing random integer values each associated w/ a Col A value simply by being in the same row. Col D has the same sort of ranking system for Col B values.

Then, I need something like (say in Col G, Row 1, but the same formula would be used for all Col G rows): find the row in Col C that equals D1. If Col A in the same row that has the value found in Col C is < B1, then subtract it from B1 (i.e. condition has been met). Otherwise...what? The only Col C row whose value = D1 has been tried and failed, so no other rows will be found to check.

I could increase all values by 1 in Col C and Col D, except for D1, if the condition above is not met. This way, a new rank Col C would equal D1. But this must be done iteratively. Is there an equivalent to a WHILE loop in Excel?

BTW, increasing by 1 after each fail would be imperfect since all Col C values that start out > D1 will never get checked, but I could account for this by resetting (i.e. decrease equally) all rank values in Col C and D such that D1 is the largest to begin with. Of course this doesn't solve my iteration issue (1000s of iterations may be required).

Thanks,
kbrownk