Make the pivot data to be un pivot data.

demitzz

New Member
Joined
Dec 7, 2011
Messages
4
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.

:)
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
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
ABC
1Item4-Oct-125-Oct-12
2Apel21
3Beruang12
4Mobil23
5pena12

<tbody>
</tbody>
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
AB
1ItemTanggal
2Apel4-Oct-12
3Apel4-Oct-12
4Apel5-Oct-12
5Beruang4-Oct-12
6Beruang5-Oct-12
7Beruang5-Oct-12
8Mobil4-Oct-12
9Mobil4-Oct-12
10Mobil5-Oct-12
11Mobil5-Oct-12
12Mobil5-Oct-12
13pena4-Oct-12
14pena5-Oct-12
15pena5-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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,472
Messages
5,636,516
Members
416,920
Latest member
Riskyplan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top