+ Reply to Thread
Results 1 to 15 of 15

How to separate a report by date

  1. #1
    Registered User
    Join Date
    05-31-2018
    Location
    Accra, Ghana
    MS-Off Ver
    2016
    Posts
    12

    How to separate a report by date

    I have an issue that is simple yet complicated (for me anyway). I've attached a report spanning a few days. I need to see if there is a macro or something that can separate this report by date. So that every record for a particular date is shifted to another tab? Basically we need to analyse each date separately so they need to be separated somehow. What would be ideal then is for the macro to work for another report with a different number of records. Thank you.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: How to separate a report by date

    So potentially 365 tabs (for year's worth of data) ?

  3. #3
    Registered User
    Join Date
    05-31-2018
    Location
    Accra, Ghana
    MS-Off Ver
    2016
    Posts
    12

    Re: How to separate a report by date

    Quote Originally Posted by JohnTopley View Post
    So potentially 365 tabs (for year's worth of data) ?
    Sorry no. For example, all records dated 25th May would go on one tab, then all records dated 26th May would go in another. There is maximum of 7 dates in our reports so we would have max of 7 tabs generated. Thank you.

  4. #4
    Registered User
    Join Date
    04-14-2018
    Location
    Vermont, USA
    MS-Off Ver
    Office 365
    Posts
    72

    Re: How to separate a report by date

    Is there a reason you think separate tabs is the best solution? If not, perhaps you could explain the kind of daily analysis you need to do because there may be a better way to accomplish it.

  5. #5
    Registered User
    Join Date
    05-31-2018
    Location
    Accra, Ghana
    MS-Off Ver
    2016
    Posts
    12

    Re: How to separate a report by date

    I'm open to suggestions. Doesn''t have to be tabs. We analyse the reports to show patrol activity per day. We could generate the reports per day but that would take a lot of time to generate for each day for many reports so we generate the excel spreadsheet for a week and then need to separate the dates so we can analyse and give a clear report per day to the client. I hope I have explained it well.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: How to separate a report by date

    7 tabs is reasonable.

    Another option is to extract data to a single sheet for a given day.

  7. #7
    Registered User
    Join Date
    05-31-2018
    Location
    Accra, Ghana
    MS-Off Ver
    2016
    Posts
    12

    Re: How to separate a report by date

    Quote Originally Posted by JohnTopley View Post
    7 tabs is reasonable.

    Another option is to extract data to a single sheet for a given day.
    Yes that is an option. The question is how to automate this process so it doesn't have to be done manually for each report. We will be doing 20 reports per day so it adds up.

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: How to separate a report by date

    Your file listed input data but no report example.

    Confused by 7 days of data but 20 reports so a sample of expected output would help, including how dates are input to select a given report.

    Extract can be automated base a date (or dates) for selection.

  9. #9
    Registered User
    Join Date
    05-31-2018
    Location
    Accra, Ghana
    MS-Off Ver
    2016
    Posts
    12

    Re: How to separate a report by date

    The finished report is not a problem as I can always do once the data has been separated.
    The 7 days of data is for patrolling in 1 particular zone. We will be analyzing data for 7 days for 20 different zones so each report

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: How to separate a report by date

    A sample is required: obvious to you but not to me (us!).

  11. #11
    Registered User
    Join Date
    05-31-2018
    Location
    Accra, Ghana
    MS-Off Ver
    2016
    Posts
    12

    Re: How to separate a report by date

    Quote Originally Posted by JohnTopley View Post
    A sample is required: obvious to you but not to me (us!).
    Understood. Please find attached. This shows the finished report with all days in it but ideally we would have each of these reports for all the data for 1 date. i.e. 25th or 26th May etc. I used a macro to edit the raw data to get this finished report.

    Thanks again.
    Attached Files Attached Files

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: How to separate a report by date

    See attached: sheet "Extract" - hope this is what you want.

    In C4

    =IFERROR(INDEX(Data!C$2:C$2000,SMALL(IF((Data!$C$2:$C$2000>=Extract!$C$1)*(Data!$C$2:$C$2000<=Extract!$E$1),ROW(Data!$C$2:$C$2000)-ROW($C$2)+1,""),ROWS($C$2:C2))),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Then copy across to D and down
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    05-31-2018
    Location
    Accra, Ghana
    MS-Off Ver
    2016
    Posts
    12

    Re: How to separate a report by date

    John, that is pretty awesome man! Just showed it to my partner and she said that it definitely does what she needs it do make her work easier! You are a rock-star sir!! Thank you sir!!

  14. #14
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: How to separate a report by date

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  15. #15
    Registered User
    Join Date
    05-31-2018
    Location
    Accra, Ghana
    MS-Off Ver
    2016
    Posts
    12

    Re: How to separate a report by date

    Done. Thank you man.

+ 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. Creating an Excel Report from 7 separate files using VBA
    By Ewoutvm in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-14-2016, 02:15 PM
  2. Creating an Excel Report from 7 separate files using VBA
    By Ewoutvm in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-12-2016, 11:42 AM
  3. [SOLVED] Excel2010 IF Formula to return value based on a separate date and two separate text values
    By jakecutler in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-12-2015, 03:24 AM
  4. split data from report into separate areas.
    By wentom in forum Excel General
    Replies: 16
    Last Post: 05-28-2012, 04:58 AM
  5. Display info from a separate report by matching the date
    By Srp in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 06-02-2011, 11:43 AM
  6. Replies: 4
    Last Post: 09-08-2009, 02:43 PM

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