If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Skip Blanks Not Working
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! |
#2
|
|||
|
|||
Skip Blanks Not Working
On Jan 5, 3:21 pm, Adam wrote:
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. |
#3
|
|||
|
|||
Skip Blanks Not Working
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 "Adam" wrote: 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! |
#4
|
|||
|
|||
Skip Blanks Not Working
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 "Adam" wrote in message ... 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! |
#5
|
|||
|
|||
Skip Blanks Not Working
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 On Jan 5, 4:21*pm, Adam wrote: 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! |
Thread Tools | |
Display Modes | |
|
|