Justplainj
Board Regular
- Joined
- Apr 15, 2021
- Messages
- 50
- Office Version
- 365
- Platform
- Windows
Good day All,
I am not quite sure ow to explain this (struggling with the post/trhead subject name also).
I have a table of training data (peoples names that did training) from a new system we use with the amount of hours the employee did as well as the trainer names.
However when there is more than one trainer, the entire row is duplicated with the second trainers name in the trainer name column.
Example:
Below is an example of how the table looks like.
First row is headers.
Second row is the training Sara McDonald did with 2 hours training and the trainer is Peter Ros.
Third row is a duplicate row, with the second trainers name Laura Kay.
Important to take note of
1. The trainers are in the same session giving training.
2. There can sometimes be only 1 trainer, other times 2 or even more (max I have seen is 4 trainers duplicating the rows 4 times).
3. Due to the duplication of rows if there is more than one trainer it means simply doing a sum function on the training hours or pivoting the data etc to get statistical info provides incorrect stats.
Is there a way to quickly get the data to display as per the following to only show the trainee information once with the trainer names in columns next to each other, either via features in excel, functions/formulas or vba?
I cannot simply remove the trainee names and remove duplicates because I require the trainer names to calculate the hourly rate of the trainee as that and allocate costs appropriately depending on how many hours they spent training, etc., and doing it manually is a pain seeing in the first month we have over 10,000 rows of data already.
Thank you in advance.
J
I am not quite sure ow to explain this (struggling with the post/trhead subject name also).
I have a table of training data (peoples names that did training) from a new system we use with the amount of hours the employee did as well as the trainer names.
However when there is more than one trainer, the entire row is duplicated with the second trainers name in the trainer name column.
Example:
Below is an example of how the table looks like.
First row is headers.
Second row is the training Sara McDonald did with 2 hours training and the trainer is Peter Ros.
Third row is a duplicate row, with the second trainers name Laura Kay.
Important to take note of
1. The trainers are in the same session giving training.
2. There can sometimes be only 1 trainer, other times 2 or even more (max I have seen is 4 trainers duplicating the rows 4 times).
3. Due to the duplication of rows if there is more than one trainer it means simply doing a sum function on the training hours or pivoting the data etc to get statistical info provides incorrect stats.
| Trainee Name | Trainee Surname | Training Title | Training Hours | Trainer Name |
| Sara | McDonald | Excel Training | 2 | Peter Ros |
| Sara | McDonald | Excel Training | 2 | Laura Kay |
| Jim | Smith | Excel Training | 2 | Peter Ross |
| Jim | Smith | Excel Training | 2 | Laura Kay |
Is there a way to quickly get the data to display as per the following to only show the trainee information once with the trainer names in columns next to each other, either via features in excel, functions/formulas or vba?
I cannot simply remove the trainee names and remove duplicates because I require the trainer names to calculate the hourly rate of the trainee as that and allocate costs appropriately depending on how many hours they spent training, etc., and doing it manually is a pain seeing in the first month we have over 10,000 rows of data already.
| Trainee Name | Trainee Surname | Training Title | Training Hours | Trainer Name 1 | Trainer Name 2 |
| Sara | McDonald | Excel Training | 2 | Peter Ros | Laura Kay |
| Jim | Smith | Excel Training | 2 | Peter Ros | Laura Kay |
Thank you in advance.
J
