Copy Tranpose & Reformat Based On Changes In Value

Hugobarb · Jun 24th 2008

I have an extract file that is in excel and lists contract numbers with a payment amount to the right. Each contract has multiple payments by period and thus there are multiple rows for each contract representing the multiple periods. The number of periods for each contract varies. The extract file contains multiple contracts. What I would like to do is have the contract number listed in one row and copy, paste, transpose the payments to the right of the contract number. The resulting file or sheet will have only one row per contract. The issue is that the code will have to recognize when the file number changes. I know I can do this manually but with 10 files and over 450 contracts this is a bit time consuming.

Attached is a sample file with two tabs titled "Extract File" and "Desired Format".
Extract File - is a sample of the main file that contains the data.
Desired Format - is a sample of what I would like the final result to be. It can be in another sheet or another file.

Thank you in advance for any help you can provide.

Robert B · Jun 29th 2008

Re: Copy Tranpose & Reformat Based On Changes In Value

Hi

I would suggest a Pivot Table using Contract No as the Row and PMT_Period as the Column

HTH

Robert

jindon · Jun 29th 2008

Re: Copy Tranpose & Reformat Based On Changes In Value

try

Code

Sub test()
Dim a, b(), i As Long, n As Long
With Sheets(1).Range("a1").CurrentRegion
    a = .Resize(,6).Value
    ReDim b(1 To UBound(a,1), 1 To Application.Max(.Columns("b")) + 1)
End With
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For i = 2 To UBound(a,1)
        If Not .exists(a(i,1)) Then
            n = n + 1 : b(n,1) = a(i,1) : .add a(i,1), n
        End If
        b(.item(a(i,1)), a(i,2) + 1) = a(i,6)
    Next
End With
Sheets(2).Range("a1").Resize(n, UBound(b,2)).Value = b
End Sub

Display More

**pike** · Jun 29th 2008

Re: Copy Tranpose & Reformat Based On Changes In Value

Hey Jindon

Your VBscripts solutions ..... perfection!

Best regards pike

Copy Tranpose & Reformat Based On Changes In Value

Participate now!

Share