Copy conditional existing data

RPeeters

Input:

CountryCode Percentages

40 10

240 20

300 30

Goal:

Copy the rows with 240 and 300, duplicate them with a new country code.

240 should be duplicated into 250 with the same percentage.

240 should also be duplicated into 260 with the same percentage.

Output:

CountryCode Percentages

40 10

240 20

250 20

260 20

How I do it:

input --- split into three lines, one original, one formula for the 250, one formula for the 260 --- union back together --- unique tool to avoid duplicates --- output

Formula:

if [CountryID] = 240 then 250 else [CountryID] endif

OR I CAN USE:

Switch([CountryID], [CountryID],240,250)

Questions:

1. The formula part copies also existing rows, meaning after the union I have three rows for 40 10 and causing me to use a unique tool aterwards. Can I avoid this? Somehow not use else [CountryID] in the IF formula (or the default value in the Switch formula, [CountryID]).

2. What is better, switch of if?

3. Is there an easier way to do this without having to use three lines and union?

Christina_H

I think all you need to do is add a filter before your formulas.  Filter out code 240, apply two formulas to change the county code, then union everything back together - both outputs from the filter for the original rows, and both formulas for the duplicates.  Doing it this way there's also no need for if or switch formulas, just change the value to 250/260.