+ Reply to Thread
Results 1 to 21 of 21

Count Occurrences and Fill Cells with Results Using a Date Range

  1. #1
    Forum Contributor
    Join Date
    02-27-2004
    Location
    California, United States
    MS-Off Ver
    Excel 2016
    Posts
    259

    Count Occurrences and Fill Cells with Results Using a Date Range

    Hello -

    I have been trying to figure out how to do this for the last week and I am stumped. I have attached a spreadsheet which has a very small portion of sample data. I have labeled the columns: ID Number, Date, Group Code, Group Name and Code ID.

    I have four tabs in the workbook: Data, Pivot Table, Summary Sheet, and Dates

    I need the pull the data by date range and Group Code and count the occurrences of each Code ID. With that information, I need to fill the cells on the Summary Sheet tab automatically (without user intervention).

    I was thinking I could use the GetPivotData function, but I haven't figured out how to get the pivot table to sort the data using a specific date range.

    Thank you in advance for your assistance with this!!

    Cliff Watson
    Attached Files Attached Files

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Count Occurrences and Fill Cells with Results Using a Date Range

    If we can use the source sheet: Data.

    Then in the Summary Sheet,

    In B4:

    Please Login or Register  to view this content.
    confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.

    In C4:

    Please Login or Register  to view this content.
    copied down

    Then copy B4:C19 and paste to top left cell of other tables (i.e. E4, H4, etc...)
    Attached Files Attached Files
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Contributor
    Join Date
    02-27-2004
    Location
    California, United States
    MS-Off Ver
    Excel 2016
    Posts
    259

    Re: Count Occurrences and Fill Cells with Results Using a Date Range

    NBVC -

    This is AWESOME! Thank you very much.

    What about taking into account the beginning and ending date? Also, my full spreadsheet has over a thousand rows of data and grows each day with new data. I tried changing the "Data!$C$2:$C$200" to "Data!$C:$C" so it would take in the complete column but it didn't work (the formula returned a null value). How can I use the complete column of data without having to go in each day and look at the ending row number?

    Thank you so much for your assistance!!!!
    Last edited by CWatsonJr; 05-18-2012 at 12:22 PM.

  4. #4
    Forum Contributor
    Join Date
    02-27-2004
    Location
    California, United States
    MS-Off Ver
    Excel 2016
    Posts
    259

    Re: Count Occurrences and Fill Cells with Results Using a Date Range

    I thought this was solved but I had to change the status back because I need to account for a date range and I need to be able to make the last cell in the column a variable because the last row increases daily.

    Thank you for the help I have received so far... it is certainly on the right track!!!!

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Count Occurrences and Fill Cells with Results Using a Date Range

    Okay, first let's make the Data sheet data a dynamic range.

    So go to Formula tab, select Define Name. Enter a name, like DataList and then a formula in the source field like:

    Please Login or Register  to view this content.
    This now allows you to add to the bottom of the list dynamically.

    Now, assuming the dates are in the Dates tab, then change formula in B4 to:

    Please Login or Register  to view this content.
    confirmed with CTRL+SHIFT+ENTER and copied down

    and change formula in C4 to:

    Please Login or Register  to view this content.
    entered with just ENTER and copied down.

    Then copy again from B4:C19 and paste to top left of each other group.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    02-27-2004
    Location
    California, United States
    MS-Off Ver
    Excel 2016
    Posts
    259

    Re: Count Occurrences and Fill Cells with Results Using a Date Range

    Thank you.

    I will not be able to get back to my spreadsheet at work until the middle of next week. I will let you know how it goes. I came home and opened this up on my MAC and since the format of Excel is different than Windows, I was trying to see if I could adapt... and I am not doing very well (that and I am exhausted). For some reason, I have no idea what or where the source field is... do you mean the formula bar? I am able to Define a name for a column in the Name Window but in Excel:mac there is no Define Name option on the Formula tab and nothing that I can find to be a Source Field. Okay - now I feel just plain ign'ernt LOL.

    I opened the file you attached and when I click on the columns on the Data worksheet, it doesn't appear any of the column names are defined. This is going to give me something to read about over the weekend... :D The last Excel book I purchased was Excel 97... I think it may be time to go out and get a new bible!

    Thank you again for your help!

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Count Occurrences and Fill Cells with Results Using a Date Range

    In the Formula tab, there should be a section in the middle of the ribbon called Defined Names.. and there should be a Name Manager button... when you select it, you should see the DataList listed. Select it and in the Refers to field at the bottom (sorry, I called it Source field.. Excel has many names for different fields) you should see my formula. If you wanted to create a new name, you click New and enter the name and "refers to"....

  8. #8
    Forum Contributor
    Join Date
    02-27-2004
    Location
    California, United States
    MS-Off Ver
    Excel 2016
    Posts
    259

    Re: Count Occurrences and Fill Cells with Results Using a Date Range

    Excel:mac is somewhat lame in my opinion - just not as friendly as the Windows version. I have thrown in some screenshots so you can see what I am looking at. If I have time, I will fire up my OLD Dell laptop which has 2007 on it and look at it there.

    There is an Insert Name in the Function section of the ribbon and when it is clicked then the Datalist name pops in a window. When that is clicked the the cell where the cursor is, highlights with a box for entering a formula and has pre-entered =Datalist, cell A2 highlights as well as column C - so I can tell the formula is in there somewhere...

    Thank you again!

    Attachment 156769Attachment 156770Attachment 156771

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Count Occurrences and Fill Cells with Results Using a Date Range

    They're coming up as invalid links for me....

    doing research on google it looks like you need to do the following: In Excel 2011 (Mac) choose Insert > Name > Define to create a named range, and "fn+command+F3" to see the name manager.
    Last edited by NBVC; 05-19-2012 at 11:57 PM.

  10. #10
    Forum Contributor
    Join Date
    02-27-2004
    Location
    California, United States
    MS-Off Ver
    Excel 2016
    Posts
    259

    Re: Count Occurrences and Fill Cells with Results Using a Date Range

    Okay - I pulled out my HP which has Excel 2010 on it so that I would have a friendlier version to play with (I actually like 2K7 better...)

    I reformatted my sample workbook so that the columns and rows of the involved sheets would match what I have in my actual database (the names have been changed to protect the innocent).

    I adapted your formulas as I thought they should be but apparently my brain is not absorbing the meaning of what I am doing (I thought I understood but then it didn't work).

    I have uploaded the sample for you to look at to see where I messed your formulas up...

    Thank you again for your help!! I know the formulas will work because you uploaded a workbook where they did... I am just not adapting them correctly.
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    02-27-2004
    Location
    California, United States
    MS-Off Ver
    Excel 2016
    Posts
    259

    Re: Count Occurrences and Fill Cells with Results Using a Date Range

    Okay - I found out one of my problems, I had invalid dates on my dates sheet... I needed to change the end date to 1/31/12 so it would pick up data in the range I have supplied. But... it is copying the same CodeID in every cell.

  12. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Count Occurrences and Fill Cells with Results Using a Date Range

    I just tested your workbook... and it works fine, if you fix the dates as you say above... I get different codes.

    Is it still happening? Do you have Automatic calcs on?

  13. #13
    Forum Contributor
    Join Date
    02-27-2004
    Location
    California, United States
    MS-Off Ver
    Excel 2016
    Posts
    259

    Re: Count Occurrences and Fill Cells with Results Using a Date Range

    Really? I do have automatic calcs on. Here is a screen shot of what I am getting.

    screenshot.jpg

    I have the end date set as 1/31/12 and the beginning date is automatically calculated to 1/4/12. This screen shot is using group 04. I have tried changing group numbers (and copying down) and I get the same results. The same code with the same number of occurrences.

    I will take this sheet over to my Mac and see what it does there.

    Grrrrr.

  14. #14
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Count Occurrences and Fill Cells with Results Using a Date Range

    is your first INDEX() function you are referring to INDEX(DefinedList,0,8)... the attached workbook it was INDEX(DefinedList,0,11). Did you double-check that the references are correct?

    all the range reference in the last ISNA(MATCH() should also reference the same range as the initial INDEX()... i.e. in the attachment sheet's case INDEX(DefinedList,0,11)
    Attached Files Attached Files
    Last edited by NBVC; 05-28-2012 at 01:19 PM. Reason: added sample attachment

  15. #15
    Forum Contributor
    Join Date
    02-27-2004
    Location
    California, United States
    MS-Off Ver
    Excel 2016
    Posts
    259

    Re: Count Occurrences and Fill Cells with Results Using a Date Range

    That may have been part of the problem. The reason the reference was different was because of the array boundaries I set between the two. I went through the formula and made sure it was referring to the correct columns and then entered it (shift-cntrl-enter) and it worked.

    I then copied the formula over to my real spreadsheet, adjusted it as needed, entered it, and it works!

    Now, I will apply this to all of my other summary tabs and play with it so it will sort high to low by occurrences.

    Thank you so much for your help!

  16. #16
    Forum Contributor
    Join Date
    02-27-2004
    Location
    California, United States
    MS-Off Ver
    Excel 2016
    Posts
    259

    Re: Count Occurrences and Fill Cells with Results Using a Date Range

    I have used this across my workbook and it is working great!

    When I look at the output, it seems to be displayed in the order it is picked in the data table (first come first serve?). Is there a way to change the order so that it displays the results in occurrence order (highest to lowest)?

  17. #17
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Count Occurrences and Fill Cells with Results Using a Date Range

    Can you clarify what you mean by highest to lowest? Are we talking dates, i.e. latest to earliest or vice versa, or something else?

  18. #18
    Forum Contributor
    Join Date
    02-27-2004
    Location
    California, United States
    MS-Off Ver
    Excel 2016
    Posts
    259

    Re: Count Occurrences and Fill Cells with Results Using a Date Range

    The formula is counting the number of occurrences of the CodeID (that are between certain dates) and displaying the results on another tab (so awesome!).

    Is there a way to change the order the CodeID is being displayed so that the highest number of occurrences are first and the lowest number of occurrences are last?

    I have posted the workbook with another line of data entered to show what I am talking about.

    Thank you again for your help.
    Attached Files Attached Files

  19. #19
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Count Occurrences and Fill Cells with Results Using a Date Range

    That, I think would involve having helper column(s) either in the Database sheet or in the Summary sheet first depending on your preference... and efficiency.. put a helper on the Database sheet might be more efficient in the long run.

  20. #20
    Forum Contributor
    Join Date
    02-27-2004
    Location
    California, United States
    MS-Off Ver
    Excel 2016
    Posts
    259

    Re: Count Occurrences and Fill Cells with Results Using a Date Range

    In the real workbook, I do have helper sheets where I populate data from the database and then sort it by occurrence for other parts of the summary sheets. I will use your formula to populate the data on the helper sheet for this category and then see if I can sort it like I do my other data and then pull the data where it needs to be on the summary sheet.

    I had thought of doing this before but I didn't want to add more stuff to the helper sheet if I didn't have to.

    Once I get all of this laid out and working correctly, it is going to save me about 3 hours each Monday when I update the workbook!

  21. #21
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Count Occurrences and Fill Cells with Results Using a Date Range

    I am saying helper columns.. not more sheets. The formula, if we came up with one, would be complex and probably inefficient.. especially if your ranges grow.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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