Thread: Extract every n row (2002 SP3)

1. 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?

2. 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

3. 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.

4. 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 e-mail address.
I assume you are not spamming.

5. Re: Extract every n row (2002 SP3)

Thanks, Tony,

This works perfectly. And, of course, we would NEVER spam!

6. 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>

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

8. Re: Extract every n row (2002 SP3)

A possible setup with a small example...

Let A3:A13 house e-mail addresses (excluding labels).

In B3 enter & copy down:

=RAND()

Select B3:B13, run Edit|Copy, run Edit|Paste Special|Values. This procedure gives you a fixed/non-volatile set of random numbers.

In C3 enter & copy down:

=RANK(B3,\$B\$3:\$B\$13)

In E1 enter the desired number of e-mails, 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 e-mails of size E1.

9. 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.

10. 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>

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•