We help IT Professionals succeed at work.

Easy VB script for excel query (1)

Medium Priority
509 Views
Last Modified: 2012-05-06
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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2015

Commented:
So if i understand correctly at the moment you just want to d it for apples right....??

Author

Commented:
No, if each column, but I guess the code for apples would be replicated for all the product lines?

Thanks.
CERTIFIED EXPERT
Top Expert 2015
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT
Top Expert 2015

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

Copy-of-INGHFS---RAW-Experts-Exc.xls

Author

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.
CERTIFIED EXPERT
Top Expert 2015

Commented:
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...

Author

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.
CERTIFIED EXPERT
Top Expert 2015

Commented:
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...

Author

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.

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.