Flip data from horizontal to vertical format for uploading

Zakky

Board Regular
Joined
Mar 26, 2016
Messages
148
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,162
Messages
6,170,432
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top