Insert Blank Row if conditions matched

[bubblyjai1982]

Hi All,

Please help me on below issue !!

I'm able to insert the blank row using Multi Row formula and then generate rows and Multi Field row tool if one condition met but not able to capture all the condition(merge when amount is same).

Condition: Insert row if below conditions are in-line

Account = Account

Counterparty = Counterparty

Payment = Payment date

Currency = Currency

And merge(not add) the Absolute amount which has same value and along with above condition met and then split and insert one blank row. For example I have attached input and output for your reference

Many Thanks

[Carolyn · Accepted solution]

I feel like there's going to be a more efficient way to do this but this is what I've come up with:

1. Sort then add a Record ID as a unique identifier
2. Use the Unique Tool to identify duplicates based on all values (except Record ID) being the same
3. Merged cells column - For the Unique values (see 2nd path down), create a column called "Merged cells" with the Absolute Amount
4. For the Duplicate values (see 3rd path down), do not create the "Absolute Amount" column
5. Nulls - For the Duplicate values (see 4th path down), replace all values except the Record IDs with nulls
6. Nulls - For the Unique values (see 1st path down), if the Record IDs are consecutive, replaces all values except the Record IDs with nulls. If the Record IDs are not consecutive, don't do anything
7. Union everything together and Sort by Record ID

The only thing is that the Merged Cells aren't actually merged as you showed in your Excel output file. I'm not sure how much of an issue that will be and/or if the objective was to just have the Absolute Amt listed once

 

 

[Bren_Spill]

@bubblyjai1982 - I went with a batch macro approach. One issue is within the macro I use a text input tool to create the blank row as you will see, but if your field names are changing this will need to be made dynamic.

If you aren't sure about how the macro is set up let me know.

Thanks,

Brendan

[bubblyjai1982]

Thank you Carolyn and Bren, it is help full !!

[CoG]

Here is another fairly efficient way to incorporate null rows:

Essentially just group on all fields that you want to form a group (what the [__Key__] field is for). Then you only need to Union that back into the original dataset and Sort into appropriate format.

Hope this helps and Happy Solving!

[Carolyn]

@CoG - That's so pretty! I knew there was a better way than mine - yours is great!