# Thread: Transposing alternate cells (2003)

1. ## Transposing alternate cells (2003)

Is there a formula (possibly an array formula?) that will allow me to pick up every alternate cell down a column and transpose these across a row? Thanks, Andy.

2. ## Re: Transposing alternate cells (2003)

If the in-between cells are empty:

- select the range you want copied
- hit F5, click special, click "Values" or "Formulas" (whichever applies)
- click OK and click copy
- go to the destination area and click edit, paste special and select the transpose option.

3. ## Re: Transposing alternate cells (2003)

The alternate cells are not empty, so I do need a formula to achieve this. Andy.

4. ## Re: Transposing alternate cells (2003)

If the column of data is in A1:A10 and you want to place every other item in C1:G1:

Select c1:G1
Enter in the formula bar:
<pre>=INDEX(\$A\$1:\$A\$10,(COLUMN()-3)*2+1)</pre>

Confirm with ctrl-shift-enter.

[Note:The "3" is since you start in Col C (3rd letter), the "2" is since you want every other (every 2nd item), and the 1 is since you want to start with the 1st value in the list]

Steve

5. ## Re: Transposing alternate cells (2003)

Say your column starts in A2, and you want the row with formulas to start in B1. Put this formula in B1:

=OFFSET(\$A\$2,2*(COLUMN()-COLUMN(\$B\$1)),0)

and fill right as far as needed. If your column and row start elsewhere, adjust the references to A2 and B1 accordingly.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•