Solved: Sorting Question

[SamAshN]

Ok, I've got what I believe is a fairly basic question, but it's stumpin me.

If I've got database of the following:

Joe 2 7 9
Sam 1 3 4
Dan 3 5 8

So, the first two columns of numbers are simply data i've entered. The third column is a sum of the first two (a2 + a3) .... (or 2 + 7 = 9).

My question is this. is there a way for me to sort the third column (containing the formula) into ascending or descending? I'd like to eventually get this:

Joe 9
Dan 8
Sam 4


Any help is greatly appreciated. Thanks!

[lucas]

Do you wish to do this with vba or just native excel function?
to sort without vba just select all of the data and go to Data-Sort
select colulmn C and decending...

[lucas]

I'm sorry...I guess that would be column D and decending

[SamAshN]

First, I'd prefer to do it in Excel, as I don't know any Visual Basic...but if there's a simple way, I'd be happy to give it a try.

Second, the simple "sort ascending" and "sort descending" buttons don't work because the value in column d is the result of a formula. As soon as it sorts it, the reference fields are changed and the values change.

[SamAshN]

I guess I could make the field references static ($a$2 + $a$3)...but there are two difficulties with this...

1. I'd have to manually add the $ to every cell for a large database.

2. I'd like to associate the name with the number. ie: when column d gets sorted, the name that's associated with the value in column d gets sorted as well (with respect to column d).

thanks for any help you can provide.

[lucas]

Hi Sam,
could you post a sanitized version of your workbook? You keep using A2 and A3 and I think you mean B1+C1 for columns....am I right in this assumption?

Second, the simple "sort ascending" and "sort descending" buttons don't work because the value in column d is the result of a formula. As soon as it sorts it, the reference fields are changed and the values change.

This should not be happening and can only be happening if you are not selecting all of the data that you wish to sort...if you only select part...then only that part is sorted and yes your formula's will get messed up.

[SamAshN]

You're right, it's b1 + c1. My mistake. So I'd be sorting column d. Everything is flowing horizontally. If you still need me to post a copy, I can in an hour or so. I'm at work now, and have to get back.

thanks.

[lucas]

select all of the data..not just the column you want to sort by...

[lucas]

You should select any data that might be to the right of column D also so that each row stays together.....thought I should add that clarification.

[lucas]

You're right, it's b1 + c1. My mistake. So I'd be sorting column d.

Sam..this is where your making your mistake I think....you don't want to just sort column d....you want to select all of the data and sort by column d.

[lucas]

If it's easier you can use Control+A to select the entire worksheet and then sort by column D decending.

[SamAshN]

Alright! It worked. I knew it was something simple that I was missing. Thanks for the help, I've been frustrated by this for a while.

Sidenote, glancing through the posts here I've seen alot of visual basic references. How hard is it to learn / apply to excel?

[lucas]

How hard is it to learn / apply to excel?

It's VBA in Excel that is posted here...Visual Basic for Appications which is not really Visual Basic which can create stand alone executable programs.

Mostly depends on your need and how much your willing to put into it I guess. Most of the little I know I learned from following posts that interest me here at the forum....VBA Espress does offer some training for a small fee.

I attached to this post an example from your question that has 2 macros in it that were both created using the macro recorder....Tools-macro-record new macro.

The macro's can be viewed by hitting Alt+F11 and look for module1 on the left in the project explorer..double click module1 to see the code.

Just start the recorder and go through your operations and click on stop when you have fininshed. The code does some extra operations that can be shortened later but it will give you a start and you can post questions here.

Be sure to mark your thread solved using the thread tools at the top of the page when you have your solution..you can still post followup questions after marking it solved.