# Excel formula to return unique values sorted lowest to highest in a range of cells

A range of cells to be examined to start from column C in the row specified by X12 and end in column G in the row specified by Y12. Using the INDIRECT function below returns error "#NAME?" instead of an expected number value. Checked via Formula - Insert Function and all the arguments seem correct.

Using Office 365.  Not sure if I need to update the formula.

``````=SORT(Uniques(INDIRECT("C" & M12 & ":G" & N12)),,,TRUE)
``````
Microsoft OfficeMicrosoft Excel

Last Comment
Pedro
Peter Harris

Try UNIQUE instead of UNIQUES
Pedro

using UNIQUE instead of UNIQUES returns "#REF!" error.
I've also removed formulas and used direct dell reference with the same errors. Formula below:

=SORT(UNIQUE(INDIRECT(C2:G47)),,,TRUE)
Peter Harris

Indirect takes text so put inverted commas around the reference. But that doesn’t fully explain your first issue.

Peter Harris

I think your sort function is the issue. You may have too many arguments. Try sort with just one argument.
byundt

Are you trying to produce a single column of results, or multiple columns?

The following formulas are using M12 and N12 to define the extent rather than X12 and Y12.

Single column formula:
``````=SORT(UNIQUE(FILTERXML("<y><z>"&SUBSTITUTE(TEXTJOIN(",",,INDIRECT("C" & M12 & ":G" & N12)),",","</z><z>")&"</z></y>","//z")))
``````
Multiple column formula:
``````=SORT(UNIQUE(INDIRECT("C" & M12 & ":G" & N12)),,,TRUE)
``````

TEXTJOIN and FILTERXML are used in the single column formula to mash all the text into a single string, then split that string into separate values. UNIQUE and SORT then work on the array returned by FILTERXML as you might expect.
Pedro

Byund,

It seems both formulas seem to work but need some tweaking.

The first formula seems to find unique numbers and sort them but I'd like the numbers going across instead of down.

``````The second formula seems to pull numbers but not sort them even though SORT is used. I've attached an excel file with the results. Hopefully, this clarifies the required result.
``````
Unique-DistinctinArrayC2-G46-Sorted.xlsb
byundt

If first formula is working, use =TRANSPOSE(SORT(UNIQUE(FILTERXML("<y><z>"&SUBSTITUTE(TEXTJOIN(",",,INDIRECT("C" & M12 & ":G" & N12))
Pedro

Byundt,

The formula works when using M12 & N12. However, when I change the cell reference to X12,Y12 or AB12,AC12 I get a "#CALC!" error. I'd like to know if this formula cannot be used with cell references after a certain point in Excel. This way I know I need to keep the cell references to within certain parameters. I've put the exact formula I used below.

``````=TRANSPOSE(SORT(UNIQUE(FILTERXML("<y><z>"&SUBSTITUTE(TEXTJOIN(",",,INDIRECT("C" & M12 & ":G" & N12)),",","</z><z>")&"</z></y>","//z"))))
``````
byundt

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Rob Henson

Rather than formula, create a Pivot Table with just the relevant column as row values. That will create a list of unique values and can be sorted ascending or descending.
Pedro

Rob,

A pivot table may not work for this scenario. I prefer a formula in the cell that can be changed as needed. I'm guessing I'm just not that familiar with pivot tables.
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY