Hi Guys,
I am struggling with some data transfromation...
I have made a sample workbook so it is easier to explain. I put a screenshot aswell of the file so you can directly use it.
Story: Imagine I am trying to follow some sales representatives' work. They enter in a software every single interaction they have with one of our customers and if they go on holliday they'll have to enter the days they went on hollidays.
Rows:
Each row contains etither a visit of a client, a day of holliday or both
When there is a full day of holliday it will return 1 and therefore there can be no client interaction on that day and no unique ID(client ID) (ROW 9)
The problem occurs when our sales representative enters half a day of hollidays (0,5) in the morning and then interactions in the afternoon (in this case 4 interactions on the 28th). In that case the line repeats 0,5 days 4 times and therefore when I sum it up it will return 2 days instead of 0,5
What I need is to be able to keep only one time the 0,5 and return 0 for the other 0,5
Or
Divide the 0,5 per the number of interactions on the same day (in this case 0,5 / 4) so 0,125 for every row on the 28th
Here is a screenshot of both possible results I would like to have:
Thank you guys in advance!
