Excel – Sort using array formulas
Create a new sorted list from a column using array formula
Recommended articles
Sort a column alphabetically
Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]
Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]
Recommended articles
Recommended articles
Recommended articles
Extract a unique distinct list sorted from A to Z
The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]
The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]
Create a new sorted list from two columns using array formula
Recommended articles
Sort text cells alphabetically from two columns
Table of Contents Sort text from two columns combined (array formula) How to create an array formula How to copy […]
Table of Contents Sort text from two columns combined (array formula) How to create an array formula How to copy […]
Create a new sorted list from a range using array formula
Recommended articles
Sort a range from A to Z [Array formula]
Question: How do I sort a range alphabetically using excel array formula? Answer: Cell range $B$2:$E$5 contains text values in random […]
Question: How do I sort a range alphabetically using excel array formula? Answer: Cell range $B$2:$E$5 contains text values in random […]
Recommended articles
Extract unique distinct values A to Z from a range and ignore blanks
This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]
This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]
Misc
Recommended articles
Sort a range based on value frequency
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, […]
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, […]
Recommended articles
Sort text values by length
The image above demonstrates a formula in cell D3 that sorts values based on character length, the value with the […]
The image above demonstrates a formula in cell D3 that sorts values based on character length, the value with the […]
Recommended articles
Recommended articles
Recommended articles
Recommended articles
Split values equally into groups
Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]
Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]
Recommended articles
Dates
Recommended articles
Recommended articles
Recommended articles
Recommended articles
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 […]
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 one did not seem to work. in your excel example file I imputed: a, h,II,b,j,k,c,l,m,d,n,f,p in column A.
The result was
a,a,b,c,d,f,h,II,j,k,l,m,n
but there are not two a's in column a and it seemed to drop p completely... it seems some how shifted... can you help? Thanks.
oops... I did not mean to leave my number for anyone other than you, can you remove it? (how embarrassing)
I figured out the problem I was having. It was simply that if "list" has 15 rows, then both my list content and my output cells NEED to contain 15 items or else funky things happen.
HOWEVER
When I try to do a lengthy list, say of 500 rows, I run into circular reference issues... any thoughts?
Matt,
This one did not seem to work. in your excel example file I imputed: a, h,II,b,j,k,c,l,m,d,n,f,p in column A.
The result was
a,a,b,c,d,f,h,II,j,k,l,m,n
but there are not two a's in column a and it seemed to drop p completely... it seems some how shifted... can you help? Thanks.
The named range contains a blank cell, that is why you get strange results. Change the named range cell reference or replace the named range with a cell reference.
Hi,
I would like to output the unique ID of the sorted list. But when duplicates exist in the original list (in this case ID 1 and 2) it ranks both the BBs as 1.
The formula i have in the Sorted ID List column is =MATCH(SMALL(COUNTIF(List,"<"&List),ROW(1:1)),COUNTIF(List,"<"&List),0)
CURRENT OUTPUT:
ID Text Sorted
values ID list
1 BB 13
2 BB 1
3 VV 1
4 EE 10
5 TT 4
6 GG 7
7 FF 6
8 KK 11
9 II 9
10 CC 8
11 HH 12
12 LL 14
13 AA 5
14 NN 3
Desired Output:
ID Text Sorted
values ID list
1 BB 13
2 BB 1
3 VV 2
4 EE 10
5 TT 4
6 GG 7
7 FF 6
8 KK 11
9 II 9
10 CC 8
11 HH 12
12 LL 14
13 AA 5
14 NN 3
can you please suggest something?
Raj,
=MATCH(SMALL(COUNTIF(List, "<"&List)+ROW(List)/1048576, ROW(1:1)), COUNTIF(List, "<"&List)+ROW(List)/1048576, 0) + CTRL + SHIFT + ENTER. Copy cell and paste it down as far as needed.
adding a countblank statement ensures this working for ranges with blank cells (otherwise the first value will be repeated.
ROW(1:1)+COUNTBLANK(List)
Thanks Oscar, and Thanks Miel for solving my probleme! Cheers and Merry Xmas!
The array formula does not need the SMALL() function to work. You can use this instead : =INDEX(List,MATCH(ROW(List)-MIN(ROW(List)),COUNTIF(List,"<"&List),0)) + cse
Thanks for the COUNTIF() trick.
Jeanbar,
You are right!
Thanks for your contribution!
Oscar,
I found out that the formula you gave to Raj is not working. I think it is due to the "ROW(1:1)" part of it which means nothing. If you want to have a valid rank for the SMALL(Array;rank) you should declare ROW(INDIRECT("1:"&ROWS(List)) as a vector of ranks.
The formula working in my environment is: SORTED LIST =
T(INDEX(LIST,MATCH(SMALL(COUNTIF(LIST,"<"&LIST)+ROW(LIST)/MAX(ROW(LIST)),ROW(INDIRECT("1:"&ROWS(LIST)))),COUNTIF(LIST,"<"&LIST)+ROW(LIST)/MAX(ROW(LIST)),0))) +cse
NB: if one or more entries are blank, they come first in the sorted list.
Jeanbar,
The formula is working here. ROW(1:1) is part of small function which returns k-th smallest value in the dataset. When formula is copied down, Row(1:1) changes to Row(2:2) and then to Row(3:3).
Row(1:1) equals 1
Row(2:2) equals 2
and so on..
Row(1:1) is a relative cell reference.
Maybe you copied the formula into all cells and then presssed CTRL + SHIFT + ENTER?
How to use the formula:
Copy array formula into cell B2 and press Ctrl + Shift + Enter.
Copy CELL B2 and paste it to the cells below, as far as needed.
This is great:)formula worked for me.
But can you explain why we press CTRL + SHIFT + ENTER?
im not an expert.
Jaseel,
I am happy you got the formula working.
The formula is an array formula. To enter the formula as an array formula, type the formula in a cell and then press and hold CTRL + SHIFT and then press ENTER once.
Read more about array formulas: Array formulas
Hi Oscar,
Your formula is great, but I need something greater (I think)
I want to sort multiple columns using formula and even multiple sort types, is it possible?
For Example:
Unsorted Data
==============
Col 1 Col 2
-------------------
B 2
A 1
C 2
A 2
B 1
C 2
Sorted Data must be :
Col 1 Col 2
---------------------
A 1
A 2
B 1
B 2
C 2
C 2
Actually I need the sort type of Col 1 ASC then Col 2 DESC then Col 3 ASC then Col 4 DESC then Col 5 ASC.
But If you could give just the Col 1 ASC then Col 2 ASC formula, it would help me a lot...
Thx
FB,
I think this post answers your question: Sort values in parallel (array formula)
Dear Oscar,
Greatest Help...
Thank you very much
Thanks for formula however if the list has blanks it puts them up top and the data is in the bottom. Since I am using dynamic validation how can i put them up top so the user doesn't have to scroll down for selection?
rivers,
=INDEX(List, MATCH(SMALL(IF(ISBLANK(List), "", COUNTIF(List, "<"&List)), ROW(1:1)), IF(ISBLANK(List), "", COUNTIF(List, "<"&List)), 0))
Oscar, how would i change your formula if i want to work with COLUMNS rather than ROW... e.g. I have a single row of data in 5 columns, CDAEB and i want formulas in the next 5 columns that results in ABCDE.
I tried swapping the match reference and row(1:1) to column(1:1) but it doesnt work... many thanks.
Themin,
=INDEX($A$1:$A$5, MATCH(SMALL(COUNTIF($A$1:$A$5, "<"&$A$1:$A$5), COLUMN(A:A)), COUNTIF($A$1:$A$5, "<"&$A$1:$A$5), 0)) Get the example file *.xlsx Themin.xlsx
what is the purpose of [COUNTIF(List, "<"&List)]? I tried the same overall formula but I replaced this part with a direct reference to the named range and switched SMALL for LARGE which resulted in the formula below:
=INDEX(List, MATCH(LARGE(List, ROW(1:1)), List, 0))
This produced the same results as your original formula which is what made me wonder why the [COUNTIF(List, "<"&List)] portion needs to be there at all.
Also, in my attempt to understand what this part of the formula is doing I came across something that confused me even more. If the section in question [COUNTIF(List, "<"&List)] is put into cells by itself, without the [INDEX(List...], then the results are dependant on the row where the formula was originally inputted and where the formula was copied. I'm assuming this is because the countif outputs an array but I don't understand why. Can you explain this for me?
I should add that when I said the modified formula produced the same results as the original I meant that both formula's had the SMALL function swapped for the LARGE function.
Hi
I see you are solving a lot of complicated tasks in excel. Though I checked also the link https://www.get-digital-help.com/2010/01/12/sorts-values-in-parallel-array-formula/, and many others I can not find the solution for my problem.
I'll explain it a litlle better what i have and what i want to get:
column A ; column B
2 1
2 17
7 11
7 26
3 5
3 19
2 1
2 18
i would like to get first column sorted ascending and second column belonging number to the number of the first column:
column A ; column B
2 1
2 17
2 1
2 18
3 5
3 19
7 11
7 26
I hope you have time to look in to it.
Best regards, Jernej
Jernej,
Is this what you are looking for?
Jernej.xls
in the sort order column i used the array formula you gave to Raj=MATCH(SMALL(COUNTIF(List, "<"&List)+ROW(List)/1048576, ROW(1:1)), COUNTIF(List, "<"&List)+ROW(List)/1048576, 0), and the second column is the text that needs sorting in alphabetical order, in the third column i placed the formula that Chandoo used in his article =VLOOKUP(ROW()-ROW($K$1),$I$2:$J$12,2,FALSE. By doing this helped me to avoid problems with non unique values. I am wondering if u have a way for me to by bass the help column altogether?
Sort Order Info needing Sorting Sorted Info
1 WORK COMPLETE WORK COMPLETE
2 WORK COMPLETE WORK COMPLETE
3 WORK COMPLETE WORK COMPLETE
4 WORK COMPLETE WORK COMPLETE
5 WORK COMPLETE WORK COMPLETE
6 WORK COMPLETE WORK COMPLETE
7 WORK COMPLETE WORK COMPLETE
8 WORK COMPLETE WORK COMPLETE
9 WORK COMPLETE WORK COMPLETE
10 WORK COMPLETE WORK COMPLETE
11 WORK COMPLETE WORK COMPLETE
Hi!
I thank you all for your effort.
I got the right result with the "trick" Anonymous stated in his post. Thanks a lot for answering. This equation will reduce my time behind the screen significantly :).
Best regards, Jernej
Jernej,
Can you give an excel example of the final thing that gave you your desired results.
You wanted above "i would like to get first column sorted ascending and second column belonging number to the number of the first column:
column A ; column B
2 1
2 17
2 1
2 18
3 5
3 19
7 11
7 26"
I need to do the same thing. Sort the first column and then after the sorting is done, put the associated 2nd column next to the first, as you mentioned. I have been successful in sorting the first column.
please help
thanks
could you give me formula for sorting the text with spacing row, and I need next column sorting by first column :
col1 col2
anne 2
marie 3
spacing (text)
jolie 2
linda 5
anne 5
i need name of "anne" converge consecutive in next column like this :
anne 2
anne 5
jolie 2 and so on
please help
thanks
pls can u help how to edit this formula
hi sir pls help how edit this shorting formula. when me was pasting this formula in differnt sheet it show #NA. how i need to apply in my sheet pls expl
pardhu,
Create a named range (List) using your cell reference.
Then apply my formula.
Hi,
I have this data:
12-04-12 1
19-04-12 3
23-04-12 2
01-05-12 1
07-05-12 1
15-05-12 1
05-06-12 1
27-08-12 1
How to get the two column output sorted on the second column using array formula?
Best Regards
Kamal,
See this section: Two columns sorting by the second column
Hello,
I love the formula!
I'm having issues with some of my data being numbers stored as text.
This is the data I have:
AK2
CB4D
23
207
H1
Returned sort:
23
23
AK2
CB4D
H1
Any idea how to solve that?
Cheers,
Ryan,
See attached *.xlsx file
ryan.xlsx
Sort 1 Sort 2
1350 1350
1351 1351
1352 1352
1353 1353
1354 1354
1355 1355
9999 1357
1357 1357
1358 1358
1359 1359
1360 1360
1361 1361
1362 1362
1363 1363
1364 1364
9999 9999
9999 9999
9999 9999
9999 9999
9999 9999
9999 9999
9999 9999
I am trying to sort the first column into the second. Wherever the number is 9999 it duplicates what would be the next number. It should be shifting up and moving the 9999 to the end. I am working with data sets of about 200 numbers.
Thanks!
Mark,
Copy cell D1. Paste to cell range D2:D22.
hi, i have a text file and wanted to sort the text file info into different location of row and column in excel.
may i know how can i do that?
inside the text file example:-
header
a1
123
456
a2
123
456
a3
123
456
789
i want to sort the data and display it using the a1,a2,a3 with the content inside
expected output in excel sheet as follow:-
cella b c
a1 123 456
a2 123 456
thank you inadvance for your help
A1, A2, A3, and so on are the row delimiters?
Using all your values:
a b c d
a1 123 456
a2 123 456
a3 123 456 789
Correct?
I have a data in cell A1, A2 & A3. How to rearrange data in ascending order in same cell. Please help how to solve without VBA or Macros.
DBCA
IJNM
ALAM
Output in should be like this.
ABCD
IJMN
AALM
Thanks in advance
ROHIT,
I have no clue but I am sure it is possible with vba.
Hi,
I know its possible, but I can't manage to do it :) I have in col A 200 names, but between them, there are empty cells, and they need to stay. Is it possible in the sort (col B) to sort from A-Z without the empty cells.
With the formula like it is now, he will put the empty cells above A.
=INDEX($A$2:$A$200; MATCH(SMALL(IF(ISBLANK($A$2:$A$200); ""; COUNTIF($A$2:$A$200; "<"&$A$2:$A$200)); ROW(90:90)); IF(ISBLANK($A$2:$A$200); ""; COUNTIF($A$2:$A$200; "<"&$A$2:$A$200)); 0))
The formula should ignore the empty cells, or delte them in the array. Thanks for helping out :)
Your formula ignores empty cells, I changed ROW(90:90) to ROW(1:1).
=INDEX($A$2:$A$200; MATCH(SMALL(IF(ISBLANK($A$2:$A$200); ""; COUNTIF($A$2:$A$200; "<"&$A$2:$A$200)); ROW(1:1)); IF(ISBLANK($A$2:$A$200); ""; COUNTIF($A$2:$A$200; "<"&$A$2:$A$200)); 0)) Did you create the array formula in one cell and then copy/paste the cell (not the formula) down as far as needed?
Hello,
I have a table of example data: Column A contains Names, Columns B and C both contain Weight and Height data respectively. How can I sort Columns B and C to give an accurate combination of both Weight and Height from Largest -> Smallest.
Many thanks!
Jo
Joseph,
Here is a post about sorting values in parallel:
Sort values in parallel (array formula)
Get the Excel example file *.xlsx
Sort-values-in-parallel-Joseph.xlsx
Many thanks Oscar!
Jo
My solution is much simpler.
1. My unsorted numbers ( or words ) are listed horizontally. e.g. B29 – G29 ( 6 numbers ). I choose 29 so that it wont be confused with the 1 used in RANK function :D
2. My sorted numbers shall be in cells J29-O29,
3. The formula for cell J29 is
=IF(RANK($B29,$B29:$G29,1)=1,$B29,IF(RANK($C29,$B29:$G29,1)=1,$C29,IF(RANK($D29,$B29:$G29,1)=1,$D29,IF(RANK($E29,$B29:$G29,1)=1,$E29,IF(RANK($F29,$B29:$G29,1)=1,$F29,IF(RANK($G29,$B29:$G29,1)=1,$G29,$Q29))))))
3. The formula for cell K29 is … just convert all the “=1″ into “=2″
4. The formula for the rest is “=3″ for L29 and so on till “=6″ for O29.
5. The RANK function will rank every cell in the range. There will not be any unranked. The last part .. ,IF(RANK($G29,$B29:$G29,1)=1,$G29,$Q29)
If there are more than one same number .. meaning there are more than one number of the same rank.. it would duplicated the first number of the same rank.
Hope this would help u guys.
Note: In order to make it work for words, you need to convert Words into ASCII by using the function CODE.
Just some futher explanations ..
1. the formula in the first sorted cell J29 will seek which number is ranked #1 and the second cell, it would seek the number ranks #2 and so on.
cJ29- "=IF(RANK($B29,$B29:$G29,1)=1,$B29,IF(RANK($C29,$B29:$G29,1)=1.."
cK29- "=IF(RANK($B29,$B29:$G29,1)=2,$B29,IF(RANK($C29,$B29:$G29,1)=2.."
The IF statement checks every one of the ranks in the unsorted cells B29 - G29 .
i used this formula successfully. but i can't understand why (--(sum(a2=$a$2:a2)) has been used in this formula. and what the logic of (--(sum(a2=$a$2:a2)). I used row function in place of (--(sum(a2=$a$2:a2)), the result was not proper when the values were same.
Dear sir,
I have another question for the foumula copied below,
Array Formula in cell E2:
=INDEX($B$2:$B$9, MATCH(SMALL(COUNTIF($B$2:$B$9, "<"&$B$2:$B$9), ROW(1:1)), COUNTIF($B$2:$B$9, "<"&$B$2:$B$9), 0))
Array Formula in cell D2:
=INDEX($A$2:$A$9, SMALL(IF(E2=$B$2:$B$9, MATCH(ROW($B$2:$B$9), ROW($B$2:$B$9))), SUM(--(E2=$E$2:E2))))
sir. The sorting of first list can be done only small function such as {=SMALL($b$2:$b$9,ROW(1:9))}, why the rest of the formula is used.
once again, thanks a lot, sir.
The sorting of first list can be done only small function such as {=SMALL($b$2:$b$9,ROW(1:9))}, why the rest of the formula is used?
Your formula works only for numbers, my formula works for text also.
Hi - I tried pasting your formula for my purposes, but it returned every row with the same thing - the first item in the list... any idea why?
sc,
Paste the formula in one cell and enter it as an array formula. Copy the cell (not the array formula) and paste to cells below.
When you copy the cell the relative cell reference changes, that will not happen i you copy the array formula.
Im using the formula and I received a 0.
=INDEX(F2:F1000,MATCH(ROW(F2:F1000)-MIN(ROW(F2:F1000)),COUNTIF(F2:F1000,"<"&F2:F1000),0))
Instead of using "list", I put the range manually.
Now I received only the first word, but then I just got #NUM!
Well I can do it, but there's a problem, i want it alphanumeric order, could be possible? for example, if you hace the same words but at the end different number. Example
VIC_TXT[1]
VIC_TXT[2]
....
Regards,
Daniel,
Array formula in cell C3:
Expand the array to A1:B9 and use the Index ability to use different columns. Then you can sort how many column you need. (sorry for bad english) :)
Hej Pelle Bergkvist
Yes, but you can only use a single column at a time, right?
I think I described the technique here:
https://www.get-digital-help.com/2013/03/15/index-function-explained/#ex5
How do you sort an array generated by formulas though? Is that possible?
How do you sort an array generated by formulas though? Is that possible? With an array formula that is.
All I get are zeros
Bryant,
How do you sort an array generated by formulas though?
Can you share your array formula?
Hi Oscar
This is my problem
Dates Values Dates Values
12/04/2012 1 1 12/04/2012 1 1
19/04/2012 2 3 01/05/2012 4 1
23/04/2012 3 2 15/05/2012 6 1
01/05/2012 4 1 05/06/2012 7 1
07/05/2012 5 3 23/04/2012 3 2
15/05/2012 6 1 19/04/2012 2 3
05/06/2012 7 1 07/05/2012 5 3
27/08/2012 8 3 27/08/2012 8 3
1
12/04/2012 1
01/05/2012 4
15/05/2012 6
05/06/2012 7
2
23/04/2012 3
Like sort it like this
3 with numbers changing
19/04/2012 2
07/05/2012 5
27/08/2012 8
I tried using the formula for sorting two columns using the second column, with the second column having the numbers for the ranking and the first column having the text that should be ranked along with it.
The formula worked fine for small lists, but started producing only a "1" in every cell when used with a large array (>2,000 entries).
I have 64 bit Excel and am surprised the program cannot handle the formula. In fact, using the SORT feature from the pull-down menus works just fine, so it doesn't seem to me that the program should be unable to do this. Rather, it is simply my inability to make your formula work. So do you have any suggestions?
The problem you are facing is in the "ROW" function within the formula. Since it is an array formula, it is only recognizing the first smallest number across board. Try replacing the "ROW(1:1)" with "ROW(List)-Row(1)" this is assuming your data "List" named range starts from row 2. Hope this helps.
Just wanted to take a moment to thank you for this guide, it condesnsed what would have taken a day into 10 minutes of work. Very easy to follow for someone new to Excell, thanks again!
Thanks for the post. I would apply the formulas in excel for sorting. This helps me for customizing sorting.
Hi,
How can I make it work without dragging the formula down from B1 cell? This is what I have on B1 cell. It works but it only shows the first item. Thanks
=ArrayFormula(INDEX(List, MATCH(SMALL(COUNTIF(List, "<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0)))
I have a 3 column list that was extrapolated from several worksheets within my workbook. There are 3 columns and 30 rows, Col A-Gender (f, m. my, fy), Col B-Name, Col C-Score. I have hidden formulas for sorting highest to lowest scores as a group, now I need this list sorted by gender from highest to lowest score. In summary, I need 1st and 2nd place for each of the 4 gender groups. I hope this is clear, can you help?
I want to remove double value by fomular, so how i have to do ?
Thanks a lot for the idea. Did not work for me as it is, mainly because row(1:1) did not change in the array formula (so it was the first element of the sorted set in all cells). Have to add a column with list numbers (1,2,3, etc) and refer to this column instead of row(1:1) in the formula. Everything else was ok, thanks!
enhancing the formula of the original excellent post, the following sorts a single-column list by Asc order
(for Desc order change the '' below )
-range 'List' covers the original list to be sorted
-the formula is entered as a single array formula over the range where the output is needed.
=INDEX(List, MATCH(SMALL(COUNTIF(List, "<="&List),
ROW(List) - ROW(OFFSET(List,0,0,1,1))+1),
COUNTIF(List, "<="&List),
0)
)
in the previous post some character where stripped out. trying again:
"(for Desc order change the '' below )"
should read
"(for Desc order change the below )"
and again:
in the previous post some character where stripped out. trying again:
"(for Desc order change the '' below )"
should read
"(for Desc order change the \ below )"
and again:
in the previous post some character where stripped out. trying again:
"(for Desc order change the '' below )"
should read
"(for Desc order change the LessThan symbol to a GreaterThan symbol )"
how do i add your formula =INDEX($B$2:$B$9, MATCH(SMALL(COUNTIF($B$2:$B$9, "<"&$B$2:$B$9), ROW(1:1)), COUNTIF($B$2:$B$9, "<"&$B$2:$B$9), 0)) to my formula as below
=IFERROR(INDEX(table1,SMALL(IF(COUNTIF(B587,table2)*COUNTIF(C587,table3)*COUNTIF($AD$2,table4), ROW(table3)-ROW($B$2)+1),COLUMN($B$1))),"")
i add another table5 to count ascending