• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Code to create a pivot table from random data

Chris van der Berg

Chris van der Berg

Member
Hi Everbody,

I have a Data Backup sheet with random information scattered between column A to W
This info are transferred from different customer invoices to the Data Backup Sheet.
I am looking for a VBA code to will sort this data into a Pivot Table.
I tried varias codes ect but were unsuccessfull.

Can someone please help.

Regards

Chris
 

Attachments

  • Pivot Table.xlsm
    33.2 KB · Views: 2
Chris van der Berg
Do You have any 'raw data'?
... before transferred someway to Your file.
... Could it transfer other way? ... like ... more useful layout?
What kind of results are You looking for?
 
Part of the original file is an invoice.
The products that appear on the invoice is in no specific order eg. the first invoice could have product A on the 1st row whilst the same product could be on the 2nd or 3rd row in the following invoices.
The data get transferred from the invoice to the Data Backup sheet via a VBA code.
If there is a more useful layout, even better
The result I am looking for is
a) Sales per product per customer
b) Sales per product for all customers (Ranked from top to bottom)
c) List of products purchased by customer( ranked from Top to bottom)
I tried to zip the original file but even after it had been zipped, it is still to big.

Hope this answers your questions

Regards
Chris
 
Chris van der Berg
Do You have any 'raw data'?
... before transferred someway to Your file.
The data get transferred from the invoice to the Data Backup sheet via a VBA code.
... means ... someone has done work to get 'Data Backup'-sheet to look as it is now.
... and now, You would change it as You could get some Pivots ... did I understand?
if so then same as 'double work!'
... that's why a asked 'raw data' .. or .. 'Your via a VBA code' could make more useful layout at once.

Nobody here needs the real 'raw data' ... You should have / create a sample-file which has same kind of data ... and enough rows.
 
In the attached is your file from msg#1 of this thread with a new table below yours on the Data Backup sheet.
This table has been created using Power Query which in Excel 2010 might be a ribbon tab of its own. From that table a pivot table on the other sheet.
Am I getting the data in the form you're looking for?
 

Attachments

  • Chandoo43694Pivot Table.xlsm
    49.9 KB · Views: 3
Chris van der Berg
Which sheet has 'the raw data'?
If many time someone 'transfers someway to somewhere' those are extra steps and those won't help You
... of course, if there will be enough extra steps then You could finally get Your needed layout.
 
Chris van der Berg said:
I downloaded power query and installed it. This seems to be the solution to my problem.
Click to expand...
The manipulations I did to the data in Power Query are probably not especially 'slick' as I'm still getting my head around it; for example if your Product/Sales pairs of columns starts with Sales at the left rather than Product the output table is likely to be plain wrong.
 
Chris van der Berg
I tried to as 'raw data' - You offered same layout ... what would change?
Try to reread #4 reply ...
What for is that sheet?
Could same person do something which could be useful for You too?
Now .. same data should modify this-way-that-way-this-way ... instead only one-way.
 
Chris van der Berg
I have read those phrases many times.
As I've tried to express many times:
Now .. same data should modify this-way-that-way-this-way ... instead only one-way.
That kind of ... are for someone others.
 
The attached suggests a different way of creating your backup data sheet, setting it up so that that data can be pivoted directly.
The sheet's called Data Backup (2) and is updated using the macro backup2 sitting in Module2.
The merged cells in cells B24:I43 of your Input Data sheet were playing havoc with vba so I unmerged them.
That way, no Power Query needed.
I realise, should you want to delete a single quote's backup information it's now more difficult than just deleting a single row, but it's much easier to extract data from.
 

Attachments

  • Chandoo43694Pivot Table 1.xlsm
    50.3 KB · Views: 7
You must log in or register to reply here.
Back
Top