How to select data in non adjacent rows of a column automatically

[jdpf]

How can I select the data in non adjacent rows of a column (eg. A1, A9. A17.
A25.......A800, A 808, A816 etc.) and place it into another column? The data
I need to select is consistently 8 rows apart in a very large spreadsheet. I
am using Excel 2003.

 

[joel]

You can add an auxilary column and sort like this

1) Put in a new column in the 1st 8 rows the number 1 to 8. Then copy the
numbers 1 to 8 down the entire new column.
2) Sort on the new column.
3) All The data you are looking for is now on adjacent rows.

 

[Jarek Kujawa]

one way:

in B2 insert the formula:

=IF(MOD(ROW()-1,8),"",A1)

copy down

then select the whole range in col B, copy-pasteSpecial->Values

or through VBA


Sub moving()

For i = 1 To 100
If ((Cells(i, "A").Row - 1) Mod 8) = 0 Then
Cells(i, "A").Offset(0, 1).Value = Cells(i, "A").Value
End If
Next i

End Sub

suit 100 to suit

 

[jdpf]

I don't need the rows numbered. What I have is a list of 1500 name and
address labels. The "names" are in cells A1, A9, A17, A25 and so on, all are
8 rows apart. The "addresses" are in cells A2, A10, A18, A26, all 8 rows
apart. the city, state and zips are in cells A3, A11, A19, A27 and so on all
8 rows apart also. I need the names in one column, addresses in the next
column and the city state and zip in the next. I have done this before but I
can't remember how.

 

[jdpf]

What I have is a list of 1500 name and address labels. The "names" are in
cells A1, A9, A17, A25 and so on, all are 8 rows apart. The "addresses" are
in cells A2, A10, A18, A26, all 8 rows apart. the city, state and zips are
in cells A3, A11, A19, A27 and so on all 8 rows apart also. I need the names
in one column, addresses in the next column and the city state and zip in the
next. I have done this before but I can't remember how.

 

[Gord Dibben]

In B1 enter this formula.

=INDEX($A:$A,(ROWS($1:1)-1)*8+COLUMNS($A:A))

Copy across to I1

Select B1:I1 and copy down until you get zeros.


Gord Dibben MS Excel MVP