complex data move and sort

June 16, 2011 at 12:42:11
Specs: Windows 7
Hello all,

I am working on simplifying a task that takes 1 to 3 hours to complete every month so I am looking for a Macro, VBA code etc to automate the process (or at least part of the process)
basicly we are pulling out the last months events on a database

We start the process with an export of data from a report (CSV file).... what we currently do with the data is; 1. sort data by date 2. copy all rows that fall within the last month to a new tab 3. sort the copied data by 3 fields, date, and process name 4. verify first entry for each process name = "request" 4b. if first entry is not "request" go back to orignal data and copy any older lines that match the process id in question 5. after all needed data is copied run formula to determin total # of days eah process took (this is date of completion minus date of request) 6. look for any days to be subtracted from total days (days in status "client sign-off" and days in "stop Clock") 7. run formula if total days = 13 or less = SLA meet

I currently have a macro writen that sorts the orignal data by date and creates the needed new tabs and renames the tabs....


See More: complex data move and sort

Report •

June 17, 2011 at 07:32:34
Sorry I know my first post was a garbled mess. here are 2 files that show the data that is imported (a very small subset of the data is included).... import.xls and the final report (what I would like the finished one to look like) Report.xls

Step by step process to get it to Finished.xls
[B]1.[/B] rename tabs to "Original data", "Completed","SDI work in Process", and "Apps in client sign-off"
[B]2.[/B] sort all data on Original data tab by date
[B]3.[/B] Copy any data for current month to completed tab (this becomes our working tab)
[B]4.[/B] Sort data on Completed tab in this order 1. Application Name, 2. Activity Date 3. Workflow Major
[B]5.[/B] go back to Original data and do same sort, ensure you have all rows of data for all listed applications in Completed tab (look for rows that fell out of the original sort by month), if any extra rows found copy to Complete tab. In my example this would be rows 14 through 16 (Lotus Notes 8.5.2 Fixpack 2 (FP2SHF75) Basic) as this Application was started prior to 6/1/2011
[B]6.[/B] Verify the last (latest step) for each application, If last step = "Package Complete Phase" leave on Complete tab, If last step = "Client Signoff Phase" copy all rows for that application to the Apps in client sign-off tab, If last step anything else move all rows to SDI Work In Process tab
[B]7.[/B] Now all further work will be done in the Complete tab.....
[B]8.[/B] Highlight The Workflow Phase Complete, Request Phase, Yellow
[B]9.[/B] Highlight The Workflow Phase Complete, Package Complete Phase, Yellow
[B]10.[/B] Highlight any rows that are SLA Clock Stopped, or Client signoff, Red
[B]11.[/B] For each program listed (there is only one in my example) we need to find total time in days spent on project.... Total time = Request phase complete (line 4 on my example) to Package complete phase (line 16 on my example) minus any time spent in Client signoff phase (Lines 14 and 15 on my example) and any time spent in status of SLA Clock Stopped Formula "=NETWORKDAYS(+E2,E16)" gives total time spend, I then run the same formula on the clock stoppage and client signoff and manually subtract these from the initial total work time.... In the example total time worked is 16 days and total time to be subtracted is 5 days for a grand total of work time of 11 days
[B]12. [/B] Enter Final work time in cell next to total time
[B]13.[/B] If time is less than or equal to 13 days enter Yes in next cell.
[B]14.[/B] create headers over last 3 columns, F g and h on my example, Time, total time and SLA Met

Wish list... If possible the script/macro etc should prompt for date range, or month (either is 100% fine).

Thank you in advance

Report •

June 17, 2011 at 08:09:58
sorry, I dont see anywhere to attach a file to a post... without that my last post doesnt make alot of sense.

Report •

June 17, 2011 at 09:12:40
You can't attach files in this forum.

You could put the file on the web and provide a link.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

Ask Question