Formula

[Guest]

Hello all, I need your help, I am working with large excel data and I need a
formula so that I can generate a random selection every 70 rows, what kind of
formula do I need and how do I integrate it in my worksheet? All answers are
welcome.

Thanks

 

[JE McGimpsey]

So do you need a random selection:


http://www.mcgimpsey.com/excel/udfs/samplenoreplace.html

or a sample every seventy rows? Or a random selection from each 70 rows.

J1: =INDEX($A:$A,(ROW()-1)*70+1)

copy down as far as necessary...

or one sample from each 70 rows:

J1: =INDEX($A:$A,(ROW()-1)*70 + INT(RAND()*70)+1)
copy down...

 

[Bernie Deitrick]

NR,

You could use two columns of formulas. Let's say that you have two blank columns, B and C. In cell
B2, enter the formula

=RAND()

and copy this formula down to match your entire data set.

Then in cell C2, enter the formula

=IF(B2=MAX(OFFSET($B$1,INT((ROW()-ROW($A$2))/70)*70+1,0,70,1)),"Select me","")

and copy that down.

Then apply a filter, and show just the "Select me" values in column C.

If you need to do something with the selection, apply the filter, select the column(s) with the data
that you need, use Edit / Goto.. / Special... "Visible Cells only" "OK" and then copy and paste the
selection somewhere else.

HTH,
Bernie
MS Excel MVP

 

[Guest]

I tried these formulas and I keep getting an error message, how do I fix it.
I have never worked with formulas so all of this is brand new to me.

Thanks for you help,

 

[Guest]

Hi, thank you for your help but I am a real dummy when it comes to formulas
so I cannot get this to work, I keep getting an error message, how do I
correct it?

Thanks again,

 

[David Biddulph]

If we have to guess what the error message is, then you might have to guess
what answer we *would* have given if we had known what the error message
was.

The one bit of advice we can give without knowing what error message you are
getting is please don't try to retype the formula. Copy it from here to the
formula bar. the formula as given shouldn't give an error message. The
only thing that might is if your list separator character is set not to a
comma but to a semi-colon; if that is the case, you can change the commas
in the formulae to semi-colons, or you can change the separator in your
Windows Regional Options.

 

[JE McGimpsey]

I happen to be a real dummy at mind reading... so I'm unsure what error
message you received. Thus I don't have a clue how to correct it.

 

[Guest]

Thanks, it worked very well.
--
NR

NR,

You could use two columns of formulas. Let's say that you have two blank columns, B and C. In cell
B2, enter the formula

=RAND()

and copy this formula down to match your entire data set.

Then in cell C2, enter the formula

=IF(B2=MAX(OFFSET($B$1,INT((ROW()-ROW($A$2))/70)*70+1,0,70,1)),"Select me","")

and copy that down.

Then apply a filter, and show just the "Select me" values in column C.

If you need to do something with the selection, apply the filter, select the column(s) with the data
that you need, use Edit / Goto.. / Special... "Visible Cells only" "OK" and then copy and paste the
selection somewhere else.

HTH,
Bernie
MS Excel MVP