Need Help in Creating Automated Report using Macros

I need help in getting a VBA Codes for my report. I run a report daily, I have around 2000 items, this is an invoice report on all our customers in one worksheet. Each customers has 3 or more invoices, seldom has one invoice only. We have around 300 customers.

Columns as follows:
A - CUSTOMER NAME
B - INVOICE NUMBER
C - INVOICE DATE
D - INVOICE DUE DATE
E - AGE OF THE INVOICE
F - INVOICE AMOUNT
G - FREQUENCY (NUMBER OF DAYS FROM THE FIRST ORDER TO THE NEXT ORDER DATE) - usually those customers with only 1 invoice is blank, meaning no frequency.

This is what I need to do, and we had this project on getting an automated report:
1. I need to get the last invoice date of each customer.
2. Need the total number of invoice/s of each customer
3. The total invoice amount of each customer, meaning from first to the last invoice they have with us.
4. Total Frequency of each customer
5. Average Order date, that is #4 divided by #3
6. Estimated Next order date, that is the answer to #5 plus #1 - the date of the last invoice
7. Next Order Date Due in 7 days, that is the answer to #6 plus 7 days
8. Next Order Date Due in 2 weeks, that is the answer to #6 plus 2 weeks
9. Next Order Date Due in 1 month, , that is the answer to #6 plus 30 days

And there is another sheet which would provide a reminder on which customer to contact on those date every time the report is run.

I have been working on VBA Code but it seems not successful, I have been learning Macros for almost a month now and I am really in need with help on this. I know this is a tough work.

Any suggestions?

Thanks!
Celine

several question.

1. How you want the date look like?
Please upload a sample file for raw and desired result.
> GO advanced > manage attachment > upload > close window

2. did Q5 you request is weired? (frequency/amount to get date??)

3.

customer to contact on those date every time the report is run.

Please clarify further, which date to be used.

Hi BoredWorker - Sorry if I was not able to respond immediately. I was out the entire week last week.

By the way, see attached raw file. I would really appreciate your help on this.

1st tab is the Raw data, I sort of created a macro that would remove all the special characters and would already calculate the frequency of each invoices.
2nd tab (Final Output) is what I need to be automated,
and so as the 3rd tab or reminder tab.

Hello Forum Expert, Thank you so much for your response. I was really looking into this forum because I was ask to get this data done.

This is very useful.

Sure, we can work on how the reminder sheet would be automated. And so as the entire workbook.
Let me know the process and how this work.

Sure,

I set the reminder tab as an example, the sheet I attached was only an example on how it the REMINDER TAB looks like. I was not able to get the correct output on the companies with the correct data because I only showed a sample. I really apologize for that. The Reminder Tab should be based on the final output sheet.

I have another question, or request with the following formula:
The formula for column A* is: =INDEX(Data!B$3:B$1359,MATCH(0,COUNTIF(A$1:A1,Data!B$3:B$1359),0))
The formula for column B* is: =MAX(IF(Data!B$3:B$1359=A2,Data!F$3:F$1359))
The formula for column C* is: =B2-MIN(IF(Data!B$3:B$1359=A2,Data!F$3:F$1359))
The formula for column D is: =COUNTIFS(Data!B$3:B$1359,A2)
How to I make Data!B$3:B$1359 an indefinite array. Like the entire Column, not just limit to cell 1359. There is an instance the Data Sheet will add up more invoices on a daily basis.

Thanks!

Got it.

Wow you are a super! This is all I needed. Thank you so much. Can't thank you enough! :-)

Sure thing I will. Thank you for being helpful. :-)