IF statement to evaluate result of random number generator to generate next random number

I need to scan for random account numbers through a sheet containing account numbers and their details. I have used the following formula to do this:

Formula:

Please Login or Register  to view this content.

The catch is that column A of the sheet, which contains the account numbers, also contains blank cells between account numbers, and the account numbers are not evenly spaced from each other, and I require the formula to return only account numbers and not blank cells.

My solution to this has been to try to evaluate the result of the INDEX-RANDBETWEEN formula above using an IF statement in order to decide whether I want to try run the formula again so that I can get a different result.

I have used:

Formula:

Please Login or Register  to view this content.

In the THEN condition of the IF statement I changed the RANDBETWEEN range to start on the third row to try and "force" a new random process from occuring (not sure if this is necessary). I have gone on to nest this process as far as probabilistically sufficient in my mind but the formula seems still to return a disproportionate number of blank results, leading me to believe that nesting these functions like this does not force Excel to try a different random cell on the RETRENCHMENT sheet.

My end result of nesting was this:

Formula:

Please Login or Register  to view this content.

Is there a more efficient way (and that works) of letting Excel perform a random number generation, having it evaluate the result of this process and then based on this result either return the result or re-run the random number generation to return a different result?

When you say:

return a different result?

Does that mean you don't want the same random number to be generated more than once?

You could make a list of the row numbers that actually contain data and then get a random row number from this new list. However, getting duplicate random numbers is likely (just about guaranteed!)

Sound like something you'd want to do?

There are ways to generate unique random numbers but it's extremely complicated.

I'm not sure I follow you on the "constantly updating" aspect.

If the raw data is constantly updating then so will the formulas that refer to the raw data.

Maybe you need a VBA procedure to do what you want?

Thanks. By constantly updating I mean that a colleague enters data in the other sheet on a near daily basis.

I have found a semi-solution that never returns blank values, only account numbers, although it seems to be not entirely random (I still can't figure out why). Luckily it is not required to be 100% random, but it still would be best if it were.

I am using the following formula now:

Formula:

Please Login or Register  to view this content.

I'd wager that the reason this formula does not return truly random account numbers from the RETRENCHMENT sheet is that my MATCH function is set to find the largest account number that is less than or equal to the randomly generated pseudo account number, and that the account numbers are not sorted in ascending order in the RETRENCHMENT sheet, although the exact logic behind such a conclusion still escapes me.

NOTE: I have also discovered that I misunderstood the RANDBETWEEN() function - using

Formula:

Please Login or Register  to view this content.

will show quickly that a different random process is used for every instance of the RANDBETWEEN() function.

Originally Posted by captive

Thanks. By constantly updating I mean that a colleague enters data in the other sheet on a near daily basis.

I thought that maybe you had something like a dynamic data exchange (DDE) link that updated every second or so. Like a DDE link to a stock exchange. Now that would be updating constantly!

I have also discovered that I misunderstood the RANDBETWEEN() function - using

=RANDBETWEEN(0,10)&"-"&RANDBETWEEN(0,10)

will show quickly that a different random process is used for every instance of the RANDBETWEEN() function.

Yes, that is correct.

The only thing I can think of that will do what you want is as I already mentioned, extract the row numbers to a contiguous range of cells and then use a fromula to get one of those row numbers in random order.