Consulting

Results 1 to 3 of 3

Thread: Calculating MAT and YTD $ for a large dataset

  1. #1

    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!

  2. #2
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Have a look at the split function in Excel Help
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •