Allocating rows to new columns when identifier is only in one row

[N89]

Hi all

I am trying to work out a way to allocate totals to a particular column based on an idenitifier existing in anoher column. I have included the input data in the attached and shown what i would like the output to look like. 

If in Column C there is the identifier 804020, then you take its value in Column A (1400022727) and for all of  those you put the number from Column D in Clear 3. 

If in Column C there is the identifier 259440, then you take its value in Column A (1400023144 and 1400023309) and for all of  those you put the number from Column D in Clear 1.

If in Column C there is the identifier 259430, then you take its value in Column A (1400023482) and for all of  those you put the number from Column D in Clear 12.

If none of the identifiers apply you take all the values from Column D and put them in "Stays:.

[Qiu · Accepted solution]

@N89 
Just manage to get similar results as your output. Need to do something about the formatting, though.

[grazitti_sapna]

Hi @N89 , give this a try.

Thanks.

[N89]

Thanks @grazitti_sapna - my problem is in my actual data set i have thousands of different Column A numbers so i can't input in the formula the wya you have done. I will try @Qiu solution with a bit of a workflow in the beginning to make my data look like the second input too without manually doing that. 

[Qiu]

@N89 
Glad to be helpful for a little.
Appreciate if you would mark it as accepted.😁