Log In
Register
Home
Active Member
November 12, 2020
Offline
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
Offline
Hello Carlos,
There is no file attached. Try to upload it again.
Br,
Anders
Active Member
November 12, 2020
Offline
That might help... 🙂
July 16, 2010
Offline
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
Active Member
November 12, 2020
Offline
Hi, i have Excel for Mac V16.43(20110804)
July 16, 2010
Offline
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?"
Active Member
November 12, 2020
Offline
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
Offline
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)
