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

dstepan · May 23, 2019

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

	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)

kweaver · May 23, 2019

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 · May 23, 2019

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.

Code:

=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)))

Code:

=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 · May 24, 2019

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.

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

kweaver · May 24, 2019

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:

Code:

=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):

Code:

=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 · May 24, 2019

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

dstepan · May 29, 2019

Thank you KWeaver.

The VBA coding works perfectly.

I appreciate your input!

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

dstepan

Board Regular

Excel Facts

kweaver

Well-known Member

kweaver

Well-known Member

dstepan

Board Regular

kweaver

Well-known Member

kweaver

Well-known Member

dstepan

Board Regular

Similar threads

Forum statistics

Share this page

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

dstepan

Board Regular

Excel Facts

kweaver

Well-known Member

kweaver

Well-known Member

dstepan

Board Regular

kweaver

Well-known Member

kweaver

Well-known Member

dstepan

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock