HI experts

I have been trying to solve since couple of month but no solution yet found, i need your help on this

I have raw data with 4 sheet, i want find which order are accountable or not.

Criteria1#

 if Order Number is 1 time and If Acc type is cancel no need accountable Leave it blank.

Criteria2#

if Order Number repeat twice ,If Acc type is cancel One Code should be accountable depends upon thesis table order flow,Another is unaccountable.

if Order Number repeat twice ,If Acc type is NEW OR ADD two or one Code should be accountable depends upon thesis table order flow,Another is unaccountable.

Criteria3#

if Order Number repeat more than twice  like 3,4,5,6.....44 ,If Acc type is cancel One Code should be accountable depends upon thesis table order flow,rest of all is unaccountable.      

if Order Number repeat more than twice  like 3,4,5,6.....44 ,If Acc type is NEW OR ADD two or one Code should be accountable depends upon thesis table order flow,rest of all is unaccountable. 

i attached working sheet with explanation.

Thanks and regards