Make the pivot data to be un pivot data.

[demitzz]

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

[TABLE="width: 512"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Item
[/TD]
[TD]4-Oct-12
[/TD]
[TD]5-Oct-12
[/TD]
[TD][/TD]
[TD]Item
[/TD]
[TD]Tanggal
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Apel
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]Apel
[/TD]
[TD]4-Oct-12
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Beruang
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]Apel
[/TD]
[TD]4-Oct-12
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Mobil
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD]Apel
[/TD]
[TD]5-Oct-12
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]pena
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD]Beruang
[/TD]
[TD]4-Oct-12
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Beruang
[/TD]
[TD]5-Oct-12
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Beruang
[/TD]
[TD]5-Oct-12
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mobil
[/TD]
[TD]4-Oct-12
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mobil
[/TD]
[TD]4-Oct-12
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mobil
[/TD]
[TD]5-Oct-12
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mobil
[/TD]
[TD]5-Oct-12
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Mobil
[/TD]
[TD]5-Oct-12
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]pena
[/TD]
[TD]4-Oct-12
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]pena
[/TD]
[TD]5-Oct-12
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]pena
[/TD]
[TD]5-Oct-12
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I want this forum help me to solve this with formula in excel.

 

[circledchicken]

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[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[/TR]
[TR]
[TH]1[/TH]
[TD]Item[/TD]
[TD="align: right"]4-Oct-12[/TD]
[TD="align: right"]5-Oct-12[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]Apel[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]Beruang[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]Mobil[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD]pena[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

The 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[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
[TR]
[TH]1[/TH]
[TD]Item[/TD]
[TD]Tanggal[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]Apel[/TD]
[TD="align: right"]4-Oct-12[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]Apel[/TD]
[TD="align: right"]4-Oct-12[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]Apel[/TD]
[TD="align: right"]5-Oct-12[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD]Beruang[/TD]
[TD="align: right"]4-Oct-12[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD]Beruang[/TD]
[TD="align: right"]5-Oct-12[/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD]Beruang[/TD]
[TD="align: right"]5-Oct-12[/TD]
[/TR]
[TR]
[TH]8[/TH]
[TD]Mobil[/TD]
[TD="align: right"]4-Oct-12[/TD]
[/TR]
[TR]
[TH]9[/TH]
[TD]Mobil[/TD]
[TD="align: right"]4-Oct-12[/TD]
[/TR]
[TR]
[TH]10[/TH]
[TD]Mobil[/TD]
[TD="align: right"]5-Oct-12[/TD]
[/TR]
[TR]
[TH]11[/TH]
[TD]Mobil[/TD]
[TD="align: right"]5-Oct-12[/TD]
[/TR]
[TR]
[TH]12[/TH]
[TD]Mobil[/TD]
[TD="align: right"]5-Oct-12[/TD]
[/TR]
[TR]
[TH]13[/TH]
[TD]pena[/TD]
[TD="align: right"]4-Oct-12[/TD]
[/TR]
[TR]
[TH]14[/TH]
[TD]pena[/TD]
[TD="align: right"]5-Oct-12[/TD]
[/TR]
[TR]
[TH]15[/TH]
[TD]pena[/TD]
[TD="align: right"]5-Oct-12[/TD]
[/TR]
</tbody>[/TABLE]
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.

 

[demitzz]

thank you circledchicken, ​i'll try it

 

[circledchicken]

thank you circledchicken, ​i'll try it

You're welcome.