Transcripts for the above video clip:

AUTOMATIC SORTING PROJECT TRANSCRIPT

In Excel, it is possible to rank a piece of information in a list so you can see where it sits. However you may want Excel to sort your list into the appropriate order.
I am not a big fan of using Macros if it can be done at all automatically. And in this segment, you will learn how to find and activate the relevant functions being Rank and VLOOKUP and how to combine these functions to automatically sort lists in Excel.

In this example we have a list of Sales People, their Units Sold, Average Sales Price, and therefore what their Total Sales were. What we’d like to do is to first Rank how they’ve done in terms of total sales, but then we’d like Excel as well to give us the final rankings in the correct order, pulling through all the correct information that needs to come through.

To do this you would do the following, the first thing we need to do is to Rank the list of sales people, and we need to put this in front of the list, the reason for this will become clear with the VLOOKUP section, if you are unfamiliar with the Rank feature, there is information available on our website.
So what you do is;

• You go to Function Wizard
• You find the Rank feature
• You fill in the criteria
• So the number is here
• The reference is what we want to compare to
• You make it an absolute reference
• And the order is descending
• So we want to put a 0 in there
• We say ok
• Copy it down
• And now you’ve go the ranking showing who is first, who is last

You now want Excel to pull through this information, sort it automatically, and pull through the relevant cells. In order to do this,

• in the first column you’ll put the order that you want the ranks to come through in,
• so we want 1,2, 3 etc
• then in a cell like this you make use of the VLOOKUP function, if you are unfamiliar the VLOOKUP function there is training on the website
• find the VLOOKUP function
• the lookup value will be this number, and because you know you are going to be copying this across, you need to anchor it, and in this case you want to anchor it in such a way that it always refers to column A,
• so we want it to be $A13
• the table array is the table you want it to look at, and this is the reason why you needed the ranking in the first column, because the first column needs to be the LOOKUP value
• highlight it
• anchor it
• the column you want it to look through, at the moment you want the sales persons name to come up through, which is column 2, type it in
• the range lookup is false you want an exact match
• and you click ok
• now what you can do is you can copy it across
• and you’ll see its still pulling column 2
• you just need to go in and say pull through column 3
• you can do that with all of these
• so this you want to be column 4
• and this 5
• you can highlight the whole lot
• copy it down
• and there you have your rankings pulled through in the correct order with all the relevant information

What this means now is, you have a listing that automatically sorts itself, so for example at the moment Antoinette is at the top with 100 units. But lets say it was a finger typing error and it should’ve only been 10 units

• When you type in 10
• and u push enter, watch down here
• you’ll see its automatically sorted so that Jane is now number 1, and Antoinette has been put right to the bottom
• and so depending on what happens here, your listing will always be in the correct order

There are some risks with using this technique. One of the main problems is the way Excel handles situations where the ranking is exactly the same. So for example you’ll see Mary has sold 67 units and Cary has sold 67 units;

• if we made their pricing the same,
• you’ll see there is a problem down here, the reason for this is the ranking feature has said, well this is 4th and this is also 4th, and the 5th position has disappeared
• so in our listing here, it pulls through the first four rankings it finds, and because it cannot find a ranking with 5th , it pulls through a not applicable error.
• now although this is a problem, there are ways to address this and that is subject of separate training, but at least when you use the false criteria in the VLOOKUP, if it cannot find a solution it brings through an error message which you can clearly see and address

Another potential risk with this technique is that VLOOKUP makes use of a lot of memory in a spreadsheet. And so if you perform this technique on a very large list you will find that your spreadsheet will increase in size exponentially