# Thread: Transpose with a pattern

1. ## Transpose with a pattern

In the attached workbook I need to transpose some data AND skip cells as I do so. For example, the data for Sheet2!B4 is contained in Sheet1!A5, and the data for Sheet2!B5 is in Sheet1!B5.

Then the data for Sheet2!C4 is in Sheet1!C5, and the data for cell Sheet2!C5 is in Sheet1!D5.

This continues on for a whole bunch of cells (eventually the entire table in Sheet2 will be filled; I deleted a bunch of data from Sheet 1 for readability) or I'd just do this manually.

May I get some suggestions for how to do this with a minimum of pain? I was thinking of mapping cells (Sheet2!B4 =Sheet1!A5, Sheet2!B5 =Sheet1!B5) and then filling the series for the rest of the cells but I can't get Excel to understand the pattern?

2. If I understand the pattern, in Sheet2!B4:
=OFFSET(Sheet1!\$A\$1,INT((ROW()-4)/2)+4,(COLUMN()-2)*2+MOD(ROW(),2))

Copy it across the columns and down the rows...

Steve

3. Exactly what I needed. Now I'll edit for the actual data. You are the best. Happy New Year!

#### Posting Permissions

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