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
Hello Carlos,
There is no file attached. Try to upload it again.
Br,
Anders
That might help... 🙂
Hi Carlos,
What version of Excel do you have?
Also, please clarify what you consider a duplicate. If we take the example of Nuno Nogueira who has 6 entries in the RawCertData, two with version 9 and 4 with version 10, which records would you count?
Mynda
Hi, i have Excel for Mac V16.43(20110804)
And the answer to my other question above: "Also, please clarify what you consider a duplicate. If we take the example of Nuno Nogueira who has 6 entries in the RawCertData, two with version 9 and 4 with version 10, which records would you count?"
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 | [email protected] | Amer | Specialist | PC | 9 | |
| Nuno Nogueira | [email protected] | Amer | Professional | PC | 9 | |
| Nuno Nogueira | [email protected] | Amer | Professional | PC | 10 | X |
| Nuno Nogueira | [email protected] | Amer | Ace Developer | PC | 10 | |
| Nuno Nogueira | [email protected] | Amer | Assoc. Developer | IS | 10 | |
| Nuno Nogueira | [email protected] | 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
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
