How to create rows of products from pivot table?

[BrentCOS]

Hi,

I have exhausted Google to find the solution so I was hoping someone here would be able to assist. I have an Excel file that contains customer orders and the associated items in those orders. I've created a pivot table that summarizes the information (order number and items purchased by order number) but what I need is a simple excel worksheet that contains the items orders, by order number, where each item is in a distinct cell running horizontally. So each row represents an order with the items listed in cells.

Here's what my Pivot table looks like. It was created from transactional data in another tab.

Here's the result I'm looking for (where each row represents a unique order number) and the cells contains the items ordered:
Example: In Row 1 below, it is a distinct order where the customer purchased one item (W010638C). In the last row below, it is another order number where 3 items were purchased.

I appreciate any help,

Brent

 

[Anthony47]

Try the following macro, to be loaded into a Standard module of your vba project:

Sub CreaList()
Dim dSh As Worksheet, myC As Range, NextR As Long, hOff As Long
'
Sheets("PivotTableSheet").Select        '<<< The sheet with the pivot table
Set dSh = Sheets("Foglio4")             '<<< The sheet to be used for the resume
'
NextR = dSh.Cells(Rows.Count, 1).End(xlUp).Row + 1
For Each myC In Range(Range("B4"), Range("B4").End(xlDown))
    If myC.Offset(0, -1) <> "" Then
        hOff = 0
        dSh.Cells(NextR, 1) = myC.Offset(0, -1).Value
        dSh.Cells(NextR, 2) = myC.Value
        NextR = NextR + 1
    Else
        hOff = hOff + 1
        dSh.Cells(NextR - 1, 2 + hOff).Value = myC.Value
    End If
Next myC
MsgBox ("Completed, rows: " & NextR - 1)
End Sub

The lines marked <<< need to be customized with your information
If you need further instruction please ask...

 

[BrentCOS]

Try the following macro, to be loaded into a Standard module of your vba project:

Sub CreaList()
Dim dSh As Worksheet, myC As Range, NextR As Long, hOff As Long
'
Sheets("PivotTableSheet").Select        '<<< The sheet with the pivot table
Set dSh = Sheets("Foglio4")             '<<< The sheet to be used for the resume
'
NextR = dSh.Cells(Rows.Count, 1).End(xlUp).Row + 1
For Each myC In Range(Range("B4"), Range("B4").End(xlDown))
    If myC.Offset(0, -1) <> "" Then
        hOff = 0
        dSh.Cells(NextR, 1) = myC.Offset(0, -1).Value
        dSh.Cells(NextR, 2) = myC.Value
        NextR = NextR + 1
    Else
        hOff = hOff + 1
        dSh.Cells(NextR - 1, 2 + hOff).Value = myC.Value
    End If
Next myC
MsgBox ("Completed, rows: " & NextR - 1)
End Sub

The lines marked <<< need to be customized with your information
If you need further instruction please ask...

Hi Anthony, thank you for your response. That is impressive. Unfortunately, I do not know what to do with the macro 'code'. What is a "Standard module"? I do not have a VBA project. Clearly I'm no where near you level of expertise with Excel!

I appreciate any clarification,

Brent

 

[BrentCOS]

Hi Anthony, thank you for your response. That is impressive. Unfortunately, I do not know what to do with the macro 'code'. What is a "Standard module"? I do not have a VBA project. Clearly I'm no where near you level of expertise with Excel!

I appreciate any clarification,

Brent

Hi Anthony,

I Googled your instructions and I followed a video on how to add macro code to a worksheet.
1) CTRL + F11
2) Select worksheet to add macro to in the VBA window
3) Insert > Module
4) Paste your code
5) Save file as .xlsm to enable macros
6) open .xlsm file and go to View > Macros then select the one I just added and RUN.

A) I receive an error message as seen below.
B) In your code I see a couple of green notes. Am I to modify the code to fit my worksheet?

 

[Anthony47]

I think you have to delete that [/CODE] after the valid code

The green portions are "comments" that tell you wich parts need to be customized, using the syntax I used

 

[BrentCOS]

I think you have to delete that [/CODE] after the valid code

The green portions are "comments" that tell you wich parts need to be customized, using the syntax I used

Wow, I got it to work! I re-pasted your code and changed some of it to align with the pivot table tab name as well as added a tab called "Results" and modified your code to dump the results in that tab. I really appreciate your help!

Do you happen to have a personal 'donation' page? $$$

 

[Anthony47]

Thank you for the feedbak
(and I don't think that people here expects to receive a donation; but you might consider your local savethechildren organization)

 

[BrentCOS]

Thank you for the feedbak
(and I don't think that people here expects to receive a donation; but you might consider your local savethechildren organization)

Understood, thank you for your incredible help!