Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

sorting columns

  Asked By: Ashan    Date: Jan 03    Category: MS Office    Views: 745
  

i have the following problem.

my excel sheet has the following columns

name marks
abc 10
def 12
ghi 25
jkl 78
mno 23
pqr 19
xyz 89

how do i sort the column "marks" in descending order of marks? and i
need one more column where the name corresponding to the max. marks
comes first.

i am new to vba programing. please do help.

Share: 

 

7 Answers Found

 
Answer #1    Answered By: Fergus Jones     Answered On: Jan 03

You don't need vba  to do this, just a basic sort. With any cell in the
list active, select 'Sort' from the 'Data' menu and the dialog which
comes up will allow you to sort  on an ascending or descending basis by
any column. Play with the various options and you'll soon see how it
works.

I'm not sure what you mean about this extra column. Please elaborate.

 
Answer #2    Answered By: Dashiell Jones     Answered On: Jan 03

but i want the columns  sorted and the sorted marks should be put in a
different column. in the "sorted names" column, the name
corresponding to the max. marks goes first...etc. the final thing
should look like this.

name marks sorted marks sorted names
abc 10 89 xyz
def 12 78 jkl
ghi 25 25 ghi
jkl 78 23 mno
mno 23 19 pqr
pqr 19 12 def
xyz 89 10 abc

what would be the vba  code for this one. please help

 
Answer #3    Answered By: Emily Campbell     Answered On: Jan 03

Or, the super-quick way...

Copy column  A and paste it in column E. Copy column B and paste it in
column D. Select any cell in D or E and do the Data - Sort thing. Then
delete column C.

And if you do all this using the macro recorder, you'll have your code!

 
Answer #4    Answered By: Brooke Robertson     Answered On: Jan 03

Why delete column  C?

Rami's request did seem strange at first because for each row, the new items
are not related to the original items. Normally, all items in a row are
related. It appears that he needs something like a report that lists
students and their marks in alphabetical order  and then a second list next
to it sorted by marks. I'm not sure why the order of the columns  in the
second list is reversed.

 
Answer #5    Answered By: Trina King     Answered On: Jan 03

Well, I initially left C blank, so that when he sorted from D, the order
in D and E would change, but A & B wouldn't. Once that was done, I could
then delete the empty column  to give him the form of output he
specified. I agree that these look like two separate 'reports' so maybe
keeping C empty would be a better idea. I suppose it depends what he
wants to do with it next.

 
Answer #6    Answered By: Baden Smith     Answered On: Jan 03

The basis for your macro should be something like:

Range("A1:B14").Select
Selection.Copy
Range("D1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("E2"), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal


The macro selects the original columns  (the first two in this case) and
copies them to the 3rd and 4th column. Columns 1 and 3 are names and columns
2 and 4 are marks.

After the Paste, the new columns are the current selection.

This new selection is then sorted by the 4th column  ("E" - marks).

 
Answer #7    Answered By: Zeke Thompson     Answered On: Jan 03

Again, you can do this without vba, using normal functions. I'd use
LARGE to rank them in order  in the third column, then INDEX and MATCH
combined to get the names into column  4.

Excel help  should give you what you need on LARGE, and I have an article
which shows how to combine INDEX and MATCH here:

http://www.grbps.com/Excel6.pdf

 
Didn't find what you were looking for? Find more on sorting columns Or get search suggestion and latest updates.




Tagged: