Multi Field formula help

[veekay]

Hi ,

 I am trying to add a column in which value of the rows would depend on one of the variables . I have attached a sheet wherein I want the final result as column H.  I have managed to get column G using a multi field formula but am unable to get Column H. The value of column H depend on the following :

- the reservation id is the same and total revenue of Hotel Code 'Package' >0 then it is a "Paid Package" 

- the reservation id is the same and total revenue of Hotel Code 'Package' =0 then it is a "Free Package" 

- There is no Hotel Code 'Package' then it is 'No Package'

I tried to use the multiplied formula with logic grouped by Reservation id, as below but it seems to change only the row+1 to Paid package, for eg. and the rows further remain with the same value.

 IF [Reservation ID] = [Row-1:Reservation ID] AND [Row-1:Package Detail]="Paid Package" THEN "Paid Package" ELSE [Package Detail] ENDIF

Please guide if there is a better way of doing it

Thanks

[LordNeilLord · Accepted solution]

Hey,

I think there is an easier way of doing this:

• Using Summarize, sum the total by Reservation ID and Hotel Code, then filter on "Package" only.
• Add in your logic as above using the formula tool
• Join this back to your original dataset
• Anything that comes out of the left side of the join is "no package"
• Union the left & join back together
• 

[veekay]

Thank you @LordNeilLord. It is exactly what I was looking for.