I have a file wth around 125000 rows.But here,I have taken a simple example.
Actually,I want to apply an automation working in my attach file.It is possible thru VBA codes but have no idea to create.
When I change the month ( Cell No.O1 ) then,automatic create the unique 'Group' and sumup according to 'Group' wise 'Qty','Sales Amount','Freight Amount' and 'Insp.Charges Amount' accordingly to the month.
For better understanding,I have plot an example of a month i.e.Jan-2014.
Please provide the VBA codes workings in this context.
An automation to create summary from change month
-
- 3StarLounger
- Posts: 354
- Joined: 27 Oct 2013, 15:11
- Location: Gurgaon INDIA
An automation to create summary from change month
Regards
Pradeep Kumar Gupta
INDIA
Pradeep Kumar Gupta
INDIA
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: An automation to create summary from change month
Column B doesn't contain date values, but text values that look like dates. This makes it very difficult to process the data.
See the screenshot below. Real date values would have been right-aligned instead of left-aligned. (And no, changing the horizontal alignment won't help)
See the screenshot below. Real date values would have been right-aligned instead of left-aligned. (And no, changing the horizontal alignment won't help)
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 354
- Joined: 27 Oct 2013, 15:11
- Location: Gurgaon INDIA
Re: An automation to create summary from change month
Now,please look the revised attach file.I have changed the 'Col.B'.
Regards
Pradeep Kumar Gupta
INDIA
Pradeep Kumar Gupta
INDIA
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: An automation to create summary from change month
You can use a pivot table for this, no code needed. When you select a different month from the dropdown, the pivot table will be updated automatically.
See the attached version.
See the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 354
- Joined: 27 Oct 2013, 15:11
- Location: Gurgaon INDIA
Re: An automation to create summary from change month
But,if my column B is in date format 01/01/2014 ( dd/mm/yyyy) instead of Jan-14,and there are so many entries in the month like 02/01/2014,06/01/2014 etc.In that case,pivot table will not depict the result month wise ( Jan-14).It will show date wise.How can we see the result of different dates in a month wise e.g.Jan-14?
Regards
Pradeep Kumar Gupta
INDIA
Pradeep Kumar Gupta
INDIA
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: An automation to create summary from change month
You can add a calculated column named Month with formulas such as =B7-DAY(B7)+1, and use that in the pivot table.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: An automation to create summary from change month
Hi Pradeep,
Here is the VBA code for the request you posted.
Just for clarity though, I see you have rejected the use of Pivot Tables a few times in the past. Can I ask why you do not use them as they are very easy to set up and is 100% more flexible and dynamic than VBA?
Here is the VBA code for the request you posted.
Just for clarity though, I see you have rejected the use of Pivot Tables a few times in the past. Can I ask why you do not use them as they are very easy to set up and is 100% more flexible and dynamic than VBA?
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 3StarLounger
- Posts: 354
- Joined: 27 Oct 2013, 15:11
- Location: Gurgaon INDIA
Re: An automation to create summary from change month
Thanks Rudi,for your awesome support to me.Very glad to find the result from Pivot table and VBA codes.
Regards
Pradeep Kumar Gupta
INDIA
Pradeep Kumar Gupta
INDIA