Append rows based on unique combination of values in column

[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 and values for seg1, Ledger, geo column should be coming from existing data

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.

Sheet 1 - has existing data

Sheet 2 – Redundant data that needs to be added.

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

[OTrieger · Accepted solution]

@srk0609 
One way will be by using the Reporting tools as you will need to have the colour in your table. So the main table you just will use a normal table and for the appended fields you will need to create a condition and then send the data to 2 different Table tools, one that produce yellow highlight and one that provide a green highlight. Use a union tool to join all tables and then send them into a Layout tool to append the highlighted tables under the original one. Then render it.

[srk0609]

I used color so that it is easy to explain I don't need color in the final output