**BleepingComputer**, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having

**no ads**shown anywhere on the site.

**Click here to Register a free account now!**or read our

**Welcome Guide**to learn how to use this site.

# Excel Sorting Problem

### #1

Posted 05 April 2009 - 08:36 PM

I have attached a test program that has a list of name to which I have attached a 'grade'. I then submit the 'grade' to an evaluation factor using a formula and store it in the column next to the grade.

Now, I want to sort the evaluation factor column in descending numeric order (with the name and original column).

I find that when I try to sort the 3rd column (the one in which I have applied the evaluation factor), the sorting is done on the basis of the alphabetical/numeric characters of the formula, rather than the numeric characters in the column itself.

While this appears to be a 'dummy' exercise, it represents one which I have fashioned for D27 in evaluating them for their educational and navigational performances. I actually do the listing and computations elsewhere on the spreadsheet and have transferred the cell values to the bottom of the 'test' page as shown.

The sorting seems to work well if the numbers in the cells are strictly numbers; however, if the cells contain formulas (which are converted into numbers that are displayed, the sorting does not seem to work.

Help...or am I asking the impossible?

I have tried to attach the XL file but I can't

### BC AdBot (Login to Remove)

### #2

Posted 05 April 2009 - 10:01 PM

I imagine that you did something similar to what I just did and posted a picture of

http://img13.imageshack.us/img13/8749/excelsort.jpg

I made some sort of a formula evaluation of grades. Original data starts in Col.A.

I copied the same data over to the right, Col.E and sorted on Col.G where you can see in the formula bar what's there.

Worked for me. So your design must be something different from what I understand.

If you could post, say a grade, and the evaluation formula it would be helpful.

**Edited by tos226, 05 April 2009 - 10:09 PM.**

### #3

Posted 05 April 2009 - 10:31 PM

A B C

name Score Evaluation

Helen 56(A20) 224(=B2*4)

Mike 66(A21) 396(=B3*6)

Jimmy 33(A22) 198(=B4*4)

Alice 14(A23) 28(=B5*2)

John 42(A24) 210(=B6*5)

Timmy 26(A25) 26(=B7*1)

I need to sort Col C for the numeric values with the highest number first, next highest next, etc.

What I get is B2, first, B3, second, etc.

It didn't look that way when I typed it in the block...sorry.

### #4

Posted 05 April 2009 - 10:38 PM

The B Col has the following:

=A20

=A21

=A22

=A23

=A24

=A25

=A26

The following cells have the following numbers in them.

A20(56)

A21(66)

A22(33)

A23(14)

A24(42)

A25(26)

### #5

Posted 05 April 2009 - 10:56 PM

http://img24.imageshack.us/img24/7333/excelsort2.jpg

and note that not only it got sorted but the formulas got nicely carried over, nothing is lost.

If you'd post a picture the way I'm doing, it might help.

I do not understand some of your writing, such as cell A20 contains 56, then in another place it has somebody's name. Sorry, but I'm confused what you have in what cell.

### #6

Posted 06 April 2009 - 07:27 AM

I will try again.

A20 has in it the number 56

A21 has the number 66

A22 has the number 33

A23 has sthe number 14

etc

B2 has the formula =A20, which displays the number 56

B3 has the formula =A21, which displays the number 66

B4 has the formula =A22, which displays the number 33

B5 has the number =A23, which displays the nuymber 13

etc

### #7

Posted 06 April 2009 - 08:38 AM

Let me be sure we are doing the same things.

In your col B, for example B2, is there a formula? In my case there would be the formula =A20, where A20 would have the number 56; B3 would have the formula =A21, where A21 would have the number 66, and so on.

In your col C, for example, C2 would have the formula =B2*4 and the number 224 would be displayed; C3 would have the formula =B3*6 and the number 396 would be displayed, and so on.

Then you would sort on cols B & C to get cols E & F.

I would actually have an additional column depicting names associated with each row.

I used the Data>>Sort>> function at the top of the page.

### #8

Posted 06 April 2009 - 10:51 AM

B,C,D,E,F

56,=B4*4,,66,=E4*6

66,=B5*6,,56,=E5*4

33,=B6*4,,42,=E6*5

14,=B7*2,,33,=E7*4

42,=B8*5,,14,=E8*2

26,=B9*1,,26,=E9*1

I have no idea how to display things so they align. So, after switching to formula view, I just made a .csv (comma separated values), a text file, and copied the text here. I did include the column names on top.

I do not have your column where you do =A21 since I see no point in doing it.

So, no, in colB I do not have a formula, I have a value. Formula is in Col.C, columns A and D are empty, sort of placeholders for your graded victims' names

Copy of data is in col.E, copy of calculation (readdressed by Excel) is in col.F

Yes, I selected just the data range seen in columns E and F, then did Data > Sort > selected column F, descending.

Edit:

Regarding "Then you would sort on cols B & C to get cols E & F."

No. I copied the original data from B and C to E and F in order to preserve the original data so I could show you all this in pictures. Then I sorted E and F. It doesn't matter where it is. I could have sorted on B and C.

**Edited by tos226, 06 April 2009 - 11:00 AM.**

### #9

Posted 06 April 2009 - 11:17 AM

I may have reproduced your problem.

Did you by any chance insert a space in your formulas before the "=" sign?

If so, those calculated cells are no longer formulas, but text, and get sorted as text.

When I inserted space in the formula, the sorted results were, again in a different location -

After

26 =B21*1

42 =B20*5

14 =B19*2

33 =B18*4

66 =B17*6

56 =B16*4

while they should be

After sort

66 396

56 224

42 210

33 132

14 28

26 26

**Edited by tos226, 06 April 2009 - 11:18 AM.**

### #10

Posted 06 April 2009 - 11:26 AM

In my scheme (using your array), every number displayed in Col B (1:6) is derived from a formula, eg., =A20,=A21, =A22, =A23, =A24, =A25, that is, =A20 is in B1, =A21 is in B2, etc.. A20 contains the number 56; A21 contains the number 66; A22 contains the number 33, etc.. The value of these formulas are displayed as 56, 66, 33, 13, 42, and 26, respectively.

In Col C (C1:6) each number is derived from formulas as follows: =B1*4, B2*6, B3*6, B4*2, B5*5 and B6*1. =B1*4 is in C1; =B2*6 is in C2, etc.. These values are displayed as: 224, 396, 198, 26, 210, and 26, in C1 thru C6, respectively.

I, too, have eliminated the names and headers.

If I insert numbers only in the columns, and sort on Col C, the sorting works fine. However, when I insert the formulas, it does not.

### #11

Posted 06 April 2009 - 11:59 AM

### #12

Posted 06 April 2009 - 12:45 PM

I have no idea why you do this, but it doesn't harm things. Seems redundant to me. Once you have a number in A20, what's the point of setting col.B value to be the same.In my scheme (using your array), every number displayed in Col B (1:6) is derived from a formula, eg., =A20,=A21, =A22, =A23, =A24, =A25, that is, =A20 is in B1, =A21 is in B2, etc.. A20 contains the number 56; A21 contains the number 66; A22 contains the number 33, etc.. The value of these formulas are displayed as 56, 66, 33, 13, 42, and 26, respectively.

OK, same as I was doingIn Col C (C1:6) each number is derived from formulas as follows: =B1*4, B2*6, B3*6, B4*2, B5*5 and B6*1. =B1*4 is in C1; =B2*6 is in C2, etc.. These values are displayed as: 224, 396, 198, 26, 210, and 26, in C1 thru C6, respectively.

Without the apostrophes, I hope.There are no spaces preceeding the formulas. For example, the B cols have the following in them '=A20', 'A21', etc. The C cols have '=B1*6', '=B2*4', etc.

At this point I am out of ideas. If I come up with something, I'll post.

Could you save your worksheet under a new name to preserve what you now have.

Then open it again, and try to repair - I think it's under options or tools.

Perhaps someone else will see something obvious. It might be that the SORT command got broken someplace.

### #13

Posted 06 April 2009 - 03:09 PM

### #14

Posted 06 April 2009 - 04:20 PM

I have one idea, you can send me your little demo worksheet that sorts wrong. I don't want to see any other data, nor any other questions via mail, as the exchange should stay on the forum. I think the way it's done by using BC mail is to click on my name and select Send message from the dropdown list, but I don't know for sure.

I can try it on my computer and we'll go from there (or not).

### #15

Posted 06 April 2009 - 04:30 PM

I am off to a meeting and will be back around 10 PM.

Later, and thanks again.

#### 0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users