Cannot group cells in excel..

Microsoft Microsoft office excel 2007 ac...
September 4, 2010 at 14:18:31
Specs: Windows Vista
I want to group cells in excel so that they are arranged the same even if i choose to arrange the whole database alphabetically. I have a list of persons with names in one column then the scores in the next 20 odd columns, one person is on one row. Now i would like to alphabetically sort these persons and keep the correct scores grouped with the personcolumn. I would also like to sort the other columns but keeping the row still intact so that i have the person in question complete with all scores and name even if i arrange all on one score category, for instance in column 18. All i need is a tool to group cells, but can't find it.

Mr. E.

See More: Cannot group cells in excel..

Report •

September 4, 2010 at 14:45:00

Try this:

For example, you have headings in row 1, with "Name" as the heading for column A, in A1
B1 to U1 have other headings for the data.
There is data associated with each name in columns B to U on rows 2 to 30

Select all the cells A1 to U30
From the ribbon select 'Data' and then the 'Sort' Icon
In the Sort dialog, check the 'My data has headers' check box
In the Column drop-down, select "Name" (the heading for your column of names) as the 'Sort by' column.
'Sort On' can remain as its default of 'Values' and 'Order' is A to Z
Click OK

All the rows are now sorted alphabetically by Name in column A.
The data in columns B to U associated with each name remains associated with the same names.

If you have a column labeled "Latest Score", the same approach works - select "Latest Score" as the 'Sort by' column, and for 'Order' select 'Largest to Smallest' and OK and you get the data sorted by that column, but all the rows of data remain intact - associated with their original names (which after this sort are no longer in alphabetical order).

Is that what you were looking for.


Report •

September 4, 2010 at 21:12:13
Since it sounds like you might want to follow Humar's instructions on a regular basis, I took his suggested steps and wrote a macro to perform them.

The code will ask the user to input the Column Header that (s)he want to sort on and then perform the sort.

If the user enters a string that the code can't find in A1:U1, it will give the user the opportunity to try again.

The code is based on the same ranges that Humar used in his example. Adjust as required.

Option Explicit
Sub MySort()
Dim myKey, myTry As String
Dim c
'Get column header from user
 myKey = Application.InputBox("Please Enter Column Header", Default:="Name")
'End Sub if user Cancels
  If myKey = "False" Then Exit Sub
'Find Header String in Header Row
   With Sheets(1).Range("A1:U1")
    Set c = .Find(myKey, LookIn:=xlValues, lookat:=xlWhole)
'Header is found, use it as the sort key
      If Not c Is Nothing Then
       Sheets(1).Range("A1:U30").Sort _
         Key1:=Range(c.Address), Order1:=xlAscending, Header:=xlYes, _
         OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
'If not, ask user if (s)he wants to try again
        myTry = MsgBox(myKey & " Not Found." & _
                       vbCrLf & vbCrLf & _
                       "Care To Try Again?", _
'If yes, start over. If not, fall through to End Sub
        If myTry = vbYes Then GoTo getColumnHeader
       End If
   End With
End Sub

Report •

September 5, 2010 at 04:15:44
thanks a million times, great reply's both of you. will work on the marcro as i need to translate it to norwegian excel... thumbs up, very happy now

Report •

Related Solutions

Ask Question