How to sort a data set in a custom order
Your boss wants you to sort the company's products by a new criterion, quality. You receive a list from your boss and now you have to sort products by this list, displayed in the above picture in cell range G3:G9.
This may seem to be a task that could take all week if you are working with a large list, however, Excel is great at these things. This task can easily be completed in under a minute.
Formula in cell F13:
The MATCH function finds the relative position of an item in the list made by your boss. You then simply sort the new list from small to large, see detailed instructions below.
MATCH(lookup_value, lookup_array, [match_type]
lookup_value: B3 is a relative cell reference. This cell reference changes when the formula is copied. Learn more about absolute and relative cell references:
lookup_array: Sheet2!$B$3:$B$9 is an absolute cell reference. It doesn't change when copied. You toggle cell references by selecting the cell reference in the formula bar and then press F4.
What happens in cell F13 when calculated?
MATCH(B3, Sheet2!$G$3:$B$G, 0)
becomes
MATCH("A", {"F"; "D"; "B"; "A"; "C"; "E"; "G"}, 0)
and returns 4 in cell F13. Value "A" has position four in the lookup_array {"F"; "D"; "B"; "A"; "C"; "E"; "G"}.
The third argument in the MATCH function determines if it should get an exact match (not case sensitive).
Copy formula
- Select cell F13.
- Copy cell F13 (Ctrl + c).
- Select cell range F14:F19.
- Paste (Ctrl + v).
Sort data
- Press with right mouse button on on cell F13.
- Press with left mouse button on Sort
- Press with left mouse button on "Sort Smallest to Largest"
Weekly Blog EMAIL
[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
Remove common records between two data sets
This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]
Filter shared records from two tables
I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]
Unique distinct records sorted based on count or frequency
Sara asks: How can you use large with multiple criteria?? Example looking for top 5 of a list based on […]
Compare two lists of data: Highlight common records
In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]
Compare tables: Highlight records not in both tables
The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two […]
Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]
Extract a unique distinct list sorted from A to Z ignore blanks
The image above demonstrates a formula in cell D3 that extracts unique distinct numbers and text values sorted from A […]
Sort dates within a date range
Array formula in D5: =SMALL(IF(($B$3:$B$12<=$E$3)*($B$3:$B$12>=$E$2), $B$3:$B$12, "A"), ROWS($A$1:A1)) How to create an array formula Copy array formula Select cell D5 […]
Lookup and return multiple sorted values based on corresponding values in another column
This article demonstrates a formula that extracts values based on a condition and sorts the returned values based on values […]
The image above shows a table with two columns in cell range B3:C16, it contains random text values in column […]
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
