No, if each column, but I guess the code for apples would be replicated for all the product lines?
Thanks.
Main Topics
Browse All TopicsI need a relatively simple VB script/macro to be coded for me please.
I have 9 product lines (including the grand total column) in the attached spreadsheet which contain revenue generated data. I need each of these product columns ranked in order of highest to lowest and then a new column to be inserted next to that product line column.
In that new column should be the cumulative values of all of the figures, dragged down to the last row in that column.
Example from attached spreadsheet
A new column should be inserted after the column titled Oranges (lets call the new column Apples ranked cumulative. The first cell (the new D2) would be 1,015,334 whilst the second one would be 993,932 + 1,015,334 etc all the way down. The data in this new column should also be saved as a value and not relative, because the same should be replicated for the other columns with their titles shown using the above procedure, this way when the columns are reordered the values in the new column will not change.
Oh and please name the macro "PR"
Thanks.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Hi
Many thanks for your post. We're moving in the right direction. I should have made something clear which I forgot to state but its a bit complicated to explain and quite important.
After each new cumulative column is created, the cumulative numbers that get populated need to be based on the product line after that specific product line has been ranked from highest to lowest (for reasons I will explain later in a new question posting with another 500 points).
If I sort the columns by each of the product lines separately, I need to be able to see the cumulative column increase as the product line P&L reduces (ranked higher to lower).
- The Apples product line is correct and the cumulative apples column is correct.
- The VB macro should then sort the Orange product column from highest to lowest (whilst of course keeping the entire client row information intact). Once this column is ranked, the macro should then cumulatively add all the Oranges P&L (as you have done with Apples).
- When that is complete, it then needs to rank from highest to lowest the Pears product column and then cumulatively add that
- etc etc until all the product lines are done in the same fashion as above
- The final column should be "Total Cumulative" so we are missing that column.
So basically the cumulative figure has to be based on the correct P&L ranking of the cell to its left. Hope that is clear! Many thanks and again I'll explain the rationale for doing this in a new 500 point question which this code will be based on as the overall solution will be split into 2 or 3 parts to award more points for this tricky request.
Thanks.
No, let me clarify further. The purpose of the sorting of each column, is only so that the right cumulative figure is matched against the correct P&L in the product column.
I am expecting the order/sorting of the Apples column to be mssed up once the sorting of the Orange column is done (and ditto for each subsequent product column). However, as each time it is sorted, the previous cumulative figure will be matched with the correct previous product P&L.
And before you ask me what difference it makes as to which cumulative figure is attached to the ranked P&L product cell, trust me it will make senese when I post the second 500 point question! I have to do these procedures manually but I'm refraining from going into too much detail because my previous posting of the FULL 500 point question got zero response due to the complexity, so am submitting in smaller tasks.
Thanks again.
Here was the original question but I guess not easy to follow. Background info for you anyway.
http://www.experts-exchang
Here is the second question link
http://www.experts-exchang
Thanks.
Business Accounts
Answer for Membership
by: saurabh726Posted on 2009-02-18 at 16:14:44ID: 23676617
So if i understand correctly at the moment you just want to d it for apples right....??