Pick values from one column and organize in another

**jediguran** · 03-31-2009, 10:39 AM

Hello

I have been struggling with this issue for some time and I think it is fairly easy to solve, but I have been unable to find the solution. I have a column of numbers and I want to copy numbers, with a constant periodicity, and paste in a new column. In the example below, I copy values from column A and past in column B, picking only every third value:

A B
1 1
2 4
3 7
4 10
5
6
7
8
9
10

Is there a smart way to do this? Thanks in advance!

**NBVC** · 03-31-2009, 10:46 AM

One way:

=IF(3*ROWS($A$1:A1)-1>ROW($A$10),"",INDEX($A$1:$A$10,3*(ROWS($A$1:A1)-1)+1))

where 3 represents you pattern.. and A1:A10 contains all the range

copied down

**prescient** · 03-31-2009, 11:07 AM

This is definitely not the cleanest but....

Sub EveryThree()

Dim RowA As Integer
Dim RowB As Integer
Dim CellValue As Double
Range("a1").Select
ActiveCell.Offset(0, 1) = ActiveCell.Value
RowA = 1
RowB = 1

Do Until IsEmpty(ActiveCell.Value)
RowA = RowA + 3
RowB = RowB + 1
Range("a" & RowA).Select
CellValue = ActiveCell.Value
Range("b" & RowB).Select
ActiveCell.Value = CellValue
Range("a" & RowA).Select

Loop

Range("b" & RowB).Select
Selection.Clear

End Sub

**NBVC** · 03-31-2009, 11:31 AM

prescient,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**jediguran** · 04-02-2009, 04:39 AM

Thanks for the quick reply!

NBVC: I tried using the formula you posted but I get "The formula you typed contains an error", marking the INDEX function. Any idea why?

/JG

**NBVC** · 04-02-2009, 07:40 AM

It may be the version of Excel you are on....

Try:

=IF(3*ROWS($A$1:A1)-1>ROW($A$10);"";INDEX($A$1:$A$10;3*(ROWS($A$1:A1)-1)+1))

**jediguran** · 04-02-2009, 07:53 AM

Yey that did the trick! Thanks a bunch

/JG

**NBVC** · 04-02-2009, 07:56 AM

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save