Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Apr 2004
    Location
    Durham, North Carolina, USA
    Posts
    32
    Thanks
    11
    Thanked 1 Time in 1 Post

    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?

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #3
    Lounger
    Join Date
    Apr 2004
    Location
    Durham, North Carolina, USA
    Posts
    32
    Thanks
    11
    Thanked 1 Time in 1 Post

    Thumbs up

    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
  •