Look-up and Average based on 4 conditions

gfischer

New Member
Joined
Jul 8, 2020
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I am trying to create a VBA macro to automatically calculate a three-month rolling average. Given a large spreadsheet of imported data, format seen below, I need to isolate in on the prior three months of invoices given a project designation, account code, and PO number. While the project designation is unique and will only appear once, account codes and PO numbers can and will be repeated very often.

Focusing on the second image...assuming it is June 2020, I want to determine the rolling average of the PO# 123456 in account 567101 of project 123EF123. The desired rows are highlighted in green. Additionally, there may be instances where multiple invoices of the same PO were incurred in one month, as seen in account 567202 with the blue highlighted rows.

I have absolutely no idea of how to proceed in automating this process. Any and all help would be greatly appreciated!
If this seems like it would be too much to do, even having the ability to quickly bring the block of data of the associated account to the active sheet would be fantastic.

2020-07-08_16-24-53.png
2020-07-08_16-25-14.png
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sample Invoice Data.xlsx
ABCDEFGH
43
44Project:123EF123
45Account Code:567101Fees
46Accounting PeriodLine CommentPO#Supplier / CustomerAmount
47APR-20123456 Company - Fees -MAR20-50,000.00
48APR-20New PO for a total of $1,000,000 USD123456COMPANY INC60,000.00
49APR-20123456 Company - Fees -APR2075,000.00
50FEB-20123456 Company - Fees -JAN20-90,000.00
51FEB-20New PO for a total of $1,000,000 USD123456COMPANY INC70,000.00
52FEB-20123456 Company - Fees -FEB2080,000.00
53JAN-20123456 Company - Fees -DEC19-75,000.00
54JAN-20New PO for a total of $1,000,000 USD123456COMPANY INC100,000.00
55JAN-20123456 Company - Fees -JAN2090,000.00
56JUN-20123456 Company - Fees -MAY20-80,000.00
57JUN-20New PO for a total of $1,000,000 USD123456COMPANY INC16,000.00
58MAR-20123456 Company - Fees -FEB20-80,000.00
59MAR-20New PO for a total of $1,000,000 USD123456COMPANY INC75,000.00
60MAR-20123456 Company - Fees -MAR2055,000.00
61MAY-20123456 Company - Fees -APR20-75,000.00
62MAY-20New PO for a total of $1,000,000 USD123456COMPANY INC55,000.00
63MAY-20123456 Company - Fees -MAY2080,000.00
64
65Account Code:567202Recruitment
66Accounting PeriodLine CommentPO#Supplier / CustomerAmount
67APR-20Travel - FEB20-125,000.00
68APR-20Travel - JAN20-80,000.00
69APR-20Travel - MAR20-120,000.00
70APR-20New PO in the amount of $100,000654321WORLDWIDE LLC50,000.00101,500.00
71APR-20New PO in the amount of $100,000654321WORLDWIDE LLC6,500.00
72APR-20New PO in the amount of $100,000654321WORLDWIDE LLC45,000.00
73APR-20Travel - APR20120,000.00
74APR-20Travel - MAR20125,000.00
75APR-20Amortization40,000.00
76FEB-20Travel - DEC-19-80,000.00
77FEB-20Travel - JAN-20-80,000.00
78FEB-20New PO in the amount of $100,000654321WORLDWIDE LLC6,500.00
79FEB-20New PO in the amount of $100,000654321WORLDWIDE LLC12,500.00
80FEB-20Request for PO. Value of $250,000987654SAMPLE CORP14,000.00
81FEB-20Travel - DEC1980,000.00
82FEB-20Travel - FEB20125,000.00
83FEB-20Travel - JAN2080,000.00
84JAN-20Travel - DEC-19-80,000.00
85JAN-20Travel - NOV-19-80,000.00
86JAN-20New PO in the amount of $100,000654321WORLDWIDE LLC7,000.00
87JAN-20New PO in the amount of $100,000654321WORLDWIDE LLC75,000.00
88JAN-20Travel - DEC-1980,000.00
89JAN-20Travel - JAN-2080,000.00
90JAN-20Amortization40,000.00
91JUN-20Travel - APR20-60,000.00
92JUN-20Travel - MAR20-125,000.00
93JUN-20Travel - MAY20-60,000.00
94JUN-20New PO in the amount of $100,000654321WORLDWIDE LLC6,500.0040,500.00
95JUN-20New PO in the amount of $100,000654321WORLDWIDE LLC34,000.00
96MAR-20Travel - DEC19-80,000.00
97MAR-20Travel - FEB20-125,000.00
98MAR-20Travel - JAN20-80,000.00
99MAR-20New PO in the amount of $100,000654321WORLDWIDE LLC25,000.00
100MAR-20New PO in the amount of $100,000654321WORLDWIDE LLC6,500.00
101MAR-20New PO for a total of $500,000456789BUSINESS EXAMPLE14,000.00
102MAR-20Travel - FEB20125,000.00
103MAR-20Travel - JAN2080,000.00
104MAR-20Travel - MAR20125,000.00
105MAR-20Amortization80,000.00
106MAY-20Travel - APR20-125,000.00
107MAY-20Travel - MAR20-125,000.00
108MAY-20New PO in the amount of $100,000654321WORLDWIDE LLC6,500.0031,850.00
109MAY-20New PO in the amount of $100,000654321WORLDWIDE LLC8,500.00
110MAY-20New PO in the amount of $100,000654321WORLDWIDE LLC16,500.00
111MAY-20Travel - APR2060,000.00
112MAY-20Travel - MAR20125,000.00
113MAY-20Travel - MAY2060,000.00
114MAY-20Amortization40,000.00
115
Sample - Accounts
 
Upvote 0

Forum statistics

Threads
1,223,880
Messages
6,175,154
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top