Avatar of Pedro
Pedro
 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)

Open in new window

Microsoft OfficeMicrosoft Excel

Avatar of undefined
Last Comment
Pedro

8/22/2022 - Mon
Peter Harris

Try UNIQUE instead of UNIQUES
Pedro

ASKER
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.

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

Open in new window

Multiple column formula:
=SORT(UNIQUE(INDIRECT("C" & M12 & ":G" & N12)),,,TRUE)

Open in new window


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

ASKER
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.

Open in new window

Unique-DistinctinArrayC2-G46-Sorted.xlsb 
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
byundt

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

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

Open in new window

ASKER CERTIFIED SOLUTION
byundt

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
Sign up - Free for 7 days
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
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.
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
Pedro

ASKER
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.