Results 1 to 10 of 10
  1. #1
    Star Lounger
    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?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    Star Lounger
    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.

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

  5. #5
    Star Lounger
    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!

  6. #6
    Uranium Lounger
    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

  7. #7
    Star Lounger
    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.

  8. #8
    3 Star Lounger
    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 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.
    Microsoft MVP - Excel

  9. #9
    3 Star Lounger
    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.

  10. #10
    Uranium Lounger
    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

Posting Permissions

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