Sorting based on qty - through formulea 1

[iken]

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,

 

[IRstuff]

?? That's all done in the sort dialog; you sort on frequency first, then the ordinality.

TTFN
faq731-376

 

[iken]

Thanks IR, I am aware of the sort dialog, but want to do this via a string of formulae

 

[AELLC]

I did something like this quite a while ago with help from this forum...I recall it involved the RANK function.

Try doing a search using keywords "Sort by formula" or "Sort by function"

 

[magoo2]

You can use the mode function to get the one with the highest frequency and then delete or add a code to remove them and repeat again and again.

 

[IRstuff]

But, I don't think there is a formulaic way for Excel to move data around. That can only be done by VBA; pivot tables are a possibility, but they're mostly run by dialogs, just like Sort.

TTFN
faq731-376

 

[AELLC]

iken,

Is this what you need?

 

[AELLC]

iken,

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.

 

[IDS]

I know you asked for no VBA, but doing it without VBA will be quite complicated (I think), whereas if you use the sort UDF from here:

http://newtonexcelbach.wordpress.com/2009/03/23/a-sort-function/

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/

 

[iken]

AELLC, I tried your sheets, but when I changed the freq, all cells went #name

I have used the sort function with a macro. Not ideal but will do.

Thanks all

 

[AELLC]

OK, I was only looking at a series of numbers that start at 1 and increment by 1 as stated in your OP

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.

 

[IDS]

iken - What version of Excel are you using? If prior to 2010 replace the Rank.eq function with Rank and it will give the results as shown. But I don't think this is the result you want. With the numbers shown it gives the correct results, but that's coincidental. If you play with the frequencies you will find that in general it doesn't give the right order.

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/

 

[iken]

Thanks IDS,

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

 

[AELLC]

Here is something w/o VBA that appears to work -

 

[AELLC]

The above was Excel 2010...here is the version in 97-2003

 

[iv63]

AELLC:
Big star for a very elegant solution. Is there a way to do this in descending order?
iv

 

[AELLC]

iv,

Yes

Cell E4=RANK(D4,$D$4:$D$30,1)+COUNTIF(D$44,D4)-1

and copy down the column

 

[IDS]

iv-63 - see the attached file for two ways of doing a reverse sort. The first one needs an extra column of numbers, but is a simpler formula, and the second doesn't need any extra columns.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/