[ms office - 2010] Data sorting problem

[ken_e]

Hi everyone,

 

I'm having problems sorting data that I have copy pasted from another spreadsheet. When I select the range and try to sort a column by values it does not register as a number (even though there's a number in the table) and gives me an option from A to Z or Z to A, which gives the wrong result.

 

If I type the values in manually the Sort works. So I think the problem must be the numbers on the spreadsheet that I'm getting the data from. The numbers do come from an IF formula but I've made sure that I'm pasting special values. I've also tried formatting the cells to a number but still no luck.

 

Can anyone help me out? I'm usually quite good with these things but this one I've been stuck for 2hours when it should be something quite simple.

I've attached an example. I want to sort the name and strings in order of Titanium, Platinum, Gold, Silver. I've hidden irrelevant columns for easier viewing.

example.xls

[mac_shinobi]

Will look later and have a go

[Steve21]

What's not working on it? Seems to sort fine here, assuming I'm understanding the issue here.

 

You can pick the data, and do a custom column sort on any of the three columns and it's sorting fine here?

 

The first page (Page 1) won't work as it's locked so can't be sorted?

 

(Or am I missing the obvious)

 

Steve

[mac_shinobi]

Downloaded the example, launched / double click on the excel file ( Example ) --> go to the sort tab --> are you selecting just the 4 or so values or are you selecting the whole table which includes each persons name etc or how are you doing this ?

[ken_e]

Hi Steve, that's right I'm trying to sort in the other tab. Firstly by column E, then D, then C, in one hit. Column E is not recognising as a value but the other columns are.

In the end it should look like this:

Kate 1,1,1

Jess 2,2

Tim 1,1

John 1

 

Someone suggested using the text to column option and I found that works but I don't want to have to do that for all the spreadsheets if there is an easier way. Any ideas?

[ken_e]

Hi mac shinobi, I'm trying to sort the whole table so that the scores stick with the person they are assigned to.

[Steve21]

If it's just the E that's giving you the problem that's because you have more text than numbers in the column.

 

All you should need to do to fix it (But I can't really show it as Page 1 is locked), is put a ifnot value of 0. Then it'll have more than 1 number to be able to sort by:

 

kate	1.00	1.00	1.00
jess	2.00	2.00	0.00
tim	1.00	1.00	0.00
john	1.00	0.00	0.00

 

etc.

Steve