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!
Solved! Go to Solution.
@max_hfr - building on @PhilipMannering 's approach, you could use a multi-row formula to retain the first On Holiday and convert others to 0.
Hope this helps.
Hi,
First of all thank you very much to you both that was perfect.
However after further investigation I realized that if I have to make filters on customers further on I will loose the 0,5 for the rows where I deleted it.
was wondering if there is a way to actually add a row so the result would look like that