Moving Data from Columns to Rows

[Lu]

Hi,

I am wondering if somebody could help with the following.

I have the following data in columns:

Acct Code Acct Descr CO1 CO3 CO5
4457 Rev 1 0 0 2000
4458 Rev 2 0 500 (1500)
4460 Rev 3 1000 0 750

What I want to get to is the following:

Acct Code Acct Descr CO Amount
4457 Rev 1 CO5 2000
4458 Rev 2 CO3 500
4458 Rev 2 CO5 (1500)
4460 Rev 3 CO1 1000
4460 Rev 3 CO5 750

So basically if there is a non-zero value for an account code then i want it against the CO# in ONE column. If there is a zero value, then i dont ant it displayed.

I have about 9000 lines of data, so if this could be automated it waould be great!

Thanks,

Lu.

 

[jindon]

Hi
try

Sub test()
Dim a, result(), n As Long, x As Long
Dim i As Long, ii As Long
With Range("a1").CurrentRegion
    With .Offset(1, 2).Resize(.Rows.Count - 1, .Columns.Count - 2)
        x = Application.CountIf(.Cells, "<>" & 0)
    End With
    a = .Value
    .ClearContents
End With
ReDim result(1 To x, 1 To 4)
For i = 2 To UBound(a, 1)
    For ii = 3 To UBound(a, 2)
        If a(i, ii) <> 0 Then
            n = n + 1
            result(n, 1) = a(i, 1)
            result(n, 2) = a(i, 2)
            result(n, 3) = a(1, ii)
            result(n, 4) = a(i, ii)
        End If
    Next
Next
With Range("a1")
    .Resize(, 4) = Array("Acct Code", "Acct Descr", "Co", "Amount")
    .Offset(1).Resize(x, 4) = result
End With
Erase a, result
End Sub

 

[Lu]

Sorry, but i do not follow this.......can you please give an example using that code.

Thx,

Lu.

 

[jindon]

It's a vba solution

1) hit Alt + F11 to open vb editor
2) go to [Insert] -> [Module] then paste the code on the blank on the right side
3) click x to close the window to get back to excel
4) go to [Tools] -> [Macro] -> [Macro] select "test" then hit Run

it will reformat the data, means erase the original data and replace with the new formatted data.

just take a copy file to test it.

 

[Lu]

that's great!!! Thx for your help.