Multi Row Formula Tool

[The_Voter]

Hello! i have encountered an issue where i need to get direction separated into Outbound and Inbound by the following logic:
OUT_RET_1 is sending money (Outbound) 5 and IN_RET_2 is receiving (Inbound) money 5. How to apply multi row formula tool in this situation if it's applicable.Please find attached file with sample data and expected output. thank you in advance!

[Emmanuel_G]

Hi @The_Voter ,

Do you want something like this ?

[mceleavey · Accepted solution]

Hi @The_Voter ,

this is not a task for the multi-row formula as that is specifically used to reference rows above and below the current line. You can learn more about that tool HERE.

This task can be completed by pivoting your data into rows using the delimiter in the DIRECTION column. The delimiter in this case is \sAND\s, that is a space followed by the word AND followed by another space. You can simply use this in the Text-to-Columns tool:

Ensure you have the "Skip empty columns" box checked.

This will split your data our into two rows, one for each of the directions.

You then simply apply the Sort tool to put it in the order you want:

I hope this helps,

M.

[FreeRangeDingo]

@mceleavey What is the \s doing in your delimiter?  What does that specify?  Thanks!

[binuacs]

@FreeRangeDingo \s is using as the delimiter for white space character. In the above example \sAND\s is equivalent to ' AND '

[mceleavey]

Hi @FreeRangeDingo ,

yeah, as @binuacs said it's the whitespace character, so in effect I'm using <space>AND<space> as the delimiter. This means I don't have to include another step to remove both the leading and trailing whitespace in the next step. Without it, you would get the following:

Regex is my friend.