I am using Excel 2007
Hi everyone,
So here is my problem I have 50 000+ rows that covers fiscal years from 2006 to 2014 and I want to calculate how many unique clients are there per asset type per fiscal year. In addition, the results table needs to adjust to filters i would use on the raw data..
As:
2007 2008 2008 Commodity 45 FX Other other
Here is a sample file: SampleData.xls
Last edited by azogz; 12-09-2013 at 05:01 PM.
I tried using PivotTable but it cannot differentiate unique values..
Anyone would have a suggestion on how to approach this problem?
try with helper column and then use PT:
Never use Merged Cells in Excel
Thanks that worked perfectly! Now I'm struggling over how to find cross-sell client. I have to identify the number of clients that does business with >=2 asset type but still having the fiscal year constraint.
check does this helps:
No it doesn't work. It gives me a number that is way too high. To recap, I want to count the number of unique clients that does business with more >=2 asset class. So i would like count only clients that has 2 different asset class or more in the same fiscal year.
Ok so In semi-english its:
if( count of unique desk for the client for the fiscal year> 1,1,0)
Last edited by azogz; 12-11-2013 at 02:50 PM.
This is what table return:
All assets are same in example.
Please Login or Register to view this content.
So, there are two Fiscal Years:
2013 returns 1 because there is Client A that has 2 trades for same asset (B has one so it's 0).
2014 returns 3 because there are clients A, B and C with 2 trades each. If A had 10 more trades (for same asset), result would still be 3 because it count how many clients achieve 2 or more trades (and not how many trades there are).
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks