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)
Last Comment
Peter Harris
Try UNIQUE instead of UNIQUES
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)
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.
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")))
=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.
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.
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.
If first formula is working, use =TRANSPOSE(SORT(UNIQUE(FILTERXML("<y><z>"&SUBSTITUTE(TEXTJOIN(",",,INDIRECT("C" & M12 & ":G" & N12))
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.
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"))))
ASKER CERTIFIED SOLUTION
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.
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.
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.
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
