Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    formula/code for counting

    QUES- DATA.xlsAttachment 28729Attachment 28728Attachment 28728
    hi to all,

    For counting purposes I need to transfer the top five rows (data sample attached) into the bottom five rows. The process starts by computing the starting month and the ending month and based on the months interval a value of (1) will be put under the right month heading, if there is a repeation, as shown in the example for num 60 then that should not be counted again.


    dubdub
    Last edited by dubdub; 2011-07-31 at 16:06.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Dubdub,

    Attached you'll find a copy of your worksheet. I used the Advanced filter w/Unique selected to select the formulas then recorded that into a macro. I also created a formula that will do the counting automatically. If you are not familiar with Advanced filters check out the online documentation then look at the way it is setup in the worksheet. Pay attention to the assigned range names as they make the whole process simpler and they are used in the macro. I have to go now, guests coming, when I get some more time I'll wrangle code to automatically fill in the formula after the filter is applied, in the same macro.

    Ok, guests gone. I've deleted the old workbook and attached a new one that "Does it All!". At least I hope so.

    Check out the following:
    Macro - Alt+F11
    Criteria Sheet. Note the Range name table shown is the current definitions if you insert rows this table will need to be refreshed for reference only it is not functional to the process.

    When you run the macro it will:
    Delete the old formulas in the counting range.
    Re-extract the unique rows from the Database at the top of sheet 1.
    Fill in the formulas next to the extracted data.

    You can add rows between the Data and Counting areas {best done between existing rows that way you don't have to manually adjust the Database range name}. All the rest is automatic when you run the macro.

    Let me know if you have any problems/questions.
    Attached Files Attached Files
    Last edited by RetiredGeek; 2011-07-31 at 22:37.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    many thank RetiredGeek, i will give it a try and i hope it will not have problem with more rows of data.

    dubdub

Posting Permissions

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