Formula that tests the sum of 2 fields against all fields of another column

[AkisM]

I have a table that looks like this:

Amount          Totals       

234.5              4214              

400                 266.3

300                 351

234.5              235

[Null]               377

[Null]               388

[Null]               634.5

[Null]               123

[Null]               534.5

And after applying a certain formula(s), I want it to look like this:

Amount          Totals           MatchesAsSum             ReOrdered

234.5              4214            No                                 634.5

400                 266.3           No                                 [Null]

300                 351              No                                 534.5

234.5              235              No                                 [Null]

[Null]               377              No                                 [Null]

[Null]               388              No                                 [Null]

[Null]               634.5           Yes                               [Null]

[Null]               123              No                                [Null]

[Null]               534.5           Yes                               [Null]

Basically what the formula is supposed to be doing is this:

If CurrentRow[Amount]+NextRow[Amount] is equal to any of the amounts in [Totals], then put the value of that [Total] to CurrentRow[ReOrdered], and put "YES" under [MatchesAsSum], at the matching row of [Totals]. This will be done for Row0+Row1, Row1+Row2, Row2+Row3, etc.

Is this possible? Thanks in advance.

[JosephSerpis]

Hi @AkisM  Yes that is possible using the Multi-Row Formula tool.

[AkisM]

Hi @JosephSerpis ,

I thought as much. Basically I'm looking for help with the expression itself. I was thinking something along the lines of:

(Field to update set as ReOrdered)

If Contains([Totals], (To.String(ToNumber(Row0[Amount])+ToNumber(Row+1[Amount])))) then [Totals] else Null() endif

but apparently I can't put a formula inside contains. And I still don't know how to tell it to put "YES" at the row of the matching value, in the [MatchesAsSum] column. Because I have no way of knowing where the match occurred. And I don't know if the multi row formula tool lets you dynamically change which field will be updated. For example I don't think the tool lets you say Row(variable number)[MatchesAsSum] = "YES".

Thanks.

[JosephSerpis · Accepted solution]

Hi @AkisM 

                  I created a workflow that creates the output you describe in your post. Essentially I use the multirow to create a total field then use the append to check across every total amounts then filter on those that do and join back to the original data.