Value repeating, keeping only one rest returns 0

[max_hfr]

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!

[PhilipMannering]

Hi Max,

Something like this?

[AbhilashR]

@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.

[max_hfr]

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

[AbhilashR · Accepted solution]

@max_hfr - does something like the attached approach work?