Randomly fill in rows

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I have ColumnA with values in 11,000+ rows. In ColumnG I have 100 dates. How can
I randomly distribute those 100 dates in ColumnB so that ColumnB has one of the
100 dates for each of the rows in ColumnA? In other words, I want a
corresponding date in ColumnB for each row in ColumnA.

Thanks!

Steve
 
in B1 put the formula

=Index($G$1:$G$100,trunc(rand()*100+1),1)

Drag fill down the column for 11000+ rows.

then select column B and do Edit=>Copy, then Immediately Edit=>PasteSpecial
and select values.

Regards,
Tom Ogilvy
 
Actually, it will error .5% and will return the highest date (date in row
100) only .5% where all other dates will be returned 1%.

Regards,
Tom Ogilvy

Tom Ogilvy said:
Just a head's up, but
This formula will try to do [a little less than 1% of the time. ]

=INDEX($G$1:$G$100,0) which will result in an error value rather than
returning a date

Regards,
Tom Ogilvy


Lance said:
in column b enter
=INDEX($G$1:$G$100,ROUND(RAND()*100,0))

Lance
Click to expand...
Click to expand...
 
I stand corrected
Lance
-----Original Message-----
Just a head's up, but
This formula will try to do [a little less than 1% of the time. ]

=INDEX($G$1:$G$100,0) which will result in an error value rather than
returning a date

Regards,
Tom Ogilvy


Lance said:
in column b enter
=INDEX($G$1:$G$100,ROUND(RAND()*100,0))

Lance ColumnG I
have 100 dates. How can that
ColumnB has one of the
Click to expand...


.
Click to expand...
 
Lance,

Thanks for taking the time to respond!!

Steve


Lance said:
I stand corrected
Lance
-----Original Message-----
Just a head's up, but
This formula will try to do [a little less than 1% of the time. ]

=INDEX($G$1:$G$100,0) which will result in an error value rather than
returning a date

Regards,
Tom Ogilvy


Lance said:
in column b enter
=INDEX($G$1:$G$100,ROUND(RAND()*100,0))

Lance
-----Original Message-----
I have ColumnA with values in 11,000+ rows. In ColumnG I
have 100 dates. How can
I randomly distribute those 100 dates in ColumnB so that
ColumnB has one of the
100 dates for each of the rows in ColumnA? In other
words, I want a
corresponding date in ColumnB for each row in ColumnA.

Thanks!

Steve


.
Click to expand...


.
Click to expand...
Click to expand...
 
Tom,

Thanks for the help!!

Steve


Tom Ogilvy said:
in B1 put the formula

=Index($G$1:$G$100,trunc(rand()*100+1),1)

Drag fill down the column for 11000+ rows.

then select column B and do Edit=>Copy, then Immediately Edit=>PasteSpecial
and select values.

Regards,
Tom Ogilvy
Click to expand...
 
Back
Top