appending fixed rows after unique combination of 2 columns

[srk0609]

I have a dataset and I want to add redundant data to it based on certain condition and also the data in columns seg1, Ledger, geo for new data rows should be coming from existing data.

 If Ledger field doesn’t have secondary in it Then 3 rows highlighted in yellow should be appended after all rows associated to a certain combination of seg1 and value in ledger

Example value in seg1 column is 1234 and value in ledger is 'abc.tz', for this combination there are 5 rows of data. The value in ledger column doesn’t have secondary in it hence there should 3 new lines of data added after 5 rows of exiting data.

The values in column seg1, ledger, geo should be coming from existing data

If Ledger field has value secondary in it Then 3 rows highlighted in green should be appended after all rows associated to a certain combination of seg1 and value in ledger

Example value in seg1 column is 5678 and value in ledger is 'abc.ir.irs.secondary' and for this combination there are 5 rows of data. The value in ledger column has secondary in it hence there should 3 new lines of data added after 5 rows of exiting data.

The values in column seg1, ledger, geo should be coming from existing data

There are 5 unique combinations for column seg1 and Ledger so there should 3 rows of data after each combination and we should populate the data based on whether secondary is present in Ledger or not

Seg1	Ledger
1234	abc.tz
5678	abc.ir
8911	abc.cy
1234	abc.tz.tzs.secondary
5678	abc.ir.irs.secondary

I’m attaching the dataset. There are 2 sheets in it.

Input - has existing data

Sheet 2 – data that needs to be added
output - final output

Here is how the final data should look like(Yellow and green color are only for representation purpose color is not needed in final output)

 Can someone please explain and provide a workflow to achieve this? Thank you

[Qiu]

@srk0609 
If I understand your intention correctly.
I used the Union tool after seperating the rows with Tile tool.

[srk0609]

Hi Qiu, the values in the rows that are appended are incorrect the values of columns seg1,Ledger,Geo should not be constant they should change based on seg1, Ledger, Geo combination that is present in above rows

This the expected o/p the values of column seg1, Ledger, Geo for new rows should be coming from above rows

This is the output you provided where the values for col seg1, Ledger, Geo are not changing they are just constant

I hope I'm able to explain my issue clearly.