<<< Click here to go back
I was supposed to prepare a period wise summary on a data. The data was available in excel format at daily level and summary was required at unequal time periods level :
Definition of time periods :
01-Jan-2014 to 16-Jan-2014 -- Period 1
17-Jan-2014 to 27-Apr-2014 -- Period 2
28-Apr-2014 to 19-Sep-2014 -- Period 3
20-Sep-2014 to 03-Feb-2015 -- Period 4
04-Feb-2015 to 29-Jun-2015 -- Period 5
30-Jun-2015 to 31-Dec-2015 -- Period 6
How do you think you would do this ?
I could think of many ways do the task :
1. Grouping dates in Pivot Table to make period column, as shown >
2. By making a separate list of all the dates and corresponding period and then use a vlookup function to populate the "period column" with the base data. Use pivot table then and summarize.
3. Put filter on the data, selecting corresponding dates period wise and use SUBTOTAL function, to get summary.
All these method are fine as such, but are definitely not the efficient ways to tackle this particular problem.
Why ?
All the above method involve too much manual intervention ... it might be huge task if the time period is even more. Also there is also possibility of human error.
1. Sort the data in ascending order of date
2 . Make a list in excel with start date and period and then VLOOKUP with approximate function as shown below :
Once you get the period column, you simply pivot the data to get the period wise summary.
And we are done !
I was supposed to prepare a period wise summary on a data. The data was available in excel format at daily level and summary was required at unequal time periods level :
Definition of time periods :
01-Jan-2014 to 16-Jan-2014 -- Period 1
17-Jan-2014 to 27-Apr-2014 -- Period 2
28-Apr-2014 to 19-Sep-2014 -- Period 3
20-Sep-2014 to 03-Feb-2015 -- Period 4
04-Feb-2015 to 29-Jun-2015 -- Period 5
30-Jun-2015 to 31-Dec-2015 -- Period 6
 |
| Click to enlarge |
I could think of many ways do the task :
1. Grouping dates in Pivot Table to make period column, as shown >
2. By making a separate list of all the dates and corresponding period and then use a vlookup function to populate the "period column" with the base data. Use pivot table then and summarize.
3. Put filter on the data, selecting corresponding dates period wise and use SUBTOTAL function, to get summary.
All these method are fine as such, but are definitely not the efficient ways to tackle this particular problem.
Why ?
All the above method involve too much manual intervention ... it might be huge task if the time period is even more. Also there is also possibility of human error.
VLOOKUP with approximate match option is here to rescue you for the task. Let's see how easy the task becomes with this.
1. Sort the data in ascending order of date
2 . Make a list in excel with start date and period and then VLOOKUP with approximate function as shown below :
Once you get the period column, you simply pivot the data to get the period wise summary.
And we are done !
Enjoy reading our other articles and stay tuned with ...
Kindly do provide your feedback in the 'Comments' Section and share as much as possible.
No comments:
Post a Comment
Do provide us your feedback, it would help us serve your better.