aurorasl2
03-06-2009, 12:27 AM
Hi VBA Forums,
I’ve Been searching through the forums and I have seen similar posts to what I am looking for, but not all in the same post, so my code is continually not working properly. Any help is greatly appreciated as this is vexing me! I am sure for the experts this is simple(r)
I am using a Webservice to pull data in one worksheet ‘Web Service Data’ and then rearranging this data and placing it into worksheet ‘Report’. I need to Sort on one Column to pull like values together, then add the total from another column, and then finally, to add a Grand Total of all of the totals.
These are all dynamic values as there could be many columns to sort by.
Here are more details to try to make my dilemma clear.
Column A – Name
Column C – the Plan Name. This is what I would like to group the rows by
Column G – This is the cost for each item. I would like to add together all of the Costs that are part of the same group.
So this is the ‘Pre’ View of the Data:
Col A Col B Col C Col D Col E Col F Col G
BoB Smith 512167 ZZ 2005-05-02-07:00 26859 11.1 10500
ZZ 2007-11-09-08:00 30404 12.47 200000
AA 2000-12-20-08:00 8331 23.1875 2500
ZZ 2002-07-01-07:00 20288 5.88 2000
ZZ 2003-07-01-07:00 21494 10.34 3000
ZZ 2004-09-28-07:00 23715 13.18 23869
AA 2004-09-28-07:00 23674 13.18 6131
ZZ 2005-01-17-08:00 25071 23.02 15
ZZ 2005-01-17-08:00 26031 15.9 187
ZZ 2005-02-28-08:00 26513 13.35 15000
ZZ 2008-06-02-07:00 31239 10.59 150000
This is the Post – which shows the items sorted by Col C value, then Subtotaled per Group, and then with a Grand Total at the bottom.
Col A Col B Col C Col D Col E Col F Col G
BoB Smith 512167 AA 2000-12-20-08:00 8331 23.1875 2500
AA 2004-09-28-07:00 23674 13.18 6131
AA Total 8631
ZZ 2002-07-01-07:00 20288 5.88 2000
ZZ 2003-07-01-07:00 21494 10.34 3000
ZZ 2004-09-28-07:00 23715 13.18 23869
ZZ 2005-01-17-08:00 25071 23.02 15
ZZ 2005-01-17-08:00 26031 15.9 187
ZZ 2005-02-28-08:00 26513 13.35 15000
ZZ 2005-05-02-07:00 26859 11.1 10500
ZZ 2007-11-09-08:00 30404 12.47 200000
ZZ 2008-06-02-07:00 31239 10.59 150000
ZZ Total 404571
Grand Total 413202
Help is much appreciated in my time of need! I have attached the data to this thread for easier viewing.
CDD
I’ve Been searching through the forums and I have seen similar posts to what I am looking for, but not all in the same post, so my code is continually not working properly. Any help is greatly appreciated as this is vexing me! I am sure for the experts this is simple(r)
I am using a Webservice to pull data in one worksheet ‘Web Service Data’ and then rearranging this data and placing it into worksheet ‘Report’. I need to Sort on one Column to pull like values together, then add the total from another column, and then finally, to add a Grand Total of all of the totals.
These are all dynamic values as there could be many columns to sort by.
Here are more details to try to make my dilemma clear.
Column A – Name
Column C – the Plan Name. This is what I would like to group the rows by
Column G – This is the cost for each item. I would like to add together all of the Costs that are part of the same group.
So this is the ‘Pre’ View of the Data:
Col A Col B Col C Col D Col E Col F Col G
BoB Smith 512167 ZZ 2005-05-02-07:00 26859 11.1 10500
ZZ 2007-11-09-08:00 30404 12.47 200000
AA 2000-12-20-08:00 8331 23.1875 2500
ZZ 2002-07-01-07:00 20288 5.88 2000
ZZ 2003-07-01-07:00 21494 10.34 3000
ZZ 2004-09-28-07:00 23715 13.18 23869
AA 2004-09-28-07:00 23674 13.18 6131
ZZ 2005-01-17-08:00 25071 23.02 15
ZZ 2005-01-17-08:00 26031 15.9 187
ZZ 2005-02-28-08:00 26513 13.35 15000
ZZ 2008-06-02-07:00 31239 10.59 150000
This is the Post – which shows the items sorted by Col C value, then Subtotaled per Group, and then with a Grand Total at the bottom.
Col A Col B Col C Col D Col E Col F Col G
BoB Smith 512167 AA 2000-12-20-08:00 8331 23.1875 2500
AA 2004-09-28-07:00 23674 13.18 6131
AA Total 8631
ZZ 2002-07-01-07:00 20288 5.88 2000
ZZ 2003-07-01-07:00 21494 10.34 3000
ZZ 2004-09-28-07:00 23715 13.18 23869
ZZ 2005-01-17-08:00 25071 23.02 15
ZZ 2005-01-17-08:00 26031 15.9 187
ZZ 2005-02-28-08:00 26513 13.35 15000
ZZ 2005-05-02-07:00 26859 11.1 10500
ZZ 2007-11-09-08:00 30404 12.47 200000
ZZ 2008-06-02-07:00 31239 10.59 150000
ZZ Total 404571
Grand Total 413202
Help is much appreciated in my time of need! I have attached the data to this thread for easier viewing.
CDD