Make the pivot  data to be un pivot data.
Results 1 to 4 of 4

Thread: Make the pivot data to be un pivot data.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2011
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Make the pivot data to be un pivot data.

    Hello every body,
    I have problem, I want make the pivot data to be un pivot data.

    Example :
    I have three column data, now I want excel make be one data tobe one line

    Item 4-Oct-12 5-Oct-12 Item Tanggal
    Apel 2 1 Apel 4-Oct-12
    Beruang 1 2 Apel 4-Oct-12
    Mobil 2 3 Apel 5-Oct-12
    pena 1 2 Beruang 4-Oct-12
    Beruang 5-Oct-12
    Beruang 5-Oct-12
    Mobil 4-Oct-12
    Mobil 4-Oct-12
    Mobil 5-Oct-12
    Mobil 5-Oct-12
    Mobil 5-Oct-12
    pena 4-Oct-12
    pena 5-Oct-12
    pena 5-Oct-12
    I want this forum help me to solve this with formula in excel.


  2. #2
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Make the pivot data to be un pivot data.

    Hi and welcome to the forum,

    Assuming its not an actual PivotTable (i.e. you can't simply double-click the Grand Total amount to get the corresponding table), a VBA solution might be as follows:

    Initial setup:

    Sheet1
    A B C
    1 Item 4-Oct-12 5-Oct-12
    2 Apel 2 1
    3 Beruang 1 2
    4 Mobil 2 3
    5 pena 1 2
    Excel 2010

    The code:
    Code:
    Sub example()
    
    Dim vIn     As Variant
    Dim vOut    As Variant
    Dim i       As Long
    Dim j       As Long
    Dim k       As Long
    Dim r       As Long
    
    With Sheet1.Range("A1").CurrentRegion
        vIn = .Value ' input range
        ReDim vOut(1 To Application.Sum( _
                            .Offset(1, 0).Resize(.Rows.Count - 1)), _
                            1 To 2)
    End With
    
    For i = 2 To UBound(vIn, 1)
        For j = 2 To UBound(vIn, 2)
            For k = 1 To vIn(i, j)
                r = r + 1
                vOut(r, 1) = vIn(i, 1) ' Item
                vOut(r, 2) = vIn(1, j) ' Tanggal
            Next k
        Next j
    Next i
    
    Sheet2.Range("A1:B1") = Array("Item", "Tanggal") ' print column headers
    Sheet2.Range("A2").Resize(UBound(vOut, 1), _
                              UBound(vOut, 2)) = vOut ' print data
    
    End Sub
    Output:

    Sheet2
    A B
    1 Item Tanggal
    2 Apel 4-Oct-12
    3 Apel 4-Oct-12
    4 Apel 5-Oct-12
    5 Beruang 4-Oct-12
    6 Beruang 5-Oct-12
    7 Beruang 5-Oct-12
    8 Mobil 4-Oct-12
    9 Mobil 4-Oct-12
    10 Mobil 5-Oct-12
    11 Mobil 5-Oct-12
    12 Mobil 5-Oct-12
    13 pena 4-Oct-12
    14 pena 5-Oct-12
    15 pena 5-Oct-12
    Excel 2010

    If you're new to VBA, then perhaps see this tutorial: Introduction to VBA & Excel Macros - What are they & Writing your First Macro using Excel | Chandoo.org - Learn Microsoft Excel Online
    Alternatively there are lots of other tutorials and guides to help you get started - just Google.

  3. #3
    New Member
    Join Date
    Dec 2011
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Make the pivot data to be un pivot data.

    thank you circledchicken, ​i'll try it

  4. #4
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Make the pivot data to be un pivot data.

    Quote Originally Posted by demitzz View Post
    thank you circledchicken, ​i'll try it
    You're welcome.

Some videos you may like

User Tag List

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
  •