I have data as follows (1 thru 11 are the rows; A and B are columns)

A B
1 ADM-1
2 blank row
3 SD1
4 blank row
5 ADM-2
6 blank row
7 SD2
8 blank row
9 ADM-3
10 blank row
11 SD3
and so on... How do I move the data so the SD#'s are next to the ADM# above
it, to be:

A B

1 ADM_1 SD1
2 ADM_2 SD2
3 ADM_3 SD3

I am using 2003.

Thank you!


--

A macro is not needed. If your data is in column A, then in B1 enter:
=INDIRECT("A" & ROW()*4-3) and copy down

and in C1 enter:
=INDIRECT("A" & ROW()*4-1) and copy down
--
Gary''s Student - gsnu200772


"MB" wrote:

I have data as follows (1 thru 11 are the rows; A and B are columns)

A B
1 ADM-1
2 blank row
3 SD1
4 blank row
5 ADM-2
6 blank row
7 SD2
8 blank row
9 ADM-3
10 blank row
11 SD3
and so on... How do I move the data so the SD#'s are next to the ADM# above
it, to be:

A B

1 ADM_1 SD1
2 ADM_2 SD2
3 ADM_3 SD3

I am using 2003.

Thank you!


--

You don't need a macro.

Select column A and F5SpecialBlanksOK

EditDeleteShift Up or delete entire rows.

In B1 enter =OFFSET($A$1,1*ROW()-1,0)

In C1 enter =OFFSET($A$1,2*ROW()-1,0)

Copy both down as far as half of "and so on".

In your example you would copy to row 3

If you had 12 cells with data you would copy down to row 6


Gord Dibben MS Excel MVP

On Fri, 7 Mar 2008 13:47:04 -0800, MB wrote:

I have data as follows (1 thru 11 are the rows; A and B are columns)

A B
1 ADM-1
2 blank row
3 SD1
4 blank row
5 ADM-2
6 blank row
7 SD2
8 blank row
9 ADM-3
10 blank row
11 SD3
and so on... How do I move the data so the SD#'s are next to the ADM# above
it, to be:

A B

1 ADM_1 SD1
2 ADM_2 SD2
3 ADM_3 SD3

I am using 2003.

Thank you!