Results 1 to 10 of 10
Thread: Extract every n row (2002 SP3)

20040908, 15:40 #1
 Join Date
 Mar 2004
 Location
 Gulfport, Florida, USA
 Posts
 51
 Thanks
 0
 Thanked 0 Times in 0 Posts
Extract every n row (2002 SP3)
User has an excel file with about 7,000 emails. He wants to pull every 7th row to make up a random mailing of about 1,000. What function would I use to do this?

20040908, 15:59 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Extract every n row (2002 SP3)
Something like this perhaps?
dim x as long
for x = 1 to 1000 step 7
'do what you want with each row
next
(you weren't very specific about what you wanted to do)
Steve

20040908, 16:09 #3
 Join Date
 Mar 2004
 Location
 Gulfport, Florida, USA
 Posts
 51
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Extract every n row (2002 SP3)
Thanks, Steve. What I want to do is to create a new column pulling out every 7th email address. I thought there might be a function that would accomplish that. I'm totally lost when it comes to VBA.

20040908, 16:11 #4
 Join Date
 Jan 2001
 Posts
 3,788
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Extract every n row (2002 SP3)
Hi Sandy
One method is to use an extra column. In the top cell of this column enter the number 1 and in the cell below it enter the following formula, replacing A1 with the cell reference for the cell with 1 in it..
<code>=IF(A1=7,1,A1+1)</code>
Copy this formula down the column so that each of the 7000 rows will now have a number from 1 to 7 in it. You can then use autofilter on this column to display every 7th row, enabling you to copy every 7th email address.
I assume you are not spamming.

20040908, 16:17 #5
 Join Date
 Mar 2004
 Location
 Gulfport, Florida, USA
 Posts
 51
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Extract every n row (2002 SP3)
Thanks, Tony,
This works perfectly. And, of course, we would NEVER spam!

20040908, 17:10 #6
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Extract every n row (2002 SP3)
The formula below assumes that the email addresses start in cell A1. If that is not correct, then the formula will need to be modified. Go to an empty column and enter the formula below in cell 1 in that column. Then copy it down for as many email addresses as you want.
<pre>=INDIRECT("A"&IF(ROW()=1,1,(ROW()1)*7))
</pre>
If, instead of selecting every 7th adderss, you want to select 1,000 random addresses from the list, then a minor modification of the code in <!post=This Thread,53961>This Thread<!/post> should do that for you.Legare Coleman

20040908, 17:33 #7
 Join Date
 Mar 2004
 Location
 Gulfport, Florida, USA
 Posts
 51
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Extract every n row (2002 SP3)
Thanks, Legare,
I tried it and it works, of course; now I'm trying to figure out why. I checked the help for the Indirect function and I understand that putting "A" in quotes will make it a constant. I also understand the first part of the If statement, but I can't grasp the second part. BTW, I didn't know about using the & character.

20040908, 20:23 #8
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Extract every n row (2002 SP3)
A possible setup with a small example...
Let A3:A13 house email addresses (excluding labels).
In B3 enter & copy down:
=RAND()
Select B3:B13, run EditCopy, run EditPaste SpecialValues. This procedure gives you a fixed/nonvolatile set of random numbers.
In C3 enter & copy down:
=RANK(B3,$B$3:$B$13)
In E1 enter the desired number of emails, say, 6 (1000 in your case).
In E3 enter & copy down:
=IF(ROW()ROW($E$3)+1<=$E$1,INDEX($A$3:$A$13,MATCH(ROW()ROW($E$3)+1,$C$3:$C$13,0)),"")
The range in E will now house a random sample of emails of size E1.Microsoft MVP  Excel

20040908, 21:09 #9
 Join Date
 Jan 2004
 Location
 Birmingham, West Midlands, United Kingdom
 Posts
 219
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Extract every n row (2002 SP3)
Yet another approach, which can be generalised to every nth row:
Create 2 new columns to the left of your list.
Number the rows.
Put the value of n somewhere handy.
Use a formula like
=(B2/$H$2=INT(B2/$H$2)) to find which row numbers are exactly divisible by n
Use autofilter to find for which rows this formula returns the value TRUE.
Example attached. With a bit of conditional formatting to make it pretty.

20040908, 21:09 #10
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Extract every n row (2002 SP3)
The IF statement looks at the current row number. If the current row number is 1 then it returns 1. If the current row number is anything else, it returns the current row number minus 1 times 7, or 7 for row 2, 14 for row 3, 21 for row 4, etc. That number is concatenated with the "A" to form cell addresses A1, A7, A14, A21, etc. The Indirect uses that cell address to retrieve the email address.
I actually just realized that every seventh cell would be A1, A8, A15, A22, etc. So the formula could be simplified to:
<pre>=INDIRECT("A"&((ROW()1)*7+1))
</pre>
Legare Coleman