×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Sorting based on qty - through formulea

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,

 

RE: Sorting based on qty - through formulea

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

RE: Sorting based on qty - through formulea

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"

RE: Sorting based on qty - through formulea

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.

RE: Sorting based on qty - through formulea

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: Eng-Tips.com Forum Policies

RE: Sorting based on qty - through formulea

(OP)
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

RE: Sorting based on qty - through formulea

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.

RE: Sorting based on qty - through formulea

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/
 

RE: Sorting based on qty - through formulea

(OP)
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

RE: Sorting based on qty - through formulea

AELLC:
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

iv,

Yes

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

and copy down the column

 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources