# Excel Function to Perform Automatic Sort



## laxsox (Oct 26, 2006)

Is there a function or series of functions that would eanble me to automatically sort a table as data is changed. I have a table that includes three columns - Priority, Months, Rate. These values are all changed based on various other calculations happening throughout the workbook. Essentially, the data then looks like this:

6 13 6.2%
1 20 6.1%
5 34 4.5%
7 13 12.3%
2 21 5.5%
4 27 3.7%
3 13 6.2%

The table needs to be sorted based on the Months. However, notice the duplicates. What I need to have happen is that if there are duplicates, then it needs to sort those duplicates based on the Rate. If there are duplicates again, then I need it to finally sort by Priority. My brain is fried and I could really use someone's guidance to accomplish this. I would prefer not to use any Macros since this Workbook is going to be distributed amoung a few different folks and I cannot guarantee poeple will allow it to run. My goal is to then pull the "sorted" order and use it elsewhere in my book. Thanks in advance.


----------



## RSpecianjr (Jan 20, 2010)

Hey laxsox,

I don't really have the time to write the formula for you, but you can use a rank formula nestled within an array formula.

The formula should basically do....

1. Combine all data, (months + (rate *.001) + (priority * .00001))
2. Use a Rank formula on the new list of data.
3. Use some sort of lookup to pull in the associated data based on the ranking list.

You can skip the array formula, if you add a separate column for part 1 and part 2. In this case, I would use Index/Match.

Regards,

Robert D. Specian Jr.


----------



## laxsox (Oct 26, 2006)

I think this will work perfectly. Thanks!


----------



## RSpecianjr (Jan 20, 2010)

Just let us know if it doesn't work and we can open the thread back up.

Regards,

Robert D. Specian Jr.


----------



## laxsox (Oct 26, 2006)

*Re: [SOLVED] Excel Function to Perform Automatic Sort*

Continuing on this conversation... I have the formulas working correctly and I have setup a VLOOKUP for another series of cells that automatically pulls the the 'Months' (based on their rank) and places them in ascending order.

However, I want to make one edit to this series of calculations... If I have three duplicates, it will show the same month 3 times followed by the other monthly calculations. What I would like to have happen is for the cell to understand that it may have a duplicate number and if it does, only show one duplicate and then show the remaining numbers. Is this possible using the table I've already created, or can I make it work with a few amendments?


----------



## RSpecianjr (Jan 20, 2010)

*Re: [SOLVED] Excel Function to Perform Automatic Sort*

Hey laxsox,

So, you want it to say something like:

May - First data for may - second data for may
- Next data for may - More data for may
- Next data for may - More data for may


Right?

Regards,

Robert D. Specian Jr.


----------



## laxsox (Oct 26, 2006)

Not quite. Let's say I havethe following column headings:

Calculated Rank | Rank | Priority | Months | Rate

Let's just say that the table produces the following values:

6 | 59.0001624 | 6 | 59.00 | 6.2%
7 | 61.0001599 | 3 | 61.00 | 6.1%
1 | 13.0001925 | 1 | 13.00 | 4.5%
2 | 13.0001725 | 2 | 13.00 | 12.3%
5 | 52.0001524 | 5 | 52.00 | 8.5%
4 | 13.0001499 | 4 | 28.00 | 4.25%
3 | 13.0000711 | 7 | 13.00 | 0.00%

Again, these numbers aren't real, I'm just trying to show an example.

What I do next is I use a VLOOKUP based on the 'Calculated Rank' (1-7) to take the appropriate the 'Months' and place them ascending order at the top of another table. So the new table would have the following column headings: 13 | 13 | 13 | 28 | 52 | 59 | 61

What I want to have happen is to tell the column headings that if it is a duplicate of another heading (or 'Months') in the table (namely the number 13) don't repeat it and only show it once. So the end result would be a table that would have the following column headings: 13 | 28 | 52 | 59 | 61 Obviously, as the initial values change, the column headings would change to, but always looking to only show one duplicate entry, if needed. 

I fell like I would use a combination of IF, VLOOKUP, and ROWS, but nothing I do will work. What do you think?


----------



## RSpecianjr (Jan 20, 2010)

Hey,

It's going to get complicated, but it can be done.

What happens if the Months are separated by ranks... so if 13 is in rank 1 and 3 and 28 is in rank 2? Do you still only want to show one 13 and in what position, 1st or 3rd?

Regards,

Robert D. Specian Jr.


----------



## laxsox (Oct 26, 2006)

The table is ranked based on months. In this case, isn't the ranked order the same then as the sorted order of months?


----------



## laxsox (Oct 26, 2006)

Theoretically, that shouldn't happen (13 in spot 1 and 3, and 28 in spot 2) because the rank is sorted based on 'Months', so it should always show duplicates one right after another.


----------



## RSpecianjr (Jan 20, 2010)

hehe, silly me, wasn't thinking.  I'm pretty busy this weekend, let you know when I come up with something.


----------



## RSpecianjr (Jan 20, 2010)

Hey laxsox,

What you will really want is to rankif. You will have to use an array fomula or a sumproduct formula to do it.

If you are only concerned with the first instance of a Month, then just rank based on month alone. When you do the lookup it will only catch the first instance.

Can you post your workbook so we can have something to play around with?

Regards,

Robert D. Specian Jr.


----------



## laxsox (Oct 26, 2006)

Hi, RSpecianjr--

Sorry for the late response - I was away on business. I've attached a sample spreadsheet of what I'm trying to do. I've been tinkering with a number of formulas all across the spreadsheet, but it should be pretty apparent what I'm trying to do. What do you think? Thanks again!


----------



## RSpecianjr (Jan 20, 2010)

Hey laxsox,

I've added the information needed to get the rankif. Play around with it and see if it is what you want or if you need something else.

Regards,

Robert D. Specian Jr.


----------



## laxsox (Oct 26, 2006)

Thanks. I'll take a look and report back to you. Would you mind explaining what you did so that I can hopefully learn from your experience? Thanks!


----------



## RSpecianjr (Jan 20, 2010)

Hey laxsox,

Well, it's kind of hard to explain but I'll try my best.

I used a series of two formulas. 



> =COUNTIF(D$2121,D21)


The above formula is used to count which instance of a particular month the associated data is in. When expanded, the range will also expand so once you get to the second instance, it will count that one and the previous instance.

and 



> =MATCH(B21,LARGE(IF(H$21:H$27=1,B$21:B$27),ROW(INDIRECT("1:"&COUNTIF(H$21:H$27,1)))),0)


This is an array formula that is used to rank as long as the column with the other formula indicates a 1 (indicating the first instance of a month). 

The least complicated way to describe the array formula is this. It uses row, indirect and countif to give you a list of number 1 to the number of unique months (no duplicates). So, 1 to 5 in this case.

It then uses an if formula to show only the values associated to unique months. Then, it uses the large formula to associate the values with the unique months and removing any false statements produced by non-unique months. This will produce an array of values in descending order. 

Lastly, the match will produce the instance number within that array based on the value in the row. Effectively giving you a descending rank if.

Hope that makes sense, let me know if you need me to elaborate = ).

Regards,

Robert D. Specian Jr.


----------



## laxsox (Oct 26, 2006)

I think it make a lot of sense. The only thing I'm not clear on is the use of INDIRECT, but I'll lookup the use of that function and see how I can use it on other sheets.

One quick quesiton, the resulting "#N/A" for some formulas... can that message be changed or how can I tell another cell to only look for a value that displays that resulting "#N/A"?


----------



## RSpecianjr (Jan 20, 2010)

Hey laxsox,

Indirect is a function that changes text to a reference. So, if you were to say have A1 as the value of B2, then =INDIRECT(B2) would be equivelent to =A1 and therefore produce the value of A1.

ISERROR is one way to deal with #N/A. So you could use something like:



> =ISERROR(MATCH(B21,LARGE(IF(H$21:H$27=1,B$21:B$27),ROW(INDIRECT("1:"&COUNTIF(H$21:H$27,1)))),0),"")


Regards,

Robert D. Specian Jr.


----------



## laxsox (Oct 26, 2006)

Unfortunately, that gives me the "You've entered too many arguments" error.


----------



## RSpecianjr (Jan 20, 2010)

oops, iferror not iserror

regards,

robert


----------



## laxsox (Oct 26, 2006)

Thanks! Dumb question... if a formula returns a negative value, how do I make it display as zero instead? I seem to remember a very easy way to do this without needing to add another function to the cell


----------



## RSpecianjr (Jan 20, 2010)

Hey laxsox,

There are a few ways. You can have add it to the formula

If(cell>=0,your formula,"")

or you can use data validation to hide the number. 

Regards,

Robert D. Specian Jr.


----------



## laxsox (Oct 26, 2006)

Which do you recommend as the cleanest and most efficient option? I'm a little worried about adding another formula to some already lengthy formulas.


----------



## RSpecianjr (Jan 20, 2010)

Hey laxsox,

It depends on what you are wanting. If you are going to be adding a range together, then the only option is use the formula. Otherwise, use data validation to change the text color to the same color as the background. The value will still be there, but you cannot see it.

Ultimately, I'd recommend the formula. It won't be a noticeable difference in calculation time.

Regards,

Robert D. Specian Jr.


----------



## laxsox (Oct 26, 2006)

Robert, Quick question, going back to the original formula to find duplicates... is there a way to add another column that says, if it is a duplicate, then what is it a duplicate of (say 13 months or 49 months)?


----------



## RSpecianjr (Jan 20, 2010)

Hey laxsox,

I'm not exactly sure what you are looking for but perhaps something like...



> =IF(H21>1,INDEX(D$21$27,MATCH(RANK($D21,$D$21:$D$27,0),G$21:G$27,0),1),"")


It's sloppy because its just thrown together, but it should give you what you want? I think haha

Regards,

Robert D. Specian Jr.


----------

