Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to 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.

Photo

Excel Sorting Problem


  • Please log in to reply
24 replies to this topic

#1 polaris6

polaris6

  • Members
  • 254 posts
  • OFFLINE
  •  
  • Local time:11:45 PM

Posted 05 April 2009 - 08:36 PM

I am not an XL expert; actually I am an XL-newbie. But I have a little problem dealing with sorting.

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 tos226

tos226

    BleepIN--BleepOUT


  • Members
  • 1,569 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:LocalHost
  • Local time:11:45 PM

Posted 05 April 2009 - 10:01 PM

Regarding your comment "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."

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 polaris6

polaris6
  • Topic Starter

  • Members
  • 254 posts
  • OFFLINE
  •  
  • Local time:11:45 PM

Posted 05 April 2009 - 10:31 PM

here it is:

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 polaris6

polaris6
  • Topic Starter

  • Members
  • 254 posts
  • OFFLINE
  •  
  • Local time:11:45 PM

Posted 05 April 2009 - 10:38 PM

Opps...

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 tos226

tos226

    BleepIN--BleepOUT


  • Members
  • 1,569 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:LocalHost
  • Local time:11:45 PM

Posted 05 April 2009 - 10:56 PM

I can hardly understand your display with numbers and letters. But I took some of your numbers and it worked for me. Please see
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 polaris6

polaris6
  • Topic Starter

  • Members
  • 254 posts
  • OFFLINE
  •  
  • Local time:11:45 PM

Posted 06 April 2009 - 07:27 AM

I am sorry...trying to explain an XL spreadsheet is like telling someone how to fly a 747.

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 polaris6

polaris6
  • Topic Starter

  • Members
  • 254 posts
  • OFFLINE
  •  
  • Local time:11:45 PM

Posted 06 April 2009 - 08:38 AM

This appears to be what I really want. How did you do that?

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 tos226

tos226

    BleepIN--BleepOUT


  • Members
  • 1,569 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:LocalHost
  • Local time:11:45 PM

Posted 06 April 2009 - 10:51 AM

This corresponds to the last image

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 :thumbsup:
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 tos226

tos226

    BleepIN--BleepOUT


  • Members
  • 1,569 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:LocalHost
  • Local time:11:45 PM

Posted 06 April 2009 - 11:17 AM

Hey, polaris6
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 polaris6

polaris6
  • Topic Starter

  • Members
  • 254 posts
  • OFFLINE
  •  
  • Local time:11:45 PM

Posted 06 April 2009 - 11:26 AM

I am sorry this is getting so complicated, but this is becoming very frustrating...with you as well. I am sure.

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 polaris6

polaris6
  • Topic Starter

  • Members
  • 254 posts
  • OFFLINE
  •  
  • Local time:11:45 PM

Posted 06 April 2009 - 11:59 AM

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.

#12 tos226

tos226

    BleepIN--BleepOUT


  • Members
  • 1,569 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:LocalHost
  • Local time:11:45 PM

Posted 06 April 2009 - 12:45 PM

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.

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 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.

OK, same as I was doing

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.

Without the apostrophes, I hope.

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 polaris6

polaris6
  • Topic Starter

  • Members
  • 254 posts
  • OFFLINE
  •  
  • Local time:11:45 PM

Posted 06 April 2009 - 03:09 PM

For your information, the values in A20:A25, come from another part of a large worksheet., something like C18, F18, I18, L18,..., BC18, which change in accordance with new input data. These cells (A20:A25 are variable in that sense, hence the notation in the sorting table '=A20', '=A21', etc.

#14 tos226

tos226

    BleepIN--BleepOUT


  • Members
  • 1,569 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:LocalHost
  • Local time:11:45 PM

Posted 06 April 2009 - 04:20 PM

Ok, I understand now - you were making a demo of sorts, hence those assignments. It didn't muddle the issue, but was unnecessary.

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 polaris6

polaris6
  • Topic Starter

  • Members
  • 254 posts
  • OFFLINE
  •  
  • Local time:11:45 PM

Posted 06 April 2009 - 04:30 PM

Thanks

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