Skip Blanks Not Working

[Adam]

I am trying to select every 4th piece of data from a column of 6000. I have
tried copying every fourth into another column and trying to copy and paste
this list using Remove Blanks, but it does not work.

What am I doing wrong, and if this is not the correct way to do this, please
provide some guidance?

Thanks!

 

[Spiky]

I am trying to select every 4th piece of data from a column of 6000. I have
tried copying every fourth into another column and trying to copy and paste
this list using Remove Blanks, but it does not work.

What am I doing wrong, and if this is not the correct way to do this, please
provide some guidance?

Thanks!

Do you need any formulas from the original data? Or would the value be
enough?

Because you could just use an Offset formula to simulate copying just
the value. An example:
=OFFSET($A$1,(ROW()-ROW($A$1))*4,0)

This should work regardless of which row the data starts in. But you
have to put the formula in that same row and adjust the reference
properly. Then copy down for all 1500 rows you need.

 

[Elkar]

The "Skip Blanks" in the Paste Special dialog does not work this way. It
prevents existing data from being copied over by "blanks". It does not
remove blanks.

One solution to your problem may be to use a formula in an adjacent column.
Let's say your data is in column A starting at Row 1. In B1, enter:

=INDIRECT("A"&ROW()*4)

Copy this down column B as far as needed. This will effectively display in
column B the contents of cells A4, A8, A12, A16, A20 etc....

HTH,
Elkar

 

[Shane Devenshire]

Hi,

It is not clear what you are trying to do, but suppose you are trying to
copy every 4th item to a new location.

1. Enter the following formula next to to first row of data
=MOD(ROW(),4)
2. Copy this formula down 6000 rows - note that every 4th item is the same
number
3. With this column of formulas selected Choose Data, Filter, AutoFilter
4. Open the AutoFilter drop down and choose the number that represents the
row with the items you want to select
5. Select your column of data and choose copy
6. Move to a new sheet and choose Paste (no need for skip blanks)

By the way there is no command called Remove Blanks.

If this helps, please click the Yes button

cheers,
Shane Devenshire

 

[krcowen]

Adam

It is not real clear what you want, but, you should be able to modify
one of the following functions to accomplish what you want.

1. With your data starting in row 1; add a new column with the formula
=mod(ROW(),4). Copy this down the 6000 rows and sort by it to get
every fourth row together. Make sure you can get your data back in
the original order.

2. Use the offset function. Again with the data starting in row 1, the
formula =OFFSET($A$1,(ROW()-1)*4+3,0) in row 1, will return every
fourth cell in column A. Use this in as many columns as you need.
Copy one fourth the way down your 6000 rows of data to pick up every
fourth row. You can adjust the second parameter ((row()-1)*4+3 to
account for the row that your data starts on.

Good luck

Ken
Norfolk, Va