Sorting Data

[JLRO]

Hello,

This is a question related to thread:
http://www.excelforum.com/showthread.php?t=629092

Once the data is shown on a results page:
1. How do I sort the results both accending and decending?
2. Create a new results list for the Top and Bottom 10-12 ranking based on a column in the original results?
3. Is there something that can be added to the array, so after the last result it does not show #REF
4. If I am using a drop down list. How do I show all the items versus one selected item from the list?

Thanks,
Joe

[corinereyes]

Hello Joe,

Here is one trick to sort the array formula (values), using the Small function in other cells. Try the attached example.

Sheet2 A1 is a validation list (drop down) shows items retrieved from Sheet 1, displaying items based from a certain criteria.

[JLRO]

Hello Corrne,

Thank you for the help.

I tried to modify the formula to ignore errors in a group of cells, such as, "#VALUE!", but was not successful.

The example is.

sheet#1 has data in cells A1:A999, but it also may contain errors, such as "#VALUE!". In sheet#2, I want to look in cells $A$1:$A$999 and rank it starting from the smallest number, while ignoring any errors.

If possible, I would like to be take it one step further and create my own ranking number list (still using SMALL and LARGE funtion) as the look up reference.

Sheet#1
3
#Value!
1
2
#Value!
10
6

Sheet#2 (using SMALL function)
Rank Result Rank Result
1 1 5 10
2 2 1 1
3 3 4 6
4 6
5 10


Hopefully this makes sense.
Do you have any ideas for a solution?

Again, thank you!
Joe

[corinereyes]

Hi Joe,

Try the attached sheet. Again by using array.

[corinereyes]

Opps...

I made some practiced in the sheet above, here is the clean file.

Note: The range with #VALUE! error is in sheet 1 the rank result is in sheet 2.

correction: Sheet B1 is not ranking, its in ascending order

[JLRO]

Hi Corine,

Thanks for the help. I attempted to use the filter, but depending on the error, the formula did not work.

I've attached your spreadsheet with some additional errors which I am not sure how to handle. Also, is there way to combine trapping/excluding the errors and just showing the result?

Thanks for you attention.
Joe

[corinereyes]

Hello,

Sorry it took sometime, i had my vacation. Try the attached sheet.

formula:

Please Login or Register  to view this content.

Formula must be confirmed with CTRL+SHIFT+ENTER. {}

[JLRO]

Hi Corine,

Thanks for the help.

I'm going to use your example to hopefully solve my next challenge!

Have a great weekend!
Joe