asked on # 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

Try UNIQUE instead of UNIQUES

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)

I've also removed formulas and used direct dell reference with the same errors. Formula below:

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

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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!

James Murphy

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

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:

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.

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.

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.

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
Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

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

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.

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"))))
```

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question
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.

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

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.

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.