Hi all, I'm new to this community. Please kindly help to advise on my problem. My set of data is as follows: NoDateCodeClient codeNameJE descriptionAccount codeDebitCredit105/01/2022BN43/20NC60001Mr. AExpense64253.000.0000105/01/2022BN43/20NC60001Mr. AExpense1121103.000.0007614/01/2022BN44/20NC00001Mr. BFA purchase24111300.000.000.00007614/01/2022BN44/20NC00001Mr. BFA purchase6428311.00007614/01/2022BN44/20NC00001Mr. BFA purchase112110300.000.011.000 I would like my output as follows: NoDateCodeClient codeNameJE descriptionAccount codeOffset accountDebitCredit105/01/2022BN43/20NC60001Mr. AExpense6425112113.000.0000105/01/2022BN43/20NC60001Mr. AExpense11211642503.000.0007614/01/2022BN44/20NC00001Mr. BFA purchase2411111211300.000.000.00007614/01/2022BN44/20NC00001Mr. BFA purchase642831121111.00007614/01/2022BN44/20NC00001Mr. BFA purchase11211241110300.000.000.0007614/01/2022BN44/20NC00001Mr. BFA purchase1121164283011.000 Thank you very much! Best regards,

Hi @Vu_Doan ! Really sorry for the late reply. I was sent abroad for a few weeks for work and had to rush for several trips in succession. Please find the new workflow as follows. Try it and let me know. Best, Cal Vu_Doan_WF.yxzp

Hi @Vu_Doan, Thanks for the sample dataset. My apologies, I was on holiday the past week and I went abroad to solve Alteryx use-cases in person. I'll look into your issue again this week when time permits, but I think you can use my workflow still and make some adjustments to meet those exception cases. Regards, Cal

Hi caltang, I've tried your flow. I think that you're really close, but there seems to be a bit issue. I noticed that some entries are not transformed correctly as belows: Input Output I also attached the raw data. Do you have any further suggestions? Sample data.xlsx

How to create a column for offsetting accounts

Vu_Doan

Hi all,

I'm new to this community. Please kindly help to advise on my problem. My set of data is as follows:

No	Date	Code	Client code	Name	JE description	Account code	Debit	Credit
1	05/01/2022	BN43/20	NC60001	Mr. A	Expense	6425	3.000.000	0
1	05/01/2022	BN43/20	NC60001	Mr. A	Expense	11211	0	3.000.000
76	14/01/2022	BN44/20	NC00001	Mr. B	FA purchase	24111	300.000.000.000	0
76	14/01/2022	BN44/20	NC00001	Mr. B	FA purchase	64283	11.000	0
76	14/01/2022	BN44/20	NC00001	Mr. B	FA purchase	11211	0	300.000.011.000

I would like my output as follows:

No	Date	Code	Client code	Name	JE description	Account code	Offset account	Debit	Credit
1	05/01/2022	BN43/20	NC60001	Mr. A	Expense	6425	11211	3.000.000	0
1	05/01/2022	BN43/20	NC60001	Mr. A	Expense	11211	6425	0	3.000.000
76	14/01/2022	BN44/20	NC00001	Mr. B	FA purchase	24111	11211	300.000.000.000	0
76	14/01/2022	BN44/20	NC00001	Mr. B	FA purchase	64283	11211	11.000	0
76	14/01/2022	BN44/20	NC00001	Mr. B	FA purchase	11211	24111	0	300.000.000.000
76	14/01/2022	BN44/20	NC00001	Mr. B	FA purchase	11211	64283	0	11.000