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
--
NR

So do you need a random selection:


http://www.mcgimpsey.com/excel/udfs/...noreplace.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...




In article ,
Noelline wrote:

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.

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


"Noelline" wrote in message
...
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
--
NR

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,
--
NR


"Bernie Deitrick" wrote:

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


"Noelline" wrote in message
...
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
--
NR

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,
--
NR


"JE McGimpsey" wrote:

So do you need a random selection:


http://www.mcgimpsey.com/excel/udfs/...noreplace.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...




In article ,
Noelline wrote:

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.

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.
--
David Biddulph

"Noelline" wrote in message
...
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,
--
NR


"Bernie Deitrick" wrote:

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


"Noelline" wrote in message
...
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
--
NR

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.


In article ,
Noelline wrote:

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, it worked very well.
--
NR


"Bernie Deitrick" wrote:

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


"Noelline" wrote in message
...
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
--
NR