Given a database of 50,000 rows, counting unique values with conditions using formulas would either adversely effect workbook performance or would not work in the first place at all.
In this workbook, I have shown the technique to count unique values with conditions on a large database
1. Using PowerPivot - Will only work in Excel 2010 and higher versions
2. Using a normal Pivot Table and SUMPRODUCT() function - Will work for all versions but is not as efficient as the PowerPivot solution.
To count unique values with conditions on small databases, you may refer to the following link
Hi Ashis,
I have a table which contains the following columns -
City Name, City Code, Customer Name, Customer code, Brand, pack size & Quantity.
Each brand has different pack size. Like one brand contains 750ML, 375ML, 180ML, 90ML and another one is contain only three pack size 750ML, 375ML, 180ML.
I want to make a power pivot which show how many unique customers are lifted all Pack size or how many unique customers lifted only one or two, three ….nth pack size.
I haven't give you the complete file due it is very heavy 800MB size csv file which is very large customer database. I have give a sample of that data and require result.
file link is as following-
https://onedrive.live.com/redir?resid=354CE60AA62E9D4C!147&authkey=!ABcCoG__6IjTAKI&ithint=file%2c.xlsx
Hi,
Please check the link. When I click on it, I get a message saying that "This Item might not exist or is no longer available"
But when i am clicking on the above link that is working properly. anyway, please find the another links.
One Drive- http://1drv.ms/1ft92cN
Google Drive - https://drive.google.com/file/d/0B5HVjakhvy_VNENSVTh3Y3NFT28/edit?usp=sharing
Hi,
You may refer to my solution in the third worksheet of this workbook.
I have used Power Query (a free add-in from Microsoft for Excel 2010 and higher versions) to generate your required Data Table and have then created a Pivot Table from it (just as you have).
Hope this helps.
Thanks.
Can you tell me the steps, what you have done to get the distinct pack size from from data through power Query.
Hi,
Download and install Power Query from this link - http://www.microsoft.com/en-in/download/details.aspx?id=39379.
Once downloaded and installed, open my file and select any cell in the Data Table of my solution worksheet. Hover your muse over the Table1 in the Workbook Queries window on the right and click on Edit. You will see my "Steps" in the "Applied Steps" window at the right.
Hope this helps.
Thanks. It solved my problem.
You are welcome.