How to copy non contiguous data from one column and make it contiguous in another column

[asihsimanis]

Dear Good People in MrExcel,

I want to plot a graphic with a non contiguous data, so I figured I should move the data to another place and make them contiguous. The problem is I have around 9000 data in one column. How can I move just row 1,4,7,10, etc (every 3rd row) to another column? Is there a way to do that without VBA? if not, what should I write in the VBA. The data is a number with up to 10 decimal numbers

 

[AlphaFrog]

This formula would copy every 3rd value from column A.

=INDEX(A:A,ROW(A1)*3-2)

 

[FDibbins]

You can do this through a 2-stage process...

1. in a blank column (say C) enter 1 next to the 1st value.
2. below that, enter this formula in row 1 and copy down...
=IF(MOD(ROW()+1,3)=2,MAX($B$1:B1)+1,"")
3. create a 2nd table (I startyed mine in E1) enter 1, 2, 3 etc downwards. This will be used to extract your data
4. In F1, copied down, use this...
=INDEX($A$1:$C$18,MATCH(E1,$C$1:$C$18,0),1)

You should now have a table of every 3rd value from your original table

 

[p45cal]

In the source column, are the cells between the ones you want to copy blank? If so check out F5, Special Cells...
Select the entire column, press keyboard function key F5, Special Cells, then choose constants, then optionally Numbers, Text etc. click OK then copy/paste to the new location.

 

[asihsimanis]

How can I use the same formula to copy row 2,5,8,11 etc? if I just Change the formula into =INDEX(A:A,ROW(A2)*3-2) it wont give me row 2,5,8, instead it will just give me row 1,4,7, etc again. Thank you soo much for the answer :D

 

[asihsimanis]

This formula would copy every 3rd value from column A.

=INDEX(A:A,ROW(A1)*3-2)

How can I use the same formula to copy row 2,5,8,11 etc? if I just Change the formula into =INDEX(A:A,ROW(A2)*3-2) it wont give me row 2,5,8, instead it will just give me row 1,4,7, etc again.

 

[AlphaFrog]

How can I use the same formula to copy row 2,5,8,11 etc? if I just Change the formula into =INDEX(A:A,ROW(A2)*3-2) it wont give me row 2,5,8, instead it will just give me row 1,4,7, etc again.

Try this...

=INDEX(A:A,ROW(A1)*3-1)

 

[Jonmo1]

This may seem a bit odd compared to AlphaFrog's formula, but it's simpler to adapt to diffferent situations.
=INDEX(A:A,(ROWS(A$1:A1)-1)*3+1)

The *3 represents the interval (every 3 rows)
The +1 represents the row # to begin in.

And I like to use ROWS(A$1:A1) instead of ROW(A1)
Mainly for robustness against insertion of rows.