sorting

[wynand]

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[wynand]

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]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[wynand]

Thank you sandy!

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[wynand]

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

 

[Wereman]

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?