Sort problem in Excel 2003

Question

I created a 3 column, 28 row PRIMARY spreadsheet that is sorted daily.  All cells are formated as 'numeric'.  The cells in column 'A' equal the sum of the numeric values contained in columns 'B' and 'C' for that row.  I sort the spreadsheet in ascending order first by the number in column 'A' first and the second sort is by the number shown in column 'B', again in ascending order.  This second sort is to place any rows with identical values in column 'A' into ascending order based on the value shown in column 'B'.

All of the cells in columns 'B' and 'C' of this PRIMARY spreadsheet pull their values from another spreadsheet's cells.  In all cells on the PRIMARY spreadsheet, the values pulled over from the other spreadsheet are positive numbers.  Four of the cells (columns 'B' and 'C' on two separate rows) on the PRIMARY spreadsheet , however, pull their values from cells in the other spreadsheet where the values in those cells are positive numbers that have been multiplied by a constant contained in that cell (i.e. 4***1.5** = 6 or 8***1.5** = 12).

When I sort the PRIMARY spreadsheet first by the value in column 'A' in ascending order, it sorts properly running from the lowest column 'A' value down to the highest column 'A' value and grouping any like column 'A' value rows next to each other.

HOWEVER, WHEN THE SECOND SORT IS APPLIED (BASED ON THE VALUES IN COLUMN 'B') THE SORTING FUNCTION DOES NOT CORRECTLY INTERPRET THE VALUE SHOWN IN THOSE COLUMN 'B' CELLS FOR EITHER OF THE TWO ROWS WHERE A VALUE MULTIPLIED BY A CONSTANT APPEARS.  THE SORT WILL PLACE THIS ROW ABOVE A ROW THAT ACTUALLY HAS A LOWER VALUE IN COLUMN 'B', THEREBY INCORRECTLY ORDERING ROWS THAT HAVE LIKE VALUES IN COLUMN 'A' BUT DIFFERENT VALUES IN COLUMN 'B'.

DOES ANYONE KNOW WHY THIS OCCURS AND HOW I CAN PREVENT IT FROM HAPPENING?

Answer 1

So much for the easy answers. If you have confirmed that all of the problem cells are formatted as numbers and the 2 sort criteria are being applied during a single sort operation then the easy answers are eliminated.

Can you post sanitized copies of the files on a file sharing site so we can look at them directly?

Answer 2

I am doing both sorts in one operation, first a primary sort on Column A followed by a secondary sort based on the values in column B.

Answer 3

When you talk about second sort, do you mean 2 separate operations or are you "properly" doing both sorts in one operation?

Answer 4

Is the file shared, i am able to reproduce this data on my excel file and able to sort appropriately.

Ensure that the column b and c is formatted correctly.

Thank you for your responce.  The file is shard and all cells are formated as 'numeric' with '0' decimal places.  Whereas my spreadsheet still does not sort properly, I am wondering if the '0' decimal places restriction somehow causes the four cells that have a numeric value multiplied by the constant (1.5) to be read incorrectly by the sort function.  The 'result' of that multiplication function is brought over to the PRIMARY spreadsheet for sorting.  Could it be that the number brought over carries some sort of code with it regarding the 1.5 multiplier that trips up the sort function once it is aware of the decimal place included in the 1.5 constant ?

Answer 5

Is the file shared, i am able to reproduce this data on my excel file and able to sort appropriately.

Ensure that the column b and c is formatted correctly.