Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts

    Arrange Web Imported Data Horizontally Excel 2007

    Hi All,

    I like to have some code or formula that arranges my imported data into horizontal order as shown in my attached workbook, please.

    It starts at A23 to A25 on sheet WEB and should be moved to sheet DATA from A3 to C3 - same procedure for B23 to B25 on WEB and E3 to G5 on DATA.

    All the way down to A82 and B82 on WEB.

    The imported data change their values but the format stays.

    Thank you very much for your time...

    Best regards,
    Wolfgang
    Attached Files Attached Files
    Last edited by wolfgang; 2014-11-01 at 09:49. Reason: Typo...

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Wolfgang,

    The following code will transpose your data from the Web sheet to the data sheet. Place in a standard module and run by clicking on the button on the data sheet.

    HTH,
    Maud

    Wolfgang1.png

    Code:
    Public Sub TransposeData()
    Application.ScreenUpdating = False
    Row = 3
    For I = 23 To 80 Step 3
     Range("A" & Row & ":C" & Row).FormulaArray = "=TRANSPOSE(WEB!$A$" & I & ":$A$" & I + 2 & ")"
     Range("E" & Row & ":G" & Row).FormulaArray = "=TRANSPOSE(WEB!$B$" & I & ":$B$" & I + 2 & ")"
     Range("I" & Row & ":K" & Row).FormulaArray = "=TRANSPOSE(WEB!$C$" & I & ":$C$" & I + 2 & ")"
     Row = Row + 1
    Next I
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    wolfgang (2014-11-01)

  4. #3
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Darmstadt, Hessen, Germany
    Posts
    128
    Thanks
    15
    Thanked 2 Times in 2 Posts
    Hi Maude,

    read it, applied it, thanked it...

    Have a great weekend...

    Best
    Wolfgang
    Last edited by wolfgang; 2014-11-01 at 11:41.

  5. #4
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    To avoid leaving a formula or formula=value just use
    Range("A" & Row & ":C" & Row).value=application.transpose(etc).value

  6. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    dg,

    Could you please post the amended code using your technique?

    Thanks,
    Maud

  7. #6
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Option Explicit
    Sub TransposeSAS()
    Dim c As Integer
    Dim lc As Integer
    Dim i As Long

    With ActiveSheet
    .Columns.ClearContents
    For c = 1 To 3
    lc = Cells(1, Columns.Count).End(xlToLeft).Column + 2 '(2)
    For i = 23 To 80 Step 3
    Cells(i - 22, lc).Resize(, 3).Value = _
    Application.Transpose(Sheets("web").Cells(i, c).Resize(3))
    Next i
    Next c
    .Columns.AutoFit
    .Columns(3).SpecialCells(xlCellTypeBlanks).EntireR ow.Delete
    End With
    End Sub

Posting Permissions

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