Flip data from horizontal to vertical format for uploading
Results 1 to 4 of 4

Thread: Flip data from horizontal to vertical format for uploading
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2016
    Location
    Sydney, Australia
    Posts
    89
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Flip data from horizontal to vertical format for uploading

    Hi, Is it possible to change data format in Excel. My data in excel is as follows:

    Name of GL GL Code Jan-19 Feb-19 Mar-19 Apr-19......until Dec-19
    Salaries 8205 $100 $200 $300 $400 $1200[/PHP][/HTML]
    Tax 8206 $10 $20 $30 $40 $120

    I need to show it as follows:
    Salaries 8205 Jan-19 $100
    Salaries 8205 Feb-19 $200
    Salaries 8205 Mar-19 $300
    Salaries 8205 Apr-19 $400
    all twelve months going down for every GL Code as there are hundreds of different GL codes

    then new GL 8206 straight after GL 8205 in a continuous rows of data
    Tax 8206 Jan-19 $10
    Tax 8206 Feb-19 $200
    Tax 8206 Mar-19 $300
    Tax 8206 Apr-19 $400


    Your help would be greatly appreciated as this will save me hours of work.

    Many thanks in advance

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,322
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Flip data from horizontal to vertical format for uploading

    Assume your data begins in cell A1 on sheet1.
    The result will remain on sheet2.

    Try this

    Code:
    Sub Flip_data()
        Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, k As Long
        Set sh1 = Sheets("Sheet1")
        Set sh2 = Sheets("Sheet2")
        sh2.Cells.Clear
        k = 2
        For Each c In sh1.Range("A2", sh1.Range("A" & Rows.Count).End(xlUp))
            For j = 3 To 14
                sh2.Cells(k, "A").Value = c
                sh2.Cells(k, "B").Value = c.Offset(, 1)
                sh2.Cells(k, "C").Value = sh1.Cells(1, j)
                sh2.Cells(k, "D").Value = sh1.Cells(c.Row, j)
                k = k + 1
            Next
        Next
        MsgBox "End"
    End Sub
    Regards Dante Amor

  3. #3
    Board Regular
    Join Date
    Mar 2016
    Location
    Sydney, Australia
    Posts
    89
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Flip data from horizontal to vertical format for uploading

    I can not believe my own eyes. This works like a dream. You VBA guys are god send and have amazing skills. I'm speechless, you have no idea how much time this has saved me.

    MEGA MEGA BIG THANK YOU, DanteAmor
    Last edited by Zakky; Jul 20th, 2019 at 11:50 PM.

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,322
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Flip data from horizontal to vertical format for uploading

    Quote Originally Posted by Zakky View Post
    I can not believe my own eyes. This works like a dream. You VBA guys are god send and have amazing skills. I'm speechless, you have no idea how much time this has saved me.

    MEGA MEGA BIG THANK YOU, DanteAmor
    Im glad to help you. I appreciate your kind words.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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