Sorting By Custom List

[silverbluemoon]

I am using MS Excel 2010. I have created a custom list with the following data in this order:

CY2010Q1
CY2010Q2
CY2010Q3
CY2010Q4
CY2011Q1
CY2011Q2
CY2011Q3
CY2011Q4
CY2012Q1
CY2012Q2
CY2012Q3
CY2012Q4
CY2013Q1
CY2013Q2
CY2013Q3
CY2013Q4

I have selected the entire worksheet by clicking the upper left hand corner of the sheet so that everything is selected. I have four columns of data and I SORT on my custom list. Each time I run it on column 4 ("Quarter"), it gives me a different result. It appears random. Some example results:

SORT #1:

CY2011Q4
CY2010Q4
CY2011Q1
CY2011Q1
CY2010Q2
CY2011Q1
CY2011Q2
CY2012Q4
CY2012Q2
CY2012Q4
CY2012Q1
CY2011Q1
CY2011Q1
CY2011Q2
CY2011Q2
CY2012Q1
CY2010Q3
CY2010Q1
CY2012Q4
CY2012Q1
CY2011Q2
CY2010Q2
CY2011Q4
CY2012Q2

SORT #2:

CY2011Q2
CY2012Q3
CY2011Q4
CY2010Q3
CY2011Q1
CY2010Q2
CY2011Q4
CY2012Q2
CY2012Q4
CY2011Q4
CY2010Q3
CY2011Q1
CY2012Q1
CY2011Q1
CY2010Q2
CY2012Q1
CY2011Q1
CY2010Q4
CY2011Q1
CY2010Q2
CY2010Q4
CY2012Q1
CY2011Q1
CY2012Q4

SORT #3:

CY2012Q3
CY2012Q2
CY2011Q2
CY2011Q1
CY2010Q4
CY2011Q1
CY2010Q2
CY2012Q4
CY2010Q2
CY2011Q2
CY2011Q2
CY2011Q1
CY2011Q4
CY2010Q2
CY2011Q1
CY2011Q2
CY2012Q1
CY2010Q3
CY2011Q1
CY2010Q2
CY2011Q4
CY2011Q1
CY2010Q3
CY2010Q4

So, the sorts above are on the same data with the same custom list with zero change in the sort parameters. The column is a Text formatted column. Can you tell what I'm doing wrong?

I'm sure it's a perfectly rookie mistake, but as I can't solve it, I've come to the you guys, the experts. I am so ridiculously frustrated.

Thanks in advance for any help you can offer!

 

[Jerry Sullivan]

Does that column have formulas or constant values in the cells?

 

[silverbluemoon]

Does that column have formulas or constant values in the cells?

It contains results of formulas. Is this the problem? Can I not sort based on a value derived from a formula?

 

[Jerry Sullivan]

Sorting by cells that have formulas can be problematic- particularly if they have relative references to other cells.

What's probably happening is that the "cells" are getting sorted in the order that you want, but the value of those cells changes when the cell is in a new row

You can test that temporarily adding a column with copy pasted as values from your Column 4
Sort by all columns by Column 4 and you should see that the column pasted as values is in the desired order but Column 4 is not.

 

[silverbluemoon]

[Solved] Re: Sorting By Custom List

Sorting by cells that have formulas can be problematic- particularly if they have relative references to other cells.

What's probably happening is that the "cells" are getting sorted in the order that you want, but the value of those cells changes when the cell is in a new row

You can test that temporarily adding a column with copy pasted as values from your Column 4 Sort by all columns by Column 4 and you should see that the column pasted as values is in the desired order but Column 4 is not.

So I copied the values only into a new column and sorted and guess what?? It works!! You are correct also that the column based on formulas still refuses to cooperate. Good call, thank you so much!!

So, one more thing then... would you say it's good practice to copy table of just values to a new sheet and sort them there, rather than try to sort a table of values based on formulas? Is this what most people do?

 

[Jerry Sullivan]

Re: [Solved] Re: Sorting By Custom List

There are some formulas that are ok to sort on- like those that will evaluate to the same value regardless of where the formula is in the column.

When possible have your formulas use lookup functions instead of relative references to other sheets.

You shouldn't need to copy your tables to a new sheet, and sorting only works if you don't need the formulas any more (otherwise when you copy them back the problem will persist.

If you don't need the formulas, it would be easier to just copy-paste the values in your original table instead of making a copy on another sheet.

 

[silverbluemoon]

Re: [Solved] Re: Sorting By Custom List

Got it! I will definitely take all of this into consideration! You've been a great help - thanks again! :D