Copying data without blank cells, and only certain cells

[Roshario]

I have 3 questions:

1). I receive large data sets from scientists in excel format that'
been transferred from some instrument. In column A are numbers usuall
starting from 200 to 780 counting up every one unit. In the column
is the data corresponding to the numbers in column A. I'm trying t
figure out a formula that will copy the number 200 and every 5 unit
thereafter (200, 205, 210...etc) in one column plus the data tha
corresponds to that number in other column.

2). Also, in some data sets I receive, there are an even amount o
blank cells between each filled cell. So A1 would be filled and fiv
cells later, A7 is filled and five cells later A13 if filled and etc.
How do I copy the numbers in column A but not the blank cells?

3). Sometime I have a combination of problems 1 and 2.


Thanks.
:confused

 

[Maurice]

For item 2, just select all and use Data > Sort, this will put all th
blanks togethe

 

[Roshario]

What if the data is not in any order and I don't want to sort

 

[Anders S]

Roshario,

2. To delete the empty rows:
- select Col A
- Edit>Goto>Special>Blank/Empty
- Edit>Delete>Entire Row


1. To copy every 5th row in Cols A and B to Cols C and D, assuming the table starts in A1 (otherwise adjust the formulas accordingly):

- in C1 enter =OFFSET($A$1,(ROW()-ROW($A$1))*5,0)
- in D1 enter =OFFSET($A$1,(ROW()-ROW($A$1))*5,1)
- fill down Cols C and D as far as needed
- select Cols C and D
- Edit>Copy
- Edit>Paste special>Values
- delete Cols A and B

HTH
Anders Silven

 

[Maurice]

Just found this, whilst searching the site

» Another way to delete empty rows
Pat Ford wrote on August 06, 2003 09:44 EST
Before sorting your list to delete empty rows that are buried in it, d
this:
1. Unmerge any cells in your range of data.
2. Insert a column to the left of your data and input this formula
=IF(B1<>"",COUNTA($B$1:B1)&"","")]
into the first row in that column(ex: A1). Then fill down in the colum
to the last row containing data. This operation will number only th
rows with data in column B.
3. Then sort by column A. Your data will remain in its original orde
but all the empty rows will now be grouped together so that you ca
delete the