Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Order the names by ranking
I have a list of collaborators with an associated classification. With
the function rank, I obtain the ranking of each one. And now I intend that whenever it modifies the classification, that the names of the collaborators are presented by order of ranking, automatically. Somebody can help me? Here's an example: collaborators classification rank galvao 75,8 8 pereira 70 11 ferreira 66 12 almeida 85 5 fournel 73,2 9 leal 62 17 rodrigues 55 18 hpereira 63 16 santos 64,5 15 jcruz 65 14 godinho 66 12 martins 88 2 amorim 86 4 mendes 80 6 nunes 87 3 ppereira 89 1 raposos 77 7 teixeiras 71 10 Thanks in advance! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Order the names by ranking
Where I said order I meant sort
Alexandra Lopes wrote: I have a list of collaborators with an associated classification. With the function rank, I obtain the ranking of each one. And now I intend that whenever it modifies the classification, that the names of the collaborators are presented by order of ranking, automatically. Somebody can help me? Here's an example: collaborators classification rank galvao 75,8 8 pereira 70 11 ferreira 66 12 almeida 85 5 fournel 73,2 9 leal 62 17 rodrigues 55 18 hpereira 63 16 santos 64,5 15 jcruz 65 14 godinho 66 12 martins 88 2 amorim 86 4 mendes 80 6 nunes 87 3 ppereira 89 1 raposos 77 7 teixeiras 71 10 Thanks in advance! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Order the names by ranking
Say we have data in cols A & B like:
Name Score Curley 8 Shep 88 Moe 5 Larry 100 We would like to have this data automatically sorted anytime a value in cols A or B has changed. (in this example Larry would automatically move to the top) In the worksheet code area install this small event macro: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target n = Cells(Rows.Count, "A").End(xlUp).Row Set r = Range("A1:B" & n) If Intersect(t, r) Is Nothing Then Exit Sub Application.EnableEvents = False r.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlYes Application.EnableEvents = True End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu2007k "Alexandra Lopes" wrote: I have a list of collaborators with an associated classification. With the function rank, I obtain the ranking of each one. And now I intend that whenever it modifies the classification, that the names of the collaborators are presented by order of ranking, automatically. Somebody can help me? Here's an example: collaborators classification rank galvao 75,8 8 pereira 70 11 ferreira 66 12 almeida 85 5 fournel 73,2 9 leal 62 17 rodrigues 55 18 hpereira 63 16 santos 64,5 15 jcruz 65 14 godinho 66 12 martins 88 2 amorim 86 4 mendes 80 6 nunes 87 3 ppereira 89 1 raposos 77 7 teixeiras 71 10 Thanks in advance! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Order the names by ranking
Hi,
I made what you said myself but the data had not been moved..they stay in the initial order, don't move.. Could you help me? Gary''s Student wrote: Say we have data in cols A & B like: Name Score Curley 8 Shep 88 Moe 5 Larry 100 We would like to have this data automatically sorted anytime a value in cols A or B has changed. (in this example Larry would automatically move to the top) In the worksheet code area install this small event macro: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target n = Cells(Rows.Count, "A").End(xlUp).Row Set r = Range("A1:B" & n) If Intersect(t, r) Is Nothing Then Exit Sub Application.EnableEvents = False r.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlYes Application.EnableEvents = True End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Order the names by ranking
Sure let's debug in a fresh, new worksheet:
1. put some names in col A 2. put some random values in col B (NOT SORTED) 3. install the macro in the proper area (instruction in previous post) 4. change a value in column B 5. tell me what occurred. -- Gary''s Student - gsnu200813 "Alexandra Lopes" wrote: Hi, I made what you said myself but the data had not been moved..they stay in the initial order, don't move.. Could you help me? Gary''s Student wrote: Say we have data in cols A & B like: Name Score Curley 8 Shep 88 Moe 5 Larry 100 We would like to have this data automatically sorted anytime a value in cols A or B has changed. (in this example Larry would automatically move to the top) In the worksheet code area install this small event macro: Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target n = Cells(Rows.Count, "A").End(xlUp).Row Set r = Range("A1:B" & n) If Intersect(t, r) Is Nothing Then Exit Sub Application.EnableEvents = False r.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlYes Application.EnableEvents = True End Sub Because it is worksheet code, it is very easy to install and use: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Order the names by ranking
Hi,
I made the 4 steps that you said me and I saw no changes in the data.. I am using excel 2007. . I don't know if it interests. Gary''s Student wrote: Sure let's debug in a fresh, new worksheet: 1. put some names in col A 2. put some random values in col B (NOT SORTED) 3. install the macro in the proper area (instruction in previous post) 4. change a value in column B 5. tell me what occurred. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Order the names by ranking
Hi,
I'm sending you an example..And I would like to have this data automatically sorted anytime a value in cols Q has changed. I hope that this helps.. Thanks Alexandra Lopes wrote: Hi, I made the 4 steps that you said me and I saw no changes in the data.. I am using excel 2007. . I don't know if it interests. Gary''s Student wrote: Sure let's debug in a fresh, new worksheet: 1. put some names in col A 2. put some random values in col B (NOT SORTED) 3. install the macro in the proper area (instruction in previous post) 4. change a value in column B 5. tell me what occurred. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Order the names by ranking
Hi,
I've repeat everythinh again (the 4 steps that you said me) and it's nice, this results:) Thank you very much! Gary''s Student wrote: Sure let's debug in a fresh, new worksheet: 1. put some names in col A 2. put some random values in col B (NOT SORTED) 3. install the macro in the proper area (instruction in previous post) 4. change a value in column B 5. tell me what occurred. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Order the names by ranking
Hello Alexandra,
An example without a macro: http://www.sulprobil.com/html/sorting.html Regards, Bernd |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Order the names by ranking
Hi Bernd P,
Thank you very much for the example. Regards, Alexandra Bernd P wrote: Hello Alexandra, An example without a macro: http://www.sulprobil.com/html/sorting.html Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ranking order of cells. | Excel Discussion (Misc queries) | |||
ranking names | Excel Discussion (Misc queries) | |||
How do you put names in abc order | New Users to Excel | |||
Ranking in order | Excel Discussion (Misc queries) | |||
Order ranking. Any suggestions? | Excel Worksheet Functions |