Sorting based on qty - through formulea
Sorting based on qty - through formulea
(OP)
Hi,
Just wondering if anyone knew of a way to sort a bunch of numbers, based on a formulea (macro is easy, but would like a formulea.
What I am trying to do is sort some numbers based on a frequency they show up.
i.e
Numbers 1 2 3 4 5 etc
Freq 4 5 1 1 0 etc
I also need to take into account differeing numbers that have the same freq as others.
So my result would be (from most freq to least)
Result 2 1 3 4 5
Thanks,
Just wondering if anyone knew of a way to sort a bunch of numbers, based on a formulea (macro is easy, but would like a formulea.
What I am trying to do is sort some numbers based on a frequency they show up.
i.e
Numbers 1 2 3 4 5 etc
Freq 4 5 1 1 0 etc
I also need to take into account differeing numbers that have the same freq as others.
So my result would be (from most freq to least)
Result 2 1 3 4 5
Thanks,





RE: Sorting based on qty - through formulea
TTFN

FAQ731-376: Eng-Tips.com Forum Policies
RE: Sorting based on qty - through formulea
RE: Sorting based on qty - through formulea
Try doing a search using keywords "Sort by formula" or "Sort by function"
RE: Sorting based on qty - through formulea
RE: Sorting based on qty - through formulea
TTFN

FAQ731-376: Eng-Tips.com Forum Policies
RE: Sorting based on qty - through formulea
Is this what you need?
RE: Sorting based on qty - through formulea
Sorry, didn't check that solution enough. It looks like you would need n-1 of intermediate steps, so this may not be practical when n is very large. See attached file.
RE: Sorting based on qty - through formulea
http
you can just sort the data on the frequency column, which is much easier (but the data will need to be in columns, rather than rows).
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Sorting based on qty - through formulea
I have used the sort function with a macro. Not ideal but will do.
Thanks all
RE: Sorting based on qty - through formulea
i.e. 1 2 3 4 5 6 etc
For anything else such as 5 8 3 1 12 26 4 etc
there would have to be another step using the OFFSET function to return the actual number in the sorted column of results.
RE: Sorting based on qty - through formulea
You could use the Match function to find the position of each rank, then use the index (or offset) function to return the actual value for that rank, if they were not in sequential order. This still has a problem if there are two of equal rank though, as the second match on that rank will return #NA. At the moment I can't see a simple way around that.
The sort function I linked to previously seems like the simplest solution to me. Are you wanting to avoid VBA altogether, or just trying to avoid a macro that will need to be initiated to give the correct results?
A spreadsheet is attached with examples of the Rank/Match method and the UDF sort function.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Sorting based on qty - through formulea
I were trying to avaid VBA and macro's solely for ease (and the fact I and otehrs would need to enable the macro at the start.
I have given into this though, and have a macro that sorts the data with a click on an "update data" button.
Thanks All
RE: Sorting based on qty - through formulea
RE: Sorting based on qty - through formulea
RE: Sorting based on qty - through formulea
Big star for a very elegant solution. Is there a way to do this in descending order?
iv
RE: Sorting based on qty - through formulea
Yes
Cell E4=RANK(D4,$D$4:$D$30,1)+COUNTIF(D$4:D4,D4)-1
and copy down the column
RE: Sorting based on qty - through formulea
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/