Easy VB script for excel query (1)

I 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.
Copy-of-INGHFS---RAW-Experts-Exc.xls
inghfsAsked:
Who is Participating?
 
Saurabh Singh TeotiaConnect With a Mentor Commented:
There you go..use this code and it will do what you are looking for....
Saurabh...

Sub cumulative()
    Application.ScreenUpdating = False
    Columns("D:D").Insert Shift:=xlToRight
    Range("D5").Select
    ActiveCell.Formula = "=""Cumulative ""&C5"
    Range("D6").Formula = "=C6"
    Range("D7:D" & Cells(65536, "C").End(xlUp).Row).Formula = "=D6+C7"
    Columns("D:D").Copy
    Columns("F:F").Insert Shift:=xlToRight
    Columns("F:F").Copy
    Columns("F:F").PasteSpecial Paste:=xlValues
    Columns("D:D").Copy
    Columns("H:H").Insert Shift:=xlToRight
    Columns("H:H").Copy
    Columns("H:H").PasteSpecial Paste:=xlValues
    Columns("D:D").Copy
    Columns("J:J").Insert Shift:=xlToRight
    Columns("J:J").Copy
    Columns("J:J").PasteSpecial Paste:=xlValues
    Columns("D:D").Copy
    Columns("L:L").Insert Shift:=xlToRight
    Columns("L:L").Copy
    Columns("L:L").PasteSpecial Paste:=xlValues
    Columns("D:D").Copy
    Columns("N:N").Insert Shift:=xlToRight
    Columns("N:N").Copy
    Columns("N:N").PasteSpecial Paste:=xlValues
    Columns("D:D").Copy
    Columns("P:P").Insert Shift:=xlToRight
    Columns("P:P").Copy
    Columns("P:P").PasteSpecial Paste:=xlValues
    Columns("D:D").Copy
    Columns("R:R").Insert Shift:=xlToRight
    Columns("R:R").Copy
    Columns("R:R").PasteSpecial Paste:=xlValues
    Columns("D:D").Copy
    Columns("D:D").PasteSpecial Paste:=xlValues
    Cells.Select
    Selection.EntireColumn.AutoFit
    Application.CutCopyMode = False
 
    Range("A1").Select
    MsgBox "Done"
    Application.ScreenUpdating = True
 
End Sub

Open in new window

0
 
Saurabh Singh TeotiaCommented:
So if i understand correctly at the moment you just want to d it for apples right....??
0
 
inghfsAuthor Commented:
No, if each column, but I guess the code for apples would be replicated for all the product lines?

Thanks.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Saurabh Singh TeotiaCommented:
Your file post i have run macro over it...
Saurabh...

Copy-of-INGHFS---RAW-Experts-Exc.xls
0
 
inghfsAuthor Commented:
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.
0
 
Saurabh Singh TeotiaCommented:
Well you cant do that...because if you do that sorting your apple sorting would go for toss...because it might not be necessary that what is the highest for Oranges is highest for apple as well..so you have to stick with 1 column sorted out in descnding order...
0
 
inghfsAuthor Commented:

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.
0
 
Saurabh Singh TeotiaCommented:
Okay...so i guess i have taken care of your first request..and im completely lost about your second request...for that request you to please open a related question..explaining in detail about what you are trying to do...
0
 
inghfsAuthor Commented:
Here was the original question but I guess not easy to follow.  Background info for you anyway.
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_24148613.html

Here is the second question link
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_24159462.html 

Thanks.

0
All Courses

From novice to tech pro — start learning today.