Calculating Total Number of Training Hours

**amiesmithe** · 06-21-2015, 01:50 PM

Total Training hours are based on the topics for each role assigned. Each employee is assigned multiple roles and the roles can be listed in multiple topics, for example: Clerk and Supervisor are in Topic 1.

For example, an employee that is assigned roles as a Clerk, Supervisor, and Floor Wiper will take Topic 1, but the employee will take the topic 1 time not 3 times. The total credit hours for topic 1 should be .5 not 1.5 which is what you get if you add the topic 3 times. I have set up the workbook but I am unable to figure out the method to calculate the number of training hours without each topic being counted more than once when multiple roles contain the same topics.

**oeldere** · 06-21-2015, 02:03 PM

What should be the result.

Please add it manualy in your file, and mark the related cells e.g. with the same color.

**amiesmithe** · 06-21-2015, 04:38 PM

Here is an updated workbook explaining what I am trying to calculate

**Toonies** · 06-21-2015, 04:40 PM

I've found this it may help you?

http://stackoverflow.com/questions/1...-unique-values

**JohnTopley** · 06-22-2015, 05:32 AM

My own view is that this would need to done using VBA as there are very complex relationships i.e checking the multiple combinations of roles and topics.

**JohnTopley** · 06-22-2015, 11:14 AM

I have put together a VBA based solution: I have not checked all the results but first agrees with your expected result.

If you are to use this solution it will need refining to make it more generic (my VBA is very rusty!)