Results 1 to 3 of 3

Thread: Offset help

  1. #1
    New Lounger
    Join Date
    Dec 2013
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Offset help

    This should be a softball question. I'm looking to move blocks of data currently residing in A1:A200 to BCDE.
    My data looks like this under A1:
    Name
    Title
    Address
    Zip
    Name
    Title
    Address
    Zip

    This data repeats itself in the same order all in column A. I've tried a number of offset formulas that end up in an error and not shifting.
    I'd like the end result to be:
    B C D E
    Name Title Address Zip

    Suggestions? Thanks in advance!

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Welcome to the Lounge:

    Your might want to try INDEX rather than offset. I have attached an example. You will need to change some formulas to pick up the data you want.

    Hope this helps

    Regards,

    Tom D
    Attached Files Attached Files

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    As an alternative, a VBA approach:

    TransposeAcross1.png TransposeAcross2.png

    Code:
    Public Sub TransposeAcross()
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    Row = 1
    For I = 1 To LastRow
        For J = 2 To 5
            Cells(I, J) = Cells(Row, 1)
            Cells(Row, 1) = ""
            Row = Row + 1
        Next J
    Next I
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2013-12-04 at 20:40.

Tags for this Thread

Posting Permissions

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