• 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.

Billing File

A

ashish mehra

Active Member
Hi all,

I have ITD billing file for last 2 years. I want to know billing for any number of del at any time, providing clear picture of actual revenue.

I have attached sample file having 2 sheets. I want to make master sheet for all the new del's & their charges.

Please check.

Regards
AM
 

Attachments

  • attachment_1474977037536_Sample Billing File.xlsb
    12.1 KB · Views: 9
Hi Ashish,

please use attached macro file which will consolidate all the sheets which are there on the workbook. you can create a pivot table based on master sheet as per your requirement.

Hope this solves your problem...
 

Attachments

  • attachment_1474977037536_Sample Billing File _v1.xlsb
    19.8 KB · Views: 9
Here's alternate method using PowerQuery. Query merged and loaded to Data Model and output into PivotTable.
 

Attachments

  • attachment_1474977037536_Sample Billing File.xlsb
    172.5 KB · Views: 19
Hi Marc,

Can't use remove duplicate as there are 5 lac rows in first month & going forward we have around 30-40 k new activations & some deactivations.

Fyi...In August file we have 7.5 lac rows.

I have attached revised template. Pl go through it.
 

Attachments

  • Sample Billing File - Revised.xlsb
    12.5 KB · Views: 7
Patience. I'm on EST. Just got into work.

FYI - PowerQuery is not available for 2007. Similar method can be used via MS Query.

1. Convert data range to table.
2. Go to Data tab (or PowerQuery depending on Excel version).
3. Choose "From Table"
4. Go to "Edit" and "Close & Load to" and choose connection only.
5. Once both tables are loaded, choose one of them and pick Merge.
6. Then right click on Query pane and add to data model.

Alternate method.
1. Go to blank query.
2. Go to View->"Advanced Editor"
3. Set "Source" to "Excel.CurrentWorkbook()". Note that it is case sensitive.
upload_2016-9-28_7-13-32.png
4. Filter the result for only the tables.
upload_2016-9-28_7-14-26.png
5. Click on expand icon beside Content and expand all columns. Then, "Close & Load to"->"Connection Only", but make sure to click on add to data model.

Once data is loaded on data model, just insert Pivot on new sheet and make sure that source is set to "Use this workbook's Data Model"
 
Many thanks for your effort.

I have successfully make a replica to your file.

Will try to check on real data that has more than 16 lac rows. Fingers crossed don't know if Power Query will support or not.

Regards
AM
 
Hi Chihiro,

Powerquery is successfully working on small data but unfortunately it does not work on data of even 6-7 lac rows.

Should we adopt new approach / software. Pl suggest.

Regards
AM
 
That's about 6~700k rows? PowerQuery should not have issue dealing with it.
I work daily with anywhere from 60k to 1.5 million rows of data.

What's your RAM, version of Excel, OS, CPU etc? You may want to discuss with IT and see if it is feasible to install Office in 64 bit instead of 32 bit to take full advantage of PowerQuery (there's memory limit to 32 bit installation, 3 or 4 Gb if I recall right).

Edited for clarity:
Also, if you are loading entire table from same workbook, that would slow things down. Load it from separate workbook to data model and use Pivot Table to summarize.

Another way to handle large amount of data is to utilize MS SQL database to do heavy lifting and fold data at query level, then load to Excel via PowerQuery. I often do this for IVR data from PBX system.

In my opinion, if data is too large to be handled by Excel, it's likely too detailed to be of much use in business analysis (there are few exceptions). It is best to summarize data prior to loading.

There are alternatives to PowerQuery/Excel, but it will require investment in infrastructure, implementation and maintenance. Software will depend on your specific need (i.e. what is the most important role? Data discovery, analysis, reporting, processing, etc).

See link for few that I've posted on.
http://forum.chandoo.org/threads/qlik-sense-desktop-has-anyone-used-this-before.28906/
- Handles large data very well. But to take full advantage, you will need licensed version with dedicated server (about 1TB RAM).

http://forum.chandoo.org/threads/adopting-dashboards.31583/#post-187720
- Next Reports is decent for simple reporting and KPI dashboard need.
- amCharts is more flexible and handles large data well (JSON format), but will require you to know java script, web administration and few other things.
 
Last edited:
Hello Chihiro,

Large data might be the reason for non performance.
I have noted it down the recommended links n software.

I am using laptop having

Ram: 4GB
Processor: Intel (R) Core (TM) i3 - 3110M CPU @ 2.40 GHz
System Type: 64-bit, x64-based processor
OS: Win 8
Version: Excel 2013

In my research found link:

This video explains how to create an Excel pivot table based on a SQL Server database. You'll learn how to create connections to external data sources, how to edit connection strings and how to create a pivot cache using the external connection. The video also shows you how to create views in SQL Server Management Studio, along with how to write basic SQL statements in your VBA code to create flexible queries that you can use to create pivot tables.

Can I recommend you to watch it.

Regards
AM
 
I'd recommend increasing memory size to at least 8 GB. If able to 16 GB. Your processor is bit weak too. If you can, get a true quad-core processor.
Edit: i3-series is dual-core with hyper threading

...Excel pivot table based on a SQL Server database.
Click to expand...
Thanks for sharing video.

PowerQuery is just the tool designed for it. You can connect to SQL Server and write native SQL query script in it to do data folding. No need for VBA. You can build named ranges in a worksheet and leverage it to pass dynamic parameter to PowerQuery.

View is standard tool in SQL DB to segment, simplify & customize underlying base table to users. I always query from a view and not the table itself. I have separate server that's dedicated to reporting and views/stored procedures are used to replicate data from production server (to avoid impacting production server when I run multiple large and/or complex queries).

Edit: Do be careful when you create views. It's easy to fall into one of more common traps. See link for detail.
http://stackoverflow.com/questions/4087673/what-are-the-downsides-of-using-sqlserver-views
 
Last edited:
You must log in or register to reply here.
Back
Top