VBA Coding or macro to pull out sum for a specific account and vendor

[dstepan]

Hello to all

I'm not sure how to approach this problem.

This first table is the end result I am looking for.

The data I pull down from my accounting system is in the second table below. It exports to excel 2016 as it is shown.
It is basically a GL detail report.
I am only concerned with 4 accounts and 2 customers: see the first table below.

Can anyone tell me if this is a feasible task? And if so, how I might begin to get the end results.

Any help is greatly appreciated.

Thank you

	A	B	C
1		SUNNY	SURGEON
2	4110-04	($227,084.80)	($195,873.43)
3	4112-04	($288,937.03)	($44,047.50)
4	4114-04	($5,478.29)	0
5	4108-02	0	0
6
7

<tbody>
</tbody>

	A	B	C	D	E	F	G
1		Date	Reference*	Source	Type	Cur	YTD
2		Hunt Ck
3	4108-02	SALES-S
4	Period:	2019-4	Begin Bal	($3,550.00)
5	30-Apr-19	G10747	PORA	AR Inv	($475.00)
6	30-Apr-19	G10747	PORA	AR Inv	($1,012.50)
7	($1,487.50)	Ending Bal	($5,037.50)
8	4108-04	SALES-ST
9	Period:	2019-4	Begin Bal	$0.00
10	00-Jan-00				$0.00
11	$0.00	Ending Bal	$0.00
12	4110-04	SALES-SV
13	Period:	2019-4	Beg Bal	($2,000,001.00)
14	09-Apr-19	G10701	TINKER	AR Inv	($16,312.50)
15	16-Apr-19	G10704	GERARD	AR Inv	($2,275.00)
16	16-Apr-19	G10705	SUNNY	AR Inv	($10,171.35)
17	25-Apr-19	G10706	SURGEON	AR Inv	($61,294.80)
18	29-Apr-19	G10720	AXIS	AR Inv	($10,000.00)
19	29-Apr-19	G10723	GERARD	AR Inv	($11,500.00)
20	29-Apr-19	G10724	PORA	AR Inv	($12,000.00)
21	29-Apr-19	G10725	SUNNY	AR Inv	($32,265.46)
22	29-Apr-19	G10726	SUNNY	AR Inv	($55,452.49)
23	29-Apr-19	G10728	SUNNY	AR Inv	($3,727.50)
24	30-Apr-19	G10729	APE	AR Inv	($32,300.00)
25	30-Apr-19	G10730	SURGEON	AR Inv	($3,409.63)
26	30-Apr-19	G10740	SUNNY	AR Inv	($108,450.00)
27	30-Apr-19	G10742	SUNNY	AR Inv	($9,716.50)
28	30-Apr-19	G10743	SUNNY	AR Inv	($1,551.50)
29	30-Apr-19	G10744	SUNNY	AR Inv	($5,750.00)
30	30-Apr-19	G10745	LACE	AR Inv	($119,800.00)
31	30-Apr-19	G10748	SURGEON	AR Inv	($71,169.00)
32	30-Apr-19	G10749	SURGEON	AR Inv	($60,000.00)
33	($627,145.73	Ending Bal	($2,687,217.73)
34	4112-04	SALES-P
35	Period:	2019-4	Beg Bal	($785,976.81)
36	21-Apr-19	G10709	SUNNY	AR Inv	($196,030.98)
37	30-Apr-19	G10739	SUNNY	AR Inv	($92,906.05)
38	30-Apr-19	G10741	SURGEON	AR Inv	($44,047.50)
39	($332,984.53)	Ending Bal	($1,118,961.34)
40	4114-04	SALES -P
41	Period:	2019-04	Beg Bal	($53,679.42)
42		G10700	GREEN	AR Inv	($4,545.00)
43		G10738	SUNNY	AR Inv	($5,478.29
44	($10,023.29)	Ending Bal	($63,702.71)

<tbody>
</tbody>

 

[kweaver]

How do you get those number if, for example, SUNNY isn't associated with ANY 4110-04 in the A-column?

And, ultimately, what column are you totaling? D or E ?

 

[kweaver]

I got this array formula (CTRL+SHIFT+Enter) to work. I found that using SUMPRODUCT also needed to be an array formula. I placed the code in the report table in B2 and filled across to C2.
You'll have to change the B$1 to fill down.

=SUM((B$1=IF(ISTEXT(GL_Detail!$C$1:$C$50),GL_Detail!$C$1:$C$50," "))*($A2=IF(ISTEXT(GL_Detail!$A$1:$A$50),GL_Detail!$A$1:$A$50," "))*(IF(ISNUMBER(GL_Detail!$E$1:$E$50),GL_Detail!$E$1:$E$50,0)))

=SUMPRODUCT(--(B$1=IF(ISTEXT(GL_Detail!$C$1:$C$50),GL_Detail!$C$1:$C$50," "))*(--($A2=IF(ISTEXT(GL_Detail!$A$1:$A$50),GL_Detail!$A$1:$A$50," ")))*(IF(ISNUMBER(GL_Detail!$E$1:$E$50),GL_Detail!$E$1:$E$50,0)))

But I had to change the A column of the GL_Detail to have some types like 4110-04 match the people, etc.

BTW: these both total the E column.

 

[dstepan]

So, I tried to clean up the report to give you a better picture of what I am trying to get to. I didn't include the account number 4114-04.
I inserted a row between each account number.
Row 1 shows the first line, 2nd line, Body, and last line of each account number.
I am concerned with the related invoice amount for each of the customer names - Sunny and Surgeon, within each account number.
See the first table in original thread.

	A	B	C	D	E
1	Account # ”Period:” Date of Inv Total Activity $ for period	Account Name Accounting Period Invoice # ”Ending Bal”	“Begin Bal” Customer Name Ending Bal $	Beg Bal $ Source	Invoice $
2
3	4108-02	SALES-S
4	Period:	2019-4	Begin Bal	($3,550.00)
5	30-Apr-19	G10747	PORA	AR Inv	($475.00)
6	30-Apr-19	G10747	PORA	AR Inv	($1,012.50)
7	($1,487.50)	Ending Bal	($5,037.50)
8
9	4108-04	SALES-ST
10	Period:	2019-4	Begin Bal	$0.00
11	00-Jan-00				$0.00
12	$0.00	Ending Bal	$0.00
12	4110-04	SALES-SV
13	Period:	2019-4	Beg Bal	($2,000,001.00)
14	09-Apr-19	G10701	TINKER	AR Inv	($16,312.50)
15	16-Apr-19	G10704	GERARD	AR Inv	($2,275.00)
16	16-Apr-19	G10705	SUNNY	AR Inv	($10,171.35)
17	25-Apr-19	G10706	SURGEON	AR Inv	($61,294.80)
18	29-Apr-19	G10720	AXIS	AR Inv	($10,000.00)
19	29-Apr-19	G10723	GERARD	AR Inv	($11,500.00)
20	29-Apr-19	G10724	PORA	AR Inv	($12,000.00)
21	29-Apr-19	G10725	SUNNY	AR Inv	($32,265.46)
22	29-Apr-19	G10726	SUNNY	AR Inv	($55,452.49)
23	29-Apr-19	G10728	SUNNY	AR Inv	($3,727.50)
24	30-Apr-19	G10729	APE	AR Inv	($32,300.00)
25	30-Apr-19	G10730	SURGEON	AR Inv	($3,409.63)
26	30-Apr-19	G10740	SUNNY	AR Inv	($108,450.00)
27	30-Apr-19	G10742	SUNNY	AR Inv	($9,716.50)
28	30-Apr-19	G10743	SUNNY	AR Inv	($1,551.50)
29	30-Apr-19	G10744	SUNNY	AR Inv	($5,750.00)
30	30-Apr-19	G10745	LACE	AR Inv	($119,800.00)
31	30-Apr-19	G10748	SURGEON	AR Inv	($71,169.00)
32	30-Apr-19	G10749	SURGEON	AR Inv	($60,000.00)
33	($627,145.73)	Ending Bal	($2,687,217.73)

<tbody>
</tbody>

 

[kweaver]

I think I understand it now.

I placed your data in a sheet starting in A1. That meant that 4108-02 was in A2.
I then created a helper column starting in H2:

=IF(OR(A2="4108-02",A2="4108-04",A2="4114-04",A2="4110-04",A2="4112-04"),A2,H1)

Starting in J2, I put the report table (e.g., SUNNY is in K2 and the A/N are in J3:J6).
In the cell below SUNNY (K3), I used this formula which I filled down and across (to SURGEON):

=SUMIFS($E$2:$E$43,$H$2:$H$43,$J3,$C$2:$C$43,K$2)

Since I cannot post an Excel file, you'll have to see if you follow this and get the results you want. I did.

 

[kweaver]

Try this: https://www.dropbox.com/h?preview=dstepan.xlsx

 

[dstepan]

Thank you KWeaver.

The VBA coding works perfectly.

I appreciate your input!