Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting
Hi
I have used large($e$14:$g$14,row(1:100) to sort sumtotals to new cells below the table. How can I sort the names belonging to these sumtotals (diferent cells than sumtotals) with these totals next to each other. in other words i need not only the sums but also the names belonging to the totals to sort together |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting
When you have created the list of totals using your formula there is no
longer a relationship between the totals and the names. You could do it with formulas provided that there was *never* two totals of the same amount. As this is a very unlikely scenario I would suggest turning on the Macro Recorder, copying the totals and names into two rows below the table and sorting them left to right. To make the macro more automatic you could paste it into a Worksheet_Change macro in the sheet module. Post back if you need further help -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "wynand" wrote in message ... Hi I have used large($e$14:$g$14,row(1:100) to sort sumtotals to new cells below the table. How can I sort the names belonging to these sumtotals (diferent cells than sumtotals) with these totals next to each other. in other words i need not only the sums but also the names belonging to the totals to sort together |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting
Sandy
thanks, please explain the last step to make automatic. Is there a way i can assign a short cut key to macro and save in personal.xls for future use? "Sandy Mann" wrote: When you have created the list of totals using your formula there is no longer a relationship between the totals and the names. You could do it with formulas provided that there was *never* two totals of the same amount. As this is a very unlikely scenario I would suggest turning on the Macro Recorder, copying the totals and names into two rows below the table and sorting them left to right. To make the macro more automatic you could paste it into a Worksheet_Change macro in the sheet module. Post back if you need further help -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "wynand" wrote in message ... Hi I have used large($e$14:$g$14,row(1:100) to sort sumtotals to new cells below the table. How can I sort the names belonging to these sumtotals (diferent cells than sumtotals) with these totals next to each other. in other words i need not only the sums but also the names belonging to the totals to sort together |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting
Thank you sandy!
"Sandy Mann" wrote: You can't save it in your personal.xls because it needs to be in the worksheet module of the workbook that you are using. Right-click on the sheet tab and select View Code. Select the sheet that you want it to work in from the Project window if it is not already selected and ensure that the left-hand window at the top of the module is saying Worksheet. Delete everything in the module, it probably says: ************************************* Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) End Sub ************************************** then copy and paste this macro into the Module: Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'Change the ranges if required If Intersect(Target, Range("E2:G13")) Is Nothing Then Exit Sub Application.EnableEvents = False Application.ScreenUpdating = False On Error GoTo GetOut Range("E14:G14").Copy Range("E17").PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("E1:G1").Copy Destination:=Range("E16") Application.CutCopyMode = False Range("E16:G17").Sort Key1:=Range("E17"), _ Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlLeftToRight Range("E16").Select GetOut: Application.EnableEvents = True Application.ScreenUpdating = True End Sub Now when you enter any value in E2:G13 the Macro will kick in and sort the totals under the table for you. If you enter data anywhere else then nothing will happen. The Macro assumes that the names are in Row 1 and the Totals are in Row 14. If the ranges are not correct the change them but keep the quotation makes around them. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "wynand" wrote in message ... Sandy thanks, please explain the last step to make automatic. Is there a way i can assign a short cut key to macro and save in personal.xls for future use? "Sandy Mann" wrote: When you have created the list of totals using your formula there is no longer a relationship between the totals and the names. You could do it with formulas provided that there was *never* two totals of the same amount. As this is a very unlikely scenario I would suggest turning on the Macro Recorder, copying the totals and names into two rows below the table and sorting them left to right. To make the macro more automatic you could paste it into a Worksheet_Change macro in the sheet module. Post back if you need further help -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "wynand" wrote in message ... Hi I have used large($e$14:$g$14,row(1:100) to sort sumtotals to new cells below the table. How can I sort the names belonging to these sumtotals (diferent cells than sumtotals) with these totals next to each other. in other words i need not only the sums but also the names belonging to the totals to sort together |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting
You're welcome. If you are still around, I accidentally left a line from
when I initially recorded the code. To select the cell to the right of the one you have just entered data into, change the line: Range("E16").Select To: Target.Offset(0, 1).Select or if you want the cursor to go down a Row change it to: -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "wynand" wrote in message ... Thank you sandy! "Sandy Mann" wrote: You can't save it in your personal.xls because it needs to be in the worksheet module of the workbook that you are using. Right-click on the sheet tab and select View Code. Select the sheet that you want it to work in from the Project window if it is not already selected and ensure that the left-hand window at the top of the module is saying Worksheet. Delete everything in the module, it probably says: ************************************* Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) End Sub ************************************** then copy and paste this macro into the Module: Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'Change the ranges if required If Intersect(Target, Range("E2:G13")) Is Nothing Then Exit Sub Application.EnableEvents = False Application.ScreenUpdating = False On Error GoTo GetOut Range("E14:G14").Copy Range("E17").PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("E1:G1").Copy Destination:=Range("E16") Application.CutCopyMode = False Range("E16:G17").Sort Key1:=Range("E17"), _ Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlLeftToRight Range("E16").Select GetOut: Application.EnableEvents = True Application.ScreenUpdating = True End Sub Now when you enter any value in E2:G13 the Macro will kick in and sort the totals under the table for you. If you enter data anywhere else then nothing will happen. The Macro assumes that the names are in Row 1 and the Totals are in Row 14. If the ranges are not correct the change them but keep the quotation makes around them. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "wynand" wrote in message ... Sandy thanks, please explain the last step to make automatic. Is there a way i can assign a short cut key to macro and save in personal.xls for future use? "Sandy Mann" wrote: When you have created the list of totals using your formula there is no longer a relationship between the totals and the names. You could do it with formulas provided that there was *never* two totals of the same amount. As this is a very unlikely scenario I would suggest turning on the Macro Recorder, copying the totals and names into two rows below the table and sorting them left to right. To make the macro more automatic you could paste it into a Worksheet_Change macro in the sheet module. Post back if you need further help -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "wynand" wrote in message ... Hi I have used large($e$14:$g$14,row(1:100) to sort sumtotals to new cells below the table. How can I sort the names belonging to these sumtotals (diferent cells than sumtotals) with these totals next to each other. in other words i need not only the sums but also the names belonging to the totals to sort together |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting
thanks
with the actual data the names needs to be below each other and the sumtotals are three sumtotals per individual with more constant data next to it I will however test and play around with your code until I get it right thanks again "Sandy Mann" wrote: You're welcome. If you are still around, I accidentally left a line from when I initially recorded the code. To select the cell to the right of the one you have just entered data into, change the line: Range("E16").Select To: Target.Offset(0, 1).Select or if you want the cursor to go down a Row change it to: -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "wynand" wrote in message ... Thank you sandy! "Sandy Mann" wrote: You can't save it in your personal.xls because it needs to be in the worksheet module of the workbook that you are using. Right-click on the sheet tab and select View Code. Select the sheet that you want it to work in from the Project window if it is not already selected and ensure that the left-hand window at the top of the module is saying Worksheet. Delete everything in the module, it probably says: ************************************* Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) End Sub ************************************** then copy and paste this macro into the Module: Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'Change the ranges if required If Intersect(Target, Range("E2:G13")) Is Nothing Then Exit Sub Application.EnableEvents = False Application.ScreenUpdating = False On Error GoTo GetOut Range("E14:G14").Copy Range("E17").PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("E1:G1").Copy Destination:=Range("E16") Application.CutCopyMode = False Range("E16:G17").Sort Key1:=Range("E17"), _ Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlLeftToRight Range("E16").Select GetOut: Application.EnableEvents = True Application.ScreenUpdating = True End Sub Now when you enter any value in E2:G13 the Macro will kick in and sort the totals under the table for you. If you enter data anywhere else then nothing will happen. The Macro assumes that the names are in Row 1 and the Totals are in Row 14. If the ranges are not correct the change them but keep the quotation makes around them. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "wynand" wrote in message ... Sandy thanks, please explain the last step to make automatic. Is there a way i can assign a short cut key to macro and save in personal.xls for future use? "Sandy Mann" wrote: When you have created the list of totals using your formula there is no longer a relationship between the totals and the names. You could do it with formulas provided that there was *never* two totals of the same amount. As this is a very unlikely scenario I would suggest turning on the Macro Recorder, copying the totals and names into two rows below the table and sorting them left to right. To make the macro more automatic you could paste it into a Worksheet_Change macro in the sheet module. Post back if you need further help -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "wynand" wrote in message ... Hi I have used large($e$14:$g$14,row(1:100) to sort sumtotals to new cells below the table. How can I sort the names belonging to these sumtotals (diferent cells than sumtotals) with these totals next to each other. in other words i need not only the sums but also the names belonging to the totals to sort together |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting
Dear Wynand
I have a similar but simpler version of your problem listed here. Can you please help? Column A has names (John, Jack, Mary, Sandy, Anna) and Column B has numbers corresponding to each (6, 4, 2, 7, 9). I want to write a formula that arranges my numbers from biggest to smallest and takes the corresponding name along with it. Any ideas? "wynand" wrote: thanks with the actual data the names needs to be below each other and the sumtotals are three sumtotals per individual with more constant data next to it I will however test and play around with your code until I get it right thanks again "Sandy Mann" wrote: You're welcome. If you are still around, I accidentally left a line from when I initially recorded the code. To select the cell to the right of the one you have just entered data into, change the line: Range("E16").Select To: Target.Offset(0, 1).Select or if you want the cursor to go down a Row change it to: -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "wynand" wrote in message ... Thank you sandy! "Sandy Mann" wrote: You can't save it in your personal.xls because it needs to be in the worksheet module of the workbook that you are using. Right-click on the sheet tab and select View Code. Select the sheet that you want it to work in from the Project window if it is not already selected and ensure that the left-hand window at the top of the module is saying Worksheet. Delete everything in the module, it probably says: ************************************* Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) End Sub ************************************** then copy and paste this macro into the Module: Private Sub Worksheet_Change(ByVal Target As Excel.Range) 'Change the ranges if required If Intersect(Target, Range("E2:G13")) Is Nothing Then Exit Sub Application.EnableEvents = False Application.ScreenUpdating = False On Error GoTo GetOut Range("E14:G14").Copy Range("E17").PasteSpecial Paste:=xlValues, _ Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("E1:G1").Copy Destination:=Range("E16") Application.CutCopyMode = False Range("E16:G17").Sort Key1:=Range("E17"), _ Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlLeftToRight Range("E16").Select GetOut: Application.EnableEvents = True Application.ScreenUpdating = True End Sub Now when you enter any value in E2:G13 the Macro will kick in and sort the totals under the table for you. If you enter data anywhere else then nothing will happen. The Macro assumes that the names are in Row 1 and the Totals are in Row 14. If the ranges are not correct the change them but keep the quotation makes around them. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "wynand" wrote in message ... Sandy thanks, please explain the last step to make automatic. Is there a way i can assign a short cut key to macro and save in personal.xls for future use? "Sandy Mann" wrote: When you have created the list of totals using your formula there is no longer a relationship between the totals and the names. You could do it with formulas provided that there was *never* two totals of the same amount. As this is a very unlikely scenario I would suggest turning on the Macro Recorder, copying the totals and names into two rows below the table and sorting them left to right. To make the macro more automatic you could paste it into a Worksheet_Change macro in the sheet module. Post back if you need further help -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "wynand" wrote in message ... Hi I have used large($e$14:$g$14,row(1:100) to sort sumtotals to new cells below the table. How can I sort the names belonging to these sumtotals (diferent cells than sumtotals) with these totals next to each other. in other words i need not only the sums but also the names belonging to the totals to sort together |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SORTING | Excel Discussion (Misc queries) | |||
Sorting | Excel Discussion (Misc queries) | |||
Sorting VLookup vs Sorting SumProduct | Excel Discussion (Misc queries) | |||
sorting SS# | Excel Discussion (Misc queries) | |||
Sorting: Sorting by the First Character | Excel Discussion (Misc queries) |