Need help creating new rows based on criteria

[RCern]

I have a data set that contains a list of customers that have different "Tiers".   I usually sort the list by Customer Number and Tier to group like items.  I need to create a workflow that will insert two new rows when the Tier changes from one row to the next.  I mocked up a dummy file (attached) and highlighted the new fields that I want to created based on tier changes for each customer.  I'm sure I use the "Generate Rows" tool, but I'm not sure how to implement.  Can anyone help?

[Brankl]

How many tiers are there? My first thought is to filter down to each tier separately and add two rows to the bottom of each table before unioning them back together. If there are a ton of tiers, you could use a macro instead of manual filters.

[ChrisTX]

See if the attached workflow helps

 

Chris

[cjaneczko]

Another solution.

[CoG]

Another another solution:

[ChrisTX]

@CoG in the Sort tool, sorting by Customer Number and Tier doesn't guarantee that the two New rows will be placed at the bottom of the group.

The sort order may be correct today, but may not be correct after a software version update, or a change to the AMP engine, or some other future software change.

[CoG]

@ChrisTX- is absolutely correct. Here is another x3 version if the order is critical:

[RCern]

@ChrisTX @CoG Thank you both so much.  Sorry for the delay, but the holidays had me off of work.  I implemented the proposed workflow, thanks so much.   A couple of changes have been requested.  Now, I am being asked that for all Plan Codes, create one summary row for each unique combination of Customer Number, Billing Location, Class Number, Plan Code, Plan Choice Coverage Tier, Month and Adjustment Indicator.   

Issues:

• I think I was able to do that with the attached workflow, however, in the summary lines some of the fields are not being populated like in the first 8 columns. 
• For the summary rows, they would like to sum the Benefit Covered Payroll, Gross Premium Amount (Where the adjustment indicator = "n", Withheld Admin Fees, Withheld commissions, premium adjustments, net premium remittance for the lines above the summary line.

Any advice or guidance would be greatly appreciated!

[ChrisTX]

@RCern the YXMD file you shared does not include the input file "Newtest - Copy.xlsx"

Please use the menu option "Options > Export workflow" to include the input file, then share the YXZP  file

[RCern]

@ChrisTX  Is this better?