Sort numeric values based on proximity to a given number
The image above demonstrates an array formula in cell C25 that extracts numbers based on how far off they are from the number in cell C22. Example, 9 and 11 are closest to 10 and are extracted first.
Array formula in C25:
How to create an array formula
- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.
Copy cell C25 and paste it down as far as needed.
Explaining array formula
Step 1 - Find k-th smallest difference to the value in C22
=INDEX($B$25:$B$37, MATCH(SMALL(ABS($B$25:$B$37-$C$22);ROW(A1)), (COUNTIF($B$25:$B$37, $B$25:$B$37)<>COUNTIF($D$24:D24, $B$25:$B$37))*ABS($B$25:$B$37-$C$22), 0))
SMALL(ABS($B$25:$B$37-$C$22), ROW(A1))
becomes
SMALL(ABS({1;3;4;7;8;11;11;14;15;17;18;19;20}-10), ROW(A1))
becomes
SMALL({9;7;6;3;2;1;1;4;5;7;8;9;10}, 1)
and returns 1.
Step 2 - Calculate valid numbers
=INDEX($B$25:$B$37, MATCH(SMALL(ABS($B$25:$B$37-$C$22);ROW(A1)), (COUNTIF($B$25:$B$37, $B$25:$B$37)<>COUNTIF($D$24:D24, $B$25:$B$37))*ABS($B$25:$B$37-$C$22), 0))
(COUNTIF($B$25:$B$37, $B$25:$B$37)<>COUNTIF($D$24:D24, $B$25:$B$37))*ABS($B$25:$B$37-$C$22)
becomes
(COUNTIF({1;3;4;7;8;11;11;14;15;17;18;19;20}, {1;3;4;7;8;11;11;14;15;17;18;19;20})<>COUNTIF("Sorted closest to farthest to 10", {1;3;4;7;8;11;11;14;15;17;18;19;20}))*ABS({1;3;4;7;8;11;11;14;15;17;18;19;20}-10)
becomes
({1;1;1;1;1;2; 2;1;1;1;1;1;1}<>{0;0;0;0;0; 0;0;0;0;0;0;0;0})*ABS({1;3;4;7;8;11;11; 14;15;17;18;19;20}-10)
becomes
({1;1;1;1;1;2; 2;1;1;1;1;1;1}<>{0;0;0;0;0; 0;0;0;0;0;0;0;0})*ABS({1;3;4;7;8;11;11; 14;15;17;18;19;20}-10)
becomes
({TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE})*{9;7;6;3;2;1;1;4;5;7;8;9;10}
and returns
{9;7;6; 3;2;1; 1;4;5; 7;8;9; 10}
Step 3 - Match k-th smallest difference to valid numbers
=INDEX($B$25:$B$37, MATCH(SMALL(ABS($B$25:$B$37-$C$22);ROW(A1)), (COUNTIF($B$25:$B$37, $B$25:$B$37)<>COUNTIF($D$24:D24, $B$25:$B$37))*ABS($B$25:$B$37-$C$22), 0))
MATCH(SMALL(ABS($B$25:$B$37-$C$22);ROW(A1)), (COUNTIF($B$25:$B$37, $B$25:$B$37)<>COUNTIF($D$24:D24, $B$25:$B$37))*ABS($B$25:$B$37-$C$22), 0)
becomes
MATCH(1,{9;7;6; 3;2;1; 1;4;5; 7;8;9; 10}, 0)
returns 6.
Step 4 - Return a value of the cell at the intersection of a particular row and column
=INDEX($B$25:$B$37, MATCH(SMALL(ABS($B$25:$B$37-$C$22);ROW(A1)), (COUNTIF($B$25:$B$37, $B$25:$B$37)<>COUNTIF($D$24:D24, $B$25:$B$37))*ABS($B$25:$B$37-$C$22), 0))
becomes
=INDEX($B$25:$B$37, 6)
becomes
=INDEX({1;3;4;7;8;11;11;14;15;17;18;19;20}, 6)
returns number 11.
Download Excel *.xlsx file
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 […]
Sort column based on frequency
Question: How do I create a new unique distinct list from a column. I also want the list sorted from large […]
Sort text cells alphabetically from two columns
Table of Contents Sort text from two columns combined (array formula) Sort text from multiple cell ranges combined (user defined […]
Denisa asks: I have a problem and i cant figure it out, even if i'm seraching for 2 days. I […]
Sort values in an Excel table programmatically [VBA]
This article demonstrates how to sort a specific column in an Excel defined Table based on event code. The event […]
Sort values in a cell based on a delimiting character [VBA]
This article demonstrates a macro that allows you to sort delimited data in a cell or cell range from A […]
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.