+ Reply to Thread
Results 1 to 7 of 7

Macro to lookup data monthwise and aggregate the value in a new sheet

  1. #1
    Registered User
    Join Date
    06-16-2014
    Location
    india
    MS-Off Ver
    2007
    Posts
    29

    Macro to lookup data monthwise and aggregate the value in a new sheet

    Hey,

    can anyone help me with a macroSample.xlsx to lookup the numbers and merge the values based on months. data given in the attached sheet.

    Output should be in a different sheet with columns 1. Number 2. Month 3. Sum value

    Thanks in advance.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,306

    Re: Macro to lookup data monthwise and aggregate the value in a new sheet

    This is probably not the layout you asked for, but see if this will suite your needs...

    A
    B
    C
    D
    1
    Apr-15
    May-15
    Jun-15
    2
    123456
    109
    117
    119
    3
    123457
    115
    111
    125
    4
    123458
    121
    123
    113


    A2=IFERROR(INDEX(Sheet1!$A$2:$A$19,MATCH(0,INDEX(COUNTIF($A$1:A1,Sheet1!$A$2:$A$19),),0)),"")
    copied down
    B=IF(A2="","",SUMIFS(Sheet1!$C$2:$C$19,Sheet1!$A$2:$A$19,$A2,Sheet1!$B$2:$B$19,B$1))
    copied down and across
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    06-16-2014
    Location
    india
    MS-Off Ver
    2007
    Posts
    29

    Re: Macro to lookup data monthwise and aggregate the value in a new sheet

    Thanks Ford. But i am looking at the template i have given.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,306

    Re: Macro to lookup data monthwise and aggregate the value in a new sheet

    You actually did not really give a template, but OK.

    If you want a list of months and a list of numbers, why not just sort your list, it will pretty much give the same thing

  5. #5
    Registered User
    Join Date
    06-16-2014
    Location
    india
    MS-Off Ver
    2007
    Posts
    29

    Re: Macro to lookup data monthwise and aggregate the value in a new sheet

    HI Ford, am just looking to figure out the logic. My workbooks are huge and data are heavy. I cannot sort things each and every time. I want the function to be refreshed when i paste the new files in a common folder. It will be a summary sheet with combination of many sheets. So kindly help me with copying from this sheet to another workbook.

    The template for output is

    A B C
    Number Month Sum Value



    I want the total rows to be reduced into 9 from 18.

  6. #6
    Registered User
    Join Date
    06-16-2014
    Location
    india
    MS-Off Ver
    2007
    Posts
    29

    Re: Macro to lookup data monthwise and aggregate the value in a new sheet

    Kindly anyone help on this!!

  7. #7
    Forum Contributor
    Join Date
    04-20-2015
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    312

    Re: Macro to lookup data monthwise and aggregate the value in a new sheet

    Why not use a pivot table?
    Your Data is perfectly fitting a pivot.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Consolidate client data from monthwise to new sheet
    By cpramesh in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 12-11-2014, 07:41 AM
  2. Combile data from two sheet to one..monthwise
    By michael_2013 in forum Excel General
    Replies: 3
    Last Post: 05-23-2013, 03:53 AM
  3. Replies: 2
    Last Post: 02-12-2013, 07:28 AM
  4. Macro to filter data monthwise
    By sivdin in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-21-2011, 06:07 AM
  5. Macro for converting aggregate to case level data
    By epi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-27-2010, 09:45 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1