IF G/L = x then subtotal y (different column)

davidlocke

I'm trying to replace a value in a column with a subtotal value on a different row and a different column.

Example of what my Alteryx data is this:

Entities	COGS Category	G/L AC	G/L Desc	P&L Recon Subtotal	GL TB Subtotal	Difference	COGS Reclass	COGS Reclass Type Descpription
1000	Reclass	558990	Settlement Account			0.00	0.00	None
1000	COGS			564176142.15	535583667.15	28592475.00	0.00
1000	SG&A	899120	Settle Standing Orde			0.00	0.00	Reclass is amt per P&L recon not part of CoGS on T/B.
1000	VAR/Allocation			-23922780.98	-8534034.08	-15388746.90	0.00
1100	COGS			477029485.23	477310624.35	-281139.12	0.00
1100	Reclass	558990	Settlement Account			0.00	0.00	None
1100	SG&A	899120	Settle Standing Orde			0.00	0.00	Reclass is amt per P&L recon not part of CoGS on T/B.
1100	VAR/Allocation			-11937052.07	8622657.49	-20559709.56	0.00

The end result would apply to the COGS reclass column of account 558900 by entity the difference column of the COGS subtotal row; and apply to the COGS Reclass column of account 899120 by entity the difference column of the equation of the  inverse (-) of the VAR subtotal row combined with the inverse (-)  of the COGS subtotal row.

Entities	COGS Category	G/L AC	G/L Desc	P&L Recon Subtotal	GL TB Subtotal	Difference	COGS Reclass
1000	Reclass	558990	Settlement Account				28592475.00
1000	SG&A	899120	Settle Standing Orde				-13203728.10
1100	Reclass	558990	Settlement Account				-281139.12
1100	SG&A	899120	Settle Standing Orde				20840848.68

TonyA

Here's one approach using a Multi-Row formula tool. I put the rows in a consistent order so I could use row offsets for calculations (setting a Row ID first so I could restore the original row order). Then I did the multi-row calcs, got rid of rows I didn't need, resorted the rows and dropped unneeded columns.

MultiRow Calcs.yxmd