I have a set of data, if you see the data set, because of account code for row 2 and row 3 are not the same so I can't summarize it. When you look at first row, the amount is submission of the 2nd and 3rd row. I want to split the first row into 2 rows and the amount is totally the opposite amount of 2nd and 3rd row. And also, the first row "T0 Fund analysis code" has to be the same compare to second row and third row "Fund". Please see below for the input data Account CodeT0 Fund Analysis CodeFundPeriodSum_Base Amount3361010000GTNPPAHITPALIF2020007-2495293.233361270000ITPALIFGTNPPAH20200072335158.253361280000ITPALIFGTNPPAH2020007160134.98 i want to have the result show below Account CodeT0 Fund Analysis CodeFundPeriodSum_Base Amount3361010000GTNPPAHITPALIF2020007-2335158.253361010000GTNPPAHITPALIF2020007-160134.983361270000ITPALIFGTNPPAH20200072335158.253361280000ITPALIFGTNPPAH2020007160134.98

How to create an offsetting pair?

I have a set of data, if you see the data set, because of account code for row 2 and row 3 are not the same so I can't summarize it. When you look at first row, the amount is submission of the 2nd and 3rd row. I want to split the first row into 2 rows and the amount is totally the opposite amount of 2nd and 3rd row. And also, the first row "T0 Fund analysis code" has to be the same compare to second row and third row "Fund". Please see below for the input data

Account Code	T0 Fund Analysis Code	Fund	Period	Sum_Base Amount
3361010000	GTNPPAH	ITPALIF	2020007	-2495293.23
3361270000	ITPALIF	GTNPPAH	2020007	2335158.25
3361280000	ITPALIF	GTNPPAH	2020007	160134.98

i want to have the result show below

Account Code	T0 Fund Analysis Code	Fund	Period	Sum_Base Amount
3361010000	GTNPPAH	ITPALIF	2020007	-2335158.25
3361010000	GTNPPAH	ITPALIF	2020007	-160134.98
3361270000	ITPALIF	GTNPPAH	2020007	2335158.25
3361280000	ITPALIF	GTNPPAH	2020007	160134.98

Preparation

Accepted answers

Qiu

@Haokun
I really should get paid for this.😁

1116-Haokun.yxmd

All comments

Qiu

@Haokun
Is this what you need?

1116-Haokun.yxmd

Haokun

Hey @Qiu

Thanks so much for the effort. However, I think that is not all the scenarios covered. I created more scenarios. you can see below.

Account Code	T0 Fund Analysis Code	Fund	Period	Sum_Base Amount
3361010000	GTNPPAH	ITPALIF	2020007	-2495293.23
3361270000	ITPALIF	GTNPPAH	2020007	2335158.25
3361280000	ITPALIF	GTNPPAH	2020007	160134.98
3361010000	ITNNPIA	ITPALIF	2020007	110464.95
3361211000	ITPALIF	ITNNPIA	2020007	-110464.95
3361010000	ITPAPWU	ITPALIF	2020007	6383227.99
3361011000	ITPAPWU	ITPALIF	2020007	1500
3361180000	ITPALIF	ITPAPWU	2020007	-6384727.99
3361010000	ITPAPLU	ITPALIF	2020007	1288142.9
3361010100	ITPAPLU	ITPALIF	2020007	-10000
3361170000	ITPALIF	ITPAPLU	2020007	-1278142.9

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280