How to sort data with a macro

As we spend more and more time at the PC nowadays, it makes sense to put it to the best use possible. Computers are very good at doing repetitive tasks, very quickly. Using macros allows you to tap into this without learning any programming. They, essentially, record what you do, and allow you to tell the computer to repeat this at the press of a button. We will go through a simple example here of re-sorting some columns of data.

Automating tasks using MACROS

The Christmas holidays have passed and at our fitness centre, Working-Out Data, it's time for January Promotions and Tax Returns. The manager, Loretta, has made a New Year's Resolution to get better at keeping on top of completing her tasks and knowing what's still left to do. She is keeping the ’To Do’ list in a spreadsheet. See Spreadsheet Data 1.

Spreadsheet Data 1

After one of the tasks is completed, Loretta changes the status to ’Done’. See Spreadsheet Data 2.

Spreadsheet Data 2

In order to be able to quickly see the tasks still ’To Be Done’, she sorts the list to put all the ’Done’ items to the bottom. First, she highlights the data to be sorted, then selects Sort from the Data menu. See Spreadsheet Data 3.

Spreadsheet Data 3

This brings up the ’Sort’ dialog box. We want to sort on ’Progress’ and in a ’Descending’ order as ’T’ comes after ’D’ in the alphabet. See Spreadsheet Data 4.

Spreadsheet Data 4

Clicking on OK sorts the data by the ’Progress’ column. We see that the ’Done’ tasks have moved to the bottom. See Spreadsheet Data 5.

Spreadsheet Data 5

Sorting data can be automated by creating a macro. Each time Loretta needs to re-sort the data she will be able to do it with one click of a button. Macros can be written using code, or, as in this example, the programme will record your actions and convert them to code for you. To record the macro we go the Tools menu, then select Macro, then Record New Macro. See Spreadsheet Data 6.

Spreadsheet Data 6

This brings up the ’Record Macro’ dialog box, as in Spreadsheet Data 7. We give the macro a name. Here, we have called it ’resort’. Click OK.

Spreadsheet Data 7

This will take us back to the spreadsheet, along with a small box which contains the ’STOP’ button. We will click this once we have finished the task we want to record. The first step in the process is to select the data to be sorted, as in Spreadsheet Data 8.

Spreadsheet Data 8

We next go up to the DATA menu and select SORT, as in Spreadsheet Data 9.

Spreadsheet Data 9

This brings up the ’Sort’ dialog box. We again select ’Progress’ and Descending, as in Spreadsheet Data 10, then click OK.

Spreadsheet Data 10

We now click the STOP button on the small box which came up when we started recording the macro, as in Spreadsheet Data 11.

Spreadsheet Data 11

Now the programme contains a macro which will sort our data in one go. Next, we add a button to the toolbar and assign the macro to it. To start this, we customise the toolbar. From the VIEW menu, select TOOLBARS, then CUSTOMIZE, as in Spreadsheet Data 12.

Spreadsheet Data 12

This brings up the ’Customize’ dialog box. Select the Commands tab, then in the left hand section scroll down to Macros and select it. In the right hand section there will then be two options, as in Spreadsheet Data 13. We want the Custom Button option. Click and drag the smiley face up on to the toolbar.

Spreadsheet Data 13

Leave the ’Customize’ dialog box open, and right-click on the smiley you just dragged on to the toolbar. At the bottom of the menu, select ASSIGN MACRO, as in Spreadsheet Data 14.

Spreadsheet Data 14

This will bring up the ’Assign Macro’ dialog box. Select the ’resort’ macro from the list, then click OK.

Spreadsheet Data 15

Next time one of the tasks is done, and Loretta updates the list, she only needs to click on the Smiley face button on her toolbar and the list will be automatically sorted. In Spreadsheet Data 16, we change the progress for ’Cancel the Milk’ to ’Done’.

Spreadsheet Data 16

Now clicking on the Smiley face button in the toolbar will resort the data automatically. As in Spreadsheet Data 17.

Spreadsheet Data 17

I hope that this brief introduction to macros and automation of work have helped to show their potential for automating tasks and, therefore, increasing productivity. The example we went through here was very simplistic, but macros can be used to perform very complex calculations and lengthy tasks. This idea of a To Do list is especially useful if you are adding tasks to a list for someone else, and they can quickly see what is to be done. If you need assistance with setting up or ammending macros, Working Data can be contacted by clicking here.

..back to top