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

	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

<tbody>
</tbody>

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

	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

<tbody>
</tbody>

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

	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

<tbody>
</tbody>

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.