• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sort Dataset Descending & Back to Original Order

H

hennakao

New Member
Hi all

I was wondering if someone can help me with some macro wizardry for my Dashboard project. Full disclosure, I have previously posted this on Excelforum but haven't received a response...

I have cost data divided into two groups (direct/indirect expenses - Ranges B3:H7 and B11:H15). In each of the cost groups there are two separate columns showing the percentage delta between 1) the previous year and the current run-rate and 2) the percentage change month-on-month.

The goal is to enable the user to sort both of these columns in descending order via a macro assigned to shapes. A third shape should undo the sorting and change the data back to original order.

I am a beginner with macros and therefore have no idea how to accomplish this. Here is my attempt which is covering only one part of what I am trying to achieve.

Sub sb_VBA_Sort_Data_Descending()
With Worksheets("Data")
Range("B4:H7").Sort _
Key1:=Range("H3"), Order1:=xlDescending
End With
End Sub

I would appreciate any help I could get.

Thank you!
 

Attachments

  • Sort Macro.xlsm
    20.4 KB · Views: 1
Last edited:
... what was original Sort order?
Could this work for You?
Read 'yellow' instruction before ...
 

Attachments

  • Sort Macro.xlsm
    25 KB · Views: 5
vletm said:
... what was original Sort order?
Could this work for You?
Read 'yellow' instruction before ...
Click to expand...
This is exactly what I was after! Two questions:
  1. Is it possible to colour code the selected headers with RGB code 255/192/0?
  2. Could you quickly run me through the code and let me know where i can amend the data range to make it fit for purpose for my original dashboard?
Thank you!
 
1) Yes. Now, You can select any colors to [A1]&[A2], both Des/Asc has own color
2) Quicly .. hmm .. No .. Yes! I add notes to code!
 

Attachments

  • Sort Macro.xlsm
    21.7 KB · Views: 6
vletm said:
1) Yes. Now, You can select any colors to [A1]&[A2], both Des/Asc has own color
2) Quicly .. hmm .. No .. Yes! I add notes to code!
Click to expand...

Hmm...maybe I am doing something wrong, but when I open the workbook and select any of the headers nothing happens. Stepped into the VBA editor and hit F5, but there is no Macro to select. Any idea what I am doing wrong?

Thank you.
 
... hmmm ...
Did You copy that Macro to Your 'own' file?
...and to 'Data'-sheet's code-page?
Your 'dashboard's sheet name have to be 'Data'!
.. as You asked hints about 'data ranges' ... hmm?
Have You checked via 'Project'-table that sheet's 'View-Code'?
Normally You cannot see 'Private Sub' anywhere else ...
> I can only close my eyes and try to get any vision of Your view of PC...
>> without file, it is sometimes a challenge...
 
vletm said:
... hmmm ...
Did You copy that Macro to Your 'own' file?
...and to 'Data'-sheet's code-page?
Your 'dashboard's sheet name have to be 'Data'!
.. as You asked hints about 'data ranges' ... hmm?
Have You checked via 'Project'-table that sheet's 'View-Code'?
Normally You cannot see 'Private Sub' anywhere else ...
> I can only close my eyes and try to get any vision of Your view of PC...
>> without file, it is sometimes a challenge...
Click to expand...

I have not yet applied it to my live Data sheet. Just downloaded the file with the updated code to see how it works, but nothing happens. I will figure it out somehow.
 
vletm said:
... hmmm ...
Did You copy that Macro to Your 'own' file?
...and to 'Data'-sheet's code-page?
Your 'dashboard's sheet name have to be 'Data'!
.. as You asked hints about 'data ranges' ... hmm?
Have You checked via 'Project'-table that sheet's 'View-Code'?
Normally You cannot see 'Private Sub' anywhere else ...
> I can only close my eyes and try to get any vision of Your view of PC...
>> without file, it is sometimes a challenge...
Click to expand...
Vletm, whilst I got the sample file to work I am having some issues with the definition of the Data ranges in my original dashboard file.

  1. I have not considered that my live file consists cost lines that should be excluded from sorting, for the simple reason that these items are summary account lines because these lines are top level items (highlighed bold in attached sheet)
  2. All lines which should be sorted both ways can also be vertically scrolled to show all cost details if required. However I only show the top 5 items per cost window. Would your code still work, despite the verticall scroll bars?
Could you kindly look at the attached file and see whether this still could be done.

Thank you v much!
 

Attachments

  • Cost Dashboard Code.xlsm
    26.8 KB · Views: 3
... hmm ... and You sent .xlsx -file
... .xlsx do not have Macros, Okay?
... see whether this still could be done.
>> Check this version...
 

Attachments

  • Cost Dashboard Code.xlsb
    30.1 KB · Views: 7
vletm said:
... hmm ... and You sent .xlsx -file
... .xlsx do not have Macros, Okay?
... see whether this still could be done.
>> Check this version...
Click to expand...
vletm, I have looked at the code, added it to my dashboard and it works! Massive thanks for your patience. One thing though, the sorting only works if i double click the respective header cell, whereas in your last file it works on a single click.
 
Single or double click ... cannot think nothing without file.
Previous file works with activating any header in my computer.
 
vletm said:
Single or double click ... cannot think nothing without file.
Previous file works with activating any header in my computer.
Click to expand...

To make matters easier I have now attached attached the original dashboard but have changed the numbers and account lines.

As you can see the code works, but the sort order only changes when double clicking. The other thing that I have just noticed is that the macro only sorts the top visible items. If you vertically scroll the data the sorting is out of order. Maybe the macro needs to be applied to the workings sheet where i pull together and calculate all the data...or is there a way to make it work as is?

Would appreciate your help if you have time.Thank you!
 

Attachments

  • Dashboard_NEW.xlsb
    405 KB · Views: 2
1) There were 'few links' ... it makes more useful if all data in same file.
2) It work as I wrote ... activate any header and wait 'short time'.
Maybe it would be quicker is set calculation manual while sorting.
3) OOOO .. shorting only top ... of course ...
so far You can select above OR below items from their headers!
>> IDEAS?
 
vletm said:
1) There were 'few links' ... it makes more useful if all data in same file.
2) It work as I wrote ... activate any header and wait 'short time'.
Maybe it would be quicker is set calculation manual while sorting.
3) OOOO .. shorting only top ... of course ...
so far You can select above OR below items from their headers!
>> IDEAS?
Click to expand...
Have cleaned up some legacy items in the Name Manager and also set the calculations to manual. This makes a big difference in terms of performance and it works on a "single click".
Sorting the top items is good, but sorting every item in the list would be the real killer. I grateful for your help thus far and would be one happy camper if this can be done. ;)
 
Next version ...
1) Row 1 is for 'default colors (background & font)' - It can hide!
2) Activating cell form row 17 or 29 range from C to K will change sort to ALL
... actually this sorts now 'Working'-sheet!
> Ideas ... Questions?
 

Attachments

  • Dashboard_NEW.xlsb
    462.4 KB · Views: 4
vletm said:
Next version ...
1) Row 1 is for 'default colors (background & font)' - It can hide!
2) Activating cell form row 17 or 29 range from C to K will change sort to ALL
... actually this sorts now 'Working'-sheet!
> Ideas ... Questions?
Click to expand...
You've nailed it!
 
You must log in or register to reply here.
Back
Top