Sort a column alphabetically
Table of Contents
1. Sort a column - Excel 365
Excel 365 formula in cell D3:
The SORT function sorts values from a cell range or array
Function syntax: SORT(array,[sort_index],[sort_order],[by_col])
2. Sort a column using array formula
I am inspired once again by the article Sorting Text in Excel using Formulas at Pointy haired Dilbert. In Chandoo´s article, he sorts text with a "helper" column. My goal with this article is to show you how to sort text cells alphabetically without a helper column.
Here is an example, cell range $A$2:$A$15 contains text values.
Here is how to automatically sort text cells without any user interaction in cell B2:
Array formula in cell B2:
This article demonstrates how to filter a unique distinct list sorted from A to Z:
Recommended articles
The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]
The following post shows you how to sort and filter duplicate values alphabetically:
Recommended articles
The following array formula extracts duplicate values sorted from A to Z from cell range B3:B21. Excel array formula in […]
Learn how to sort a multi-column cell range alphabetically and display the values in a single column:
Recommended articles
Question: How do I sort a range alphabetically using excel array formula? Answer: Cell range $B$2:$E$5 contains text values in random […]
Sort a data set using an array formula:
Recommended articles
I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]
Tip! You can sort a data set in any way you like it, if you convert it to an excel defined table:Â Excel table - Sort data
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 B2 and paste it down as far as needed.
Recommended article
Recommended articles
Array formulas allows you to do advanced calculations not possible with regular formulas.
Explaining array formula in cell B2
=INDEX($A$2:$A$15, MATCH(SMALL(COUNTIF($A$2:$A$15, "<"&$A$2:$A$15), ROW(1:1)), COUNTIF($A$2:$A$15, "<"&$A$2:$A$15), 0))
Step 1 - Count "smaller" values
COUNTIF(range,criteria) counts the number of cells within a range that meet the given condition
=INDEX($A$2:$A$15, MATCH(SMALL(COUNTIF($A$2:$A$15, "<"&$A$2:$A$15), ROW(1:1)), COUNTIF($A$2:$A$15, "<"&$A$2:$A$15), 0))
COUNTIF($A$2:$A$15, "<"&$A$2:$A$15)
becomes
COUNTIF({MM,BB,VV,EE,TT,GG,FF,KK,KK,II,CC,HH,LL,AA,NN}, "<"&MM,"<"&BB,"<"&VV,"<"&EE,"<"&TT,"<"&GG,"<"&FF,"<"&KK,"<"&II,"<"&CC,"<"&HH,"<"&LL,"<"&AA,"<"&NN)
The first value in this array formula is "MM". Let´s see what happens when COUNTIF calculates how many values is small than "MM".
COUNTIF({MM,BB,VV,EE,TT,GG,FF,KK,II,CC,HH,LL,AA,NN}, "<"&"MM"
becomes
COUNTIF({MM"<"&"MM",BB"<"&"MM",VV"<"&"MM",EE"<"&"MM",TT"<"&"MM",GG"<"&"MM",FF"<"&"MM",KK"<"&"MM",II"<"&"MM",CC"<"&"MM",HH"<"&"MM",LL"<"&"MM",AA"<"&"MM",NN"<"&"MM"}
MM<MM is FALSE and BB<MM is TRUE and so on.. The array becomes:
COUNTIF({FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, FALSE}
becomes
COUNTIF({0, 1, 0, 1,0, 1 , 1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,1 ,0} and the total is 10 which is the first number in the returning array. 0+1+0+1+0+1+1+1+1+1+1+1+1+1+1+1+0 = 10
Next is COUNTIF({MM,BB,VV,EE,TT,GG,FF,KK,II,CC,HH,LL,AA,NN}, "<"&"BB")
becomes
COUNTIF({MM"<"&"BB",BB"<"&"BB",VV"<"&"BB",EE"<"&"BB",TT"<"&"BB",GG"<"&"BB",FF"<"&"BB",KK"<"&"BB",II"<"&"BB",CC"<"&"BB",HH"<"&"BB",LL"<"&"BB",AA"<"&"BB",NN"<"&"BB"})
becomes
COUNTIF({FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE,FALSE , TRUE, FALSE})
becomes
COUNTIF({0, 0, 0, 0,0, 0, 0,0,0,0,0,0,0,0,0,1,0}) and the total is 1 which is the second number in the returning array.
and so on...
COUNTIF($A$2:$A$15, "<"&$A$2:$A$15) returns this array:
{10, 1,13,3,12,5,4,8,7,2,6,9,0,11}
Recommended article
Recommended articles
Counts the number of cells that meet a specific condition.
Step 2 - Return the k-th smallest number in array
SMALL(array,k) returns the k-th smallest row number in this data set.
SMALL({10, 1,13,3,12,5,4,8,7,2,6,9,0,11}, ROW(1:1)) returns the smallest number in this data set, 0 (zero).
Recommended article
Recommended articles
The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.
Step 3 - Find position in array
MATCH(lookup_value;lookup_array; [match_type])Â returns the relative position of an item in an array that matches a specified value
MATCH(SMALL(COUNTIF($A$2:$A$15, "<"&$A$2:$A$15), ROW(1:1)), COUNTIF($A$2:$A$15, "<"&$A$2:$A$15), 0)
becomes
MATCH(0, {10, 1,13,3,12,5,4,8,7,2,6,9,0,11}, 0) and returns 13.
Step 4 - Return value
INDEX(array,row_num,[column_num])Â returns a value or reference of the cell at the intersection of a particular row and column, in a given range.
=INDEX($A$2:$A$15, MATCH(SMALL(COUNTIF($A$2:$A$15, "<"&$A$2:$A$15), ROW(1:1)), COUNTIF($A$2:$A$15, "<"&$A$2:$A$15), 0))
becomes
=INDEX($A$2:$A$15, 13) and returns AA in cell B2.
Get Excel sample file for this tutorial
Sorting-text-cells-using-array-formulas.xls
(Excel 97-2003 Workbook *.xls)
3. Sort two columns by the second column - Excel 365
4. Sort two columns by the second column
Array Formula in cell E2:
Array Formula in cell D2:
Get excel *.xlsx
Two columns sorting by the second column.xlsx
Sort values category
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 […]
Excel categories
80 Responses to “Sort a column alphabetically”
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.
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