Calculating MAT and YTD $ for a large dataset

After pasting data from another source into columns A and B (with a header row) as follows...

Column A includes a “.” Delimited string in the format REGION.CATEGORY.PRODUCT.YEAR.MONTH(MMM)
Column B is the corresponding Monthly Revenue Value to Column A

I am hoping to use VBA to quickly populate other columns as follows...

Column C should extract REGION from column A
Column D should extract CATEGORY from column A
Column E should extract PRODUCT from column A
Column F should extract YEAR from column A
Column G should extract MONTH from column A

Column H should calculate MAT Revenue for the current REGION.CATEGORY.PRODUCT combination and ending with the date(year and month)
Column I should calculate Last Year MAT Revenue for the current REGION.CATEGORY.PRODUCT combination and ending with the same month last year
Column J should calculate YTD Revenue for the current REGION.CATEGORY.PRODUCT combination and ending with the date(year and month)
Column K should calculate Last YTD Revenue for the current REGION.CATEGORY.PRODUCT combination and ending with the same month last year

There are approximately 15,000 rows in my data set making looping SUMIFS very time consuming. Has anyone get any ideas how I might achieve this?

Other complicating factors include the fact that if there were no sales in a particular month, there may be missing date references and as I will be refreshing columns A and B each month, the range will also grow.

Any help would be greatly appreciated!

Have a look at the split function in Excel Help

Welcome to the forum!

We can easily make VBA do it but you have to run it each time the data changes. With some effort, we could probably do it all by formulas. The 3rd option is probably one that I might use which does both methods but all automatically like formulas. In other words, I would use UDF's.

Since your data varies, it is best to post a short example of the input and the expected output marked manually.