Hi everyone,
I'm trying to figure out this multi-field formula -- in my data, I have a set of journal entries (over 50,000 entries) that are booked between US and European business units. However, in order to analyze these correctly, I need to make sure that if an entry is booked between both US and Europe, that it get's bundled with US entries.
I've attached a screenshot of sample data of what it looks like currently and what I want it to look like.
Below is the formula that I'm using:
IF [Journal ID] = [Row-1:Journal ID] AND [Unit2] !=[Row-1:Unit2] THEN "US" ELSE [Unit2] ENDIF
With the formula, I was trying to say that if the current row Journal ID matches the previous row Journal ID and the current row Unit2 (US or Europe) doesn't match the previous row Unit2, then to replace what is in Unit2 with US otherwise leave the cell as is. But the logic isn't working in the workflow the way I want.
I've also tried a Join and Join Multiple, but that duplicates entries and though I could take those out using the Unique tool, there are some duplicates within the source data that are needed, so I can't use the unique tool and keep the ones that are needed.
If anyone has any insight that would be super helpful! Thanks!
