Multi row to apply values if zero "0"

Question

Dear all,

I have the following data set and I am trying to apply the values for the same ID if the cell is 0. This happens across all my dataset but just wanted to see if this case works so that then I can apply it to all.

IDCharge158700186331470158700186331470158700186331470158700186331470158700186331473816158700186331473816158700186331473816158700186331473816158700186331473816158700186331473816

Pretty much I want the same charge across all rows, as these have more columns that make them not "unique".

I did the following multi-row formula but am getting something wrong:

IF [Row-1:ID] = [ID] AND [Charge] = 0
THEN [Row-1:Charge]
ELSE [Charge]
ENDIF

Maybe there is a simpler way to do this, but I have the tendency to overcomplicate things.

Thank you all in advance for your time and assistance!

Edit: I cannot use:

[ROW+1:ID]

as it would grab the charge for a different ID.

Best,

Juan1

Book1.xlsx

PhilipL · Accepted Answer

@juan1

Hi Juan,

It looks like some of your records have nothing but 0 charges for some of your IDs, so not sure what you want to do there. For those that have transferable values I went with a different approach - join your 0 charge records back to the non-0 records and pass the non-0 charge value. I did it with a Formula copy of the fields but a Select with a field selection/rename could work too.

Check out the attached example.

Thanks,

Phil

DanM · Accepted Answer

You'll want to Sort the data by ID Ascending and Charge Descending.

Then in a multi-row formula field, Choose the Charge field and group by the ID. then use the expression If [Charge]=0 then [Row-1:Charge] else [Charge] endif

You only have to IDs in the data that have a 0 and another #. All the rest are greater than one or have 0 for the all the values.