
Active Member

November 12, 2020

Hi,
I have this project report on which I need to count the number of people trained by region, version, and type of certification, the problem I have is that if the same person is certified in both versions I only want to coun it in the latest version. But I am stuck with how not to count it on version 9 if certified on both.
The sheet called specialization data is the report, the manual results are what it should actually come up with

VIP

Trusted Members

December 7, 2016


Active Member

November 12, 2020

hi,
so in the case of nuno, we look at product,level and version.
Name | USER REGION | Level | NEW PRODUCT | NEW PRODUCT VERSION | We count this | |
Nuno Nogueira | nnogueira@sample.com | Amer | Specialist | PC | 9 | |
Nuno Nogueira | nnogueira@sample.com | Amer | Professional | PC | 9 | |
Nuno Nogueira | nnogueira@sample.com | Amer | Professional | PC | 10 | X |
Nuno Nogueira | nnogueira@sample.com | Amer | Ace Developer | PC | 10 | |
Nuno Nogueira | nnogueira@sample.com | Amer | Assoc. Developer | IS | 10 | |
Nuno Nogueira | nnogueira@sample.com | Amer | Specialist | PC | 10 | X |
So nuno, should be counted toward the Specialis/Amer/PC on version 10 and not on version 9, as we only want to count the most recent version when the resources has both the old and the new version. He should also be counted on the Professional/amer/PC on version 10 and not in the version 9 line.
Specialization | ||||||||||||
Specialist Cert Requirement | Professional Cert Requirement | |||||||||||
Required | 50 | 10 | 20 | 10 | Required | 5 | 1 | 2 | 1 | |||
PC | Region | AMER | APAC | EMEA | LATAM | Region | AMER | APAC | EMEA | LATAM | ||
9.0 Achieved | 46 | 0 | 3 | 0 | 9.0 Achieved | 3 | 0 | 0 | 0 | |||
10.0 Achieved | 81 | 9 | 1 | 1 | 10.0 Achieved | 9 | 0 | 0 | 0 | |||
Total Achieved | 127 | 9 | 4 | 1 | Total Achieved | 12 | 0 | 0 | 0 | |||
Gap | 77 | -1 | -16 | -9 | Gap | 7 | -1 | -2 | -1 | |||
let me know if this makes it more clear.
Regards,
Carlos


July 16, 2010

Hi Carlos,
Thanks for clarifying. You can use Power Query to remove the duplicates, then you can use a PivotTable to summarise the counts. See file attached:
Duplicates Removed sheet contains the query output
Manual Results sheet has the PivotTables alongside your examples. Note there are some discrepancies in your manual counts compared to the PivotTable.
I hope that helps.
Mynda
1 Guest(s)
