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

Insert Blank Row.xlsx

Workflow

Accepted answers

ccanterman

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

Sort then add a Record ID as a unique identifier
Use the Unique Tool to identify duplicates based on all values (except Record ID) being the same
Merged cells column - For the Unique values (see 2nd path down), create a column called "Merged cells" with the Absolute Amount
For the Duplicate values (see 3rd path down), do not create the "Absolute Amount" column
Nulls - For the Duplicate values (see 4th path down), replace all values except the Record IDs with nulls
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
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

New Workflow2.yxzp

All comments

ccanterman

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

Sort then add a Record ID as a unique identifier
Use the Unique Tool to identify duplicates based on all values (except Record ID) being the same
Merged cells column - For the Unique values (see 2nd path down), create a column called "Merged cells" with the Absolute Amount
For the Duplicate values (see 3rd path down), do not create the "Absolute Amount" column
Nulls - For the Duplicate values (see 4th path down), replace all values except the Record IDs with nulls
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
Union everything together and Sort by Record ID

New Workflow2.yxzp

Bren_Spill2

@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

Insert Blank Row.yxzp

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!

Sample Workflow.yxmd

ccanterman

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703