Need help in coding

[taruna1986]

Hi Admin,

I have the attached data and I'm trying to find the correct macro coding for creating my journal. I want if the account number & company code subtotal are zero then the macro would display the account number, profit center, and company code with value in other columns for a journal. Please advise.

 

[Joe4]

I don't think your question makes much sense, in conjunction with the image you are showing.
Can you shows us both a BEFORE and AFTER image (what your original data looks like, and what you want your final result to look like)?

 

[taruna1986]

@Joe4 Sorry for not being clear in my request before. Please see attached the original and final results. I am looking for a macro that subtotal by account number & company code and if the amount is zero for the same then displays the columns given in the final results. Thanks for looking into this.

 

[Joe4]

OK, I am not understanding how you are arriving at your Final Results based on the original sample.
Why do you keep some records and dump others?

Perhaps you can walk us through the first few lines, and explain exactly how you arrive at the end result.
And explain why some records are left off altogether.

 

[taruna1986]

OK, I am not understanding how you are arriving at your Final Results based on the original sample.
Why do you keep some records and dump others?

Perhaps you can walk us through the first few lines, and explain exactly how you arrive at the end result.
And explain why some records are left off altogether.

@Joe4 These are the steps, I want to add to the macro coding:
Step1: Check the subtotal value of account number & company code
Step 2: If it is zero then display the columns given in the final results
Step 3: If it is not zero then don't add it to the final results
In the above snip, the final results only show data where the subtotal of account number & company code is getting zero.

 

[taruna1986]

@Joe4 Can you help me on this pls?

 

[Skyybot]

Wouldn't a simple filter work?

 

[Skyybot]

@taruna1986 , you started a conversation that I couldn't reply to. Filter is not a formula. Select Amount Column, click on Data, click Filter. Then click the new down arrow in the Amount Column. In the dropdown, select number filters, then click Less Than. Type 0 in the box, then Okay. Or click Less than or equal to and type 0 in the box.

 

[taruna1986]

Wouldn't a simple filter work?

@Skkybot

@taruna1986 , you started a conversation that I couldn't reply to. Filter is not a formula. Select Amount Column, click on Data, click Filter. Then click the new down arrow in the Amount Column. In the dropdown, select number filters, then click Less Than. Type 0 in the box, then Okay. Or click Less than or equal to and type 0 in the box.

It's not that easy, otherwise, I would have done it.

 

[Ezguy4u]

taruna let's keep the ball rolling, the question I have is, below should be your input and output. Now I believe by your description of what you want, shouldn't company code 300 account number 1110100031 should equal 0 but it's not in your final result. Is this by design or did you miss it? Anyway your problem is now in a usable form so we may get some more help. In the mean time I will try and come up with a macro.

1703022137069.xlsx
	A	B	C	D	E	F	G	H	I
1	Before: Original Data					After: Final results
2	Company code	Account Number	Amount	Profit Center		Company code	Account Number	Amount	Profit Center
3	100	1110100011	10,000	3000/P1200001		100	1110100011	10,000	3000/P1200001
4	100	1110100011	-10,000	3000/P6300001		100	1110100011	-10,000	3000/P6300001
5	100	1110100011	0	3000/P6700001		100	1110100011	0	3000/P6700001
6	100	1110100031	3,857	3000/P4050001		300	1110100011	6,000	3000/P4200001
7	100	1110100031	4,000	3000/P5000001		300	1110100011	-6,000	3000/P6600001
8	100	1110100031	3,800	3000/P5000023		200	1110100031	6,000	3000/P4150001
9	300	1110100011	6,000	3000/P4200001		200	1110100031	-6,000	3000/P5000002
10	300	1110100011	-6,000	3000/P6600001		200	1110100031	0	3000/P5000200
11	300	1110100031	10,000	3000/P3400001
12	300	1110100031	-10,000	3000/P4200001
13	300	1110100031	6,500	3000/P5000015
14	300	1110100031	3,800	3000/P5000202
15	200	1110100011	3,857	3000/P4150001
16	200	1110100011	4,000	3000/P6310001
17	200	1110100011	3,800	3000/P7000001
18	200	1110100031	6,000	3000/P4150001
19	200	1110100031	-6,000	3000/P5000002
20	200	1110100031	0	3000/P5000200
Data