How to sort a data set using three different approaches, built-in tools, array formulas, and VBA
I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts a data set using array formulas and explain in detail how the formulas work. Why would you want to sort a table using array formulas when Excel has tools built-in that works great? Formulas are dynamic and change instantly when new values are added.
What's on this page
If you have built an interactive worksheet or Dashboard you may have form controls, active-x controls, drop-down Lists or something else connected to your data. The Excel user may not be happy to manually sort values using the features Excel provides.
It may be tedious and time-consuming to sort values each time conditions change or data is added even if built-in tools is used.
The sorted table to the right is created with an array formula using the data in the table to the left. It is sorted by the first column and then by the second column and lastly the third column. The order is from A to Z.
The second techniques is to manually sort the data set using the built in tools Excel provides.
The last technique I will demonstrate is to use a macro that sorts values based on certain conditions like if a value is changed or a worksheet is activated etc, it is event code that makes this possible. They are not placed on the same module as regular macros but in worksheet or workbook modules which I will also describe in detail in this article.
Sort using array formulas
The following formula adapts to as many as there are rows in the Excel Table making this feasible for any size of data set. If you have huge amounts of data I recommend using the VBA macro instead, it will be much faster.
Update! Excel 365 dynamic array formula in cell F3:
The SORTBY function sorts a cell range or array based on values in a corresponding range or array.
Function syntax: SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)
Array formula in cell F3:
How to enter an array formula
- Copy array formula.
- Select cell F3.
- Press with left mouse button on in formula bar.
- Paste array formula to formula bar.
- Press and hold CTRL + SHIFT.
- Press Enter.
How to copy array formula
- Select cell F3.
- Copy cell (Ctrl +c).
- Select cell range G:3:H3.
- Paste (Ctrl + v).
- Select cell range F3:H3.
- Copy (Ctrl + c).
- Select cell range F4:H8.
- Paste (Ctrl + v).
Explaining array formula in cell F3
Step 1 - Sort Col B from A to Z using the COUNTIF function
The COUNTIF function counts cells based on a condition, however, using a different approach with a less than sign you can create an array containing numbers representing the sort order.
The relative position of each number in the array corresponds to the same value in cell range $B$3:$B$8.
COUNTIF($B$3:$B$20, "<"&$B$3:$B$20)
becomes
COUNTIF({"A"; "A"; "A"; "A"; "A"; "A"; "A"; "A"; "A"; "A"; "A"; "A"; "A"; "A"; "A"; "A"; "A"; "A"}, {"<A";"<A";"<A";"<A";"<A";"<A";"<A";"<A";"<A";"<A";"<A";"<A";"<A";"<A";"<A";"<A";"<A";"<A"})
and returns
{0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}.
Step 2 - Sort Col C from A to Z with COUNTIF function
This part of the formula also creates an array containing numbers representing the sort order of each item in column C, however, it also divides the numbers with 10, 100 or 1000 etc based on the number of rows in the data set.
The ROWS function returns the number of rows in a cell range or a structured reference meaning a cell reference pointing to an Excel Table.
COUNTIF($C$3:$C$20, "<"&$C$3:$C$20)/(10^CEILING(LOG10(ROWS(Table1[Col B])), 1))
becomes
COUNTIF($C$3:$C$20, "<"&$C$3:$C$20)/(10^CEILING(LOG10(18), 1))
The next steps rounds the number to the nearest power of 10. Example, 5 returns 10. 99 returns 100. 101 returns 1000 and so on.
The LOG10 function calculates the logarithm of a number using the base 10.
COUNTIF($C$3:$C$20, "<"&$C$3:$C$20)/(10^CEILING(LOG10(18), 1))
becomes
COUNTIF($C$3:$C$20, "<"&$C$3:$C$20)/(10^CEILING(1.25527250510331, 1))
The CEILING function rounds a number up to its nearest multiple.
COUNTIF($C$3:$C$20, "<"&$C$3:$C$20)/(10^CEILING(1.25527250510331, 1))
becomes
COUNTIF($C$3:$C$20, "<"&$C$3:$C$20)/(10^2)
The ^ character works just like the POWER function, it calculates a number raised to a power.
COUNTIF($C$3:$C$20, "<"&$C$3:$C$20)/(10^2)
becomes
COUNTIF($C$3:$C$20, "<"&$C$3:$C$20)/100
and returns
{0.17; 0.16; 0.15; 0.14; 0.13; 0.12; 0.11; 0.1; 0.09; 0.08; 0.07; 0.06; 0.05; 0.04; 0.01; 0.03; 0.01; 0}.
Step 3 - Sort Col D from A to Z with COUNTIF function
Column D is the third and last column in the data set.
COUNTIF($D$3:$D$20, "<"&$D$3:$D$20)/(10^CEILING(LOG10(ROWS(Table1[Col B])), 1))^2
becomes
COUNTIF($D$3:$D$20, "<"&$D$3:$D$20)/100^2
becomes
COUNTIF($D$3:$D$20, "<"&$D$3:$D$20)/10000
and returns
{0; 0.0002; 0.0003; 0.0004; 0.0005; 0.0006; 0.0007; 0.0008; 0.0009; 0.001; 0.0011; 0.0012; 0.0013; 0.0014; 0; 0.0015; 0.0016; 0.0017}.
Step 4 - Add all arrays
The plus character allows you to add arrays, we have three arrays in this calculation.
COUNTIF($B$3:$B$20, "<"&$B$3:$B$20)+COUNTIF($C$3:$C$20, "<"&$C$3:$C$20)/(10^CEILING(LOG10(ROWS(Table1[Col B])), 1))+COUNTIF($D$3:$D$20, "<"&$D$3:$D$20)/(10^CEILING(LOG10(ROWS(Table1[Col B])), 1))^2
becomes
{0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0} + {0.17; 0.16; 0.15; 0.14; 0.13; 0.12; 0.11; 0.1; 0.09; 0.08; 0.07; 0.06; 0.05; 0.04; 0.01; 0.03; 0.01; 0} + {0; 0.0002; 0.0003; 0.0004; 0.0005; 0.0006; 0.0007; 0.0008; 0.0009; 0.001; 0.0011; 0.0012; 0.0013; 0.0014; 0; 0.0015; 0.0016; 0.0017}
and returns
{0.17; 0.1602; 0.1503; 0.1404; 0.1305; 0.1206; 0.1107; 0.1008; 0.0909; 0.081; 0.0711; 0.0612; 0.0513; 0.0414; 0.01; 0.0315; 0.0116; 0.0017}.
Step 5 - Return the k-th smallest value
The SMALL function extracts the k-th smallest value from a cell range or array.
SMALL(array, k)
SMALL(COUNTIF($B$3:$B$20, "<"&$B$3:$B$20)+COUNTIF($C$3:$C$20, "<"&$C$3:$C$20)/(10^CEILING(LOG10(ROWS(Table1[Col B])), 1))+COUNTIF($D$3:$D$20, "<"&$D$3:$D$20)/(10^CEILING(LOG10(ROWS(Table1[Col B])), 1))^2, ROW(B1))
becomes
SMALL({0.17; 0.1602; 0.1503; 0.1404; 0.1305; 0.1206; 0.1107; 0.1008; 0.0909; 0.081; 0.0711; 0.0612; 0.0513; 0.0414; 0.01; 0.0315; 0.0116; 0.0017}, ROW(B1))
becomes
SMALL({0.17; 0.1602; 0.1503; 0.1404; 0.1305; 0.1206; 0.1107; 0.1008; 0.0909; 0.081; 0.0711; 0.0612; 0.0513; 0.0414; 0.01; 0.0315; 0.0116; 0.0017}, 1)
and returns 0.0017
Step 6 - Find the relative position
To be able to get the correct value we need to find the position of the k-th smallest value in the array. The MATCH function returns the relative position.
MATCH(SMALL(COUNTIF($B$3:$B$8, "<"&$B$3:$B$8)+COUNTIF($C$3:$C$8, "<"&$C$3:$C$8)/10+COUNTIF($D$3:$D$8, "<"&$D$3:$D$8)/100, ROW(A1)), COUNTIF($B$3:$B$8, "<"&$B$3:$B$8)+COUNTIF($C$3:$C$8, "<"&$C$3:$C$8)/10+COUNTIF($D$3:$D$8, "<"&$D$3:$D$8)/100, 0)
becomes
MATCH({0.0017}, {0.17; 0.1602; 0.1503; 0.1404; 0.1305; 0.1206; 0.1107; 0.1008; 0.0909; 0.081; 0.0711; 0.0612; 0.0513; 0.0414; 0.01; 0.0315; 0.0116; 0.0017}, 0)
and returns 18.
Step 7 - Return the corresponding value
The INDEX function is able to fetch the value from a given cell range based on arow and column number.
INDEX($B$3:$D$20, MATCH(SMALL(COUNTIF($B$3:$B$20, "<"&$B$3:$B$20)+COUNTIF($C$3:$C$20, "<"&$C$3:$C$20)/(10^CEILING(LOG10(ROWS(Table1[Col B])), 1))+COUNTIF($D$3:$D$20, "<"&$D$3:$D$20)/(10^CEILING(LOG10(ROWS(Table1[Col B])), 1))^2, ROW(B1)), COUNTIF($B$3:$B$20, "<"&$B$3:$B$20)+COUNTIF($C$3:$C$20, "<"&$C$3:$C$20)/(10^CEILING(LOG10(ROWS(Table1[Col B])), 1))+COUNTIF($D$3:$D$20, "<"&$D$3:$D$20)/(10^CEILING(LOG10(ROWS(Table1[Col B])), 1))^2, 0), COLUMN(A1))
becomes
INDEX($B$3:$D$20, 2, COLUMN(A1))
becomes
=INDEX($B$3:$D$20, 2, 1)
and returns A in cell F3.
Sort using built-in "Sort & Filter" feature
You can also use Excel's Sort feature which is located on the "Home" tab on the ribbon.
The difference with this approach is that it is manual, you need to apply the following steps each time you add/delete or edit values contrary to the VBA macro and formulas.
Follow these steps:
- Select the entire data set including the header names.
- Go to tab "Home" on the ribbon.
- Press with mouse on the "Sort & Filter" button.
- Press with mouse on "Custom Sort..." and a dialog box appears.
- Sort the first column by column B.
- Press with mouse on "Add Level" button.
- Sort the second column by column C.
- Press with mouse on "Add Level" button.
- Sort the third column by column D.
- Press with left mouse button on the OK button to apply changes.
Tip! Press with mouse on "A to Z" to change the sort order from "A to Z" to "Z to A". Disable checkbox "My data has headers" if your data set has no column names.
Sort using VBA
This example demonstrates a button that is linked to a macro, press with left mouse button on the button to start the macro. I will also show you how to run the macro if a cell on the same worksheet has been changed using event code.
VBA code
Sub Macro1() 'Allows you to write shorter code by referring to an object only once instead of using it with each property. With ActiveWorkbook.Worksheets("VBA").Sort 'Clear previous sort conditions .SortFields.Clear 'Apply sorting from A to Z to cell range B3:B8 .SortFields.Add2 Key:=Range("B3:B8"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal 'Apply sorting from A to Z to cell range C3:C8 .SortFields.Add2 Key:=Range("C3:C8"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal 'Apply sorting from A to Z to cell range D3:D8 .SortFields.Add2 Key:=Range("D3:D8"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal 'Set range to B2:D8 .SetRange Range("B2:D8") 'Column names exists .Header = xlYes 'No case sensitivity .MatchCase = False 'Sort orientation xlTopToBottom vs xlLeftToRight .Orientation = xlTopToBottom 'Apply sorting to range .Apply End With End Sub
Where to put the VBA code?
- Press Alt + F11 to open the Visual Basic Editor.
- Press with mouse on "Insert" on the top menu.
- Press with mouse on "Module" to insert a module to your workbook. It is named Module1 in the image above.
- Copy above VBA code.
- Paste to code window.
How to create a button linked to the macro
- Go to tab "Developer" on the ribbon.
- Press with mouse on "Insert Controls" button.
- Press with mouse on "Button" button.
- Press with left mouse button on and drag on the worksheet to create the button. You can always later resize the button.
- A dialog box appears asking for a macro to be assigned. Select the macro you want to use and press with left mouse button on OK.
Event code
Event code allows you to run the sort macro automatically based on specific things that can happen, for instance, activating the worksheet, selecting a cell or in this case, if a cell value has changed.
'Event code that is rund when a cell value in worksheet VBA changes Private Sub Worksheet_Change(ByVal Target As Range) 'Start macro named macro1 Macro1 End Sub
Where to put the Event code?
- Press with right mouse button on on the worksheet tab located at the very bottom of your Excel Screen.
- Press with mouse on "View Code".
- The Visual Basic Editor opens with the worksheet module selected.
- Copy and paste the event code above to the worksheet module.
- Exit VB Editor and return to Excel.
Table category
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table. The animated […]
This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
This article describes a macro that hides specific columns automatically based on values in two given cells. I am also […]
The image above demonstrates a macro linked to a button. Press with left mouse button on the button and the […]
This article demonstrates how to filter duplicate records using a simple formula and an Excel defined table.
I will in this article demonstrate a macro that copies criteria from one Excel Table and applies them to another […]
In this tutorial, I am going to demonstrate how to filter an Excel define Table through a VBA macro. How it […]
This article explains how to filter a data set based on extremely many conditions in an Excel defined Table, in […]
This blog post demonstrates how to filter unique distinct values from an Excel table dynamically. When you change or add […]
Today I am going to demonstrate how amazing pivot tables are! Take a look at this time sheet. You can […]
This article demonstrates how to insert and use a scroll bar (Form Control) in Excel. It allows the user to […]
An Excel table allows you to easily sort, filter and sum values in a data set where values are related.
This article demonstrates a macro that automatically applies a filter to an Excel defined Table based on the result from […]
The filter feature in Excel won't allow you to do OR logic between columns, however, you can if you allow […]
This article explains how to calculate the largest and smallest number based on a condition which is if the number […]
Sort values category
Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]
This article demonstrates ways to extract unique distinct sorted from A to Z ignoring blanks, and based on a condition. […]
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 […]
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 […]
This article demonstrates a formula that sorts cell values by their frequency, in other words, how many times a value […]
Table of Contents Sort text from two columns combined (array formula) How to create an array formula How to copy […]
This article demonstrates a macro and a formula that allows you to sort delimited data in a cell or cell […]
This article demonstrates a formula that sorts items arranged horizontally based on the adjacent numbers, every other column contains a […]
This article demonstrates how to sort a specific column in an Excel defined Table based on event code. The event […]
Question: How do I sort a range alphabetically using excel array formula? Answer: Cell range $B$2:$E$5 contains text values in random […]
The formula in cell B8 extracts a list sorted based on frequency. Array formula in B8: =TEXTJOIN("", TRUE, IF(MIN(IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, […]
Ralee asks in in this blog post: Sort values in parallel (array formula) If there is information in adjacent columns, […]
Overview The array formula in cell range C2:C6 creates a random list from the values in cell range A2:A6. Array […]
In this article, I will demonstrate two techniques for counting per row. The first example is simple and straightforward. The […]
The image above demonstrates a formula in cell D3 that sorts values based on character length, the value with the […]
The following array formula extracts duplicate values sorted from A to Z from cell range B3:B21. Excel array formula in […]
This article describes a formula that sorts values arranged in a column from A to z by every other value. […]
This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
How to use Excel Tables
Excel categories
One Response to “How to sort a data set using three different approaches, built-in tools, array formulas, and VBA”
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.
Ola,
Gostaria de fazer uma classificação de pares e impares para jogos aqui no Brasil.
Assim:
de c3 até q3000 quinze dezenas (numeros)
de U3 em diante separar Impares e Pares
Desde ja agradeço
Rogerio