Jump to content
  • GUESTS

    If you want access to members only forums on HSO, you will gain access only when you Sign-in or Sign-Up .

    This box will disappear once you are signed in as a member. ?

Sorting Table in Microsoft Excel


Coach1310

Recommended Posts

I am trying to create a table in Excel where I use the values listed in one of the columns to keep it sorted. These values will need to be updated regularily and I would like for the table to be resorted automatically. Anybody able to help me with this?

Thanks

Link to comment
Share on other sites

you can select the column (click on the letter in the header of your column) you want to sort by and then click on the sort key in your menu bar. it will give you a couple options to sort from. I have MS Excel '07, so unless you have the same I can't give you more defined instructions unless I was at my work computer.

I think the key itself is the same though. If so it's the AZ (A over Z) with a down arrow next to it.

Hope this helps.

Link to comment
Share on other sites

Okay.... I have Excel '03 and the instructions you gave me worked to sort the column, but how do I get it to move the all the other parts of that row with it....

EXAMPLE

If I have the following each in their own colums, how do I get it to sort the entire table due to the value of a certain column

Month # of sales % increase revenue profit

January 27 11 $5000 $285

February 19 09 $3200 $134

etc....

I think you get the idea. How do I get it to sort by the revenue column or # of sales column AND take the other values of that row with it?

Thanks again

Link to comment
Share on other sites

Anytime I've tried to sort in MS Excel '03, there was another window that opened and had something like 3 options to sort by. Only choose the top and leave the others blank.

Let me see if I can dig into my work e-mail account and find a file that I worked on and open to find out for sure.

Link to comment
Share on other sites

I don't have Excel '03 here at home but if you look a little closer in the sort menu options you should be able to find a means to sort with filtering. Select the columns you want to include and then open the sort filter and define which column you want to use as your sort basis.

Link to comment
Share on other sites

You can do this with a macro. The macro would look like this.

Private Sub Worksheet_Calculate()

Range("A1:B9").Sort _

Key1:=Range("B2"), _

Order1:=xlAscending, _

Key2:=Range("A2"), _

Order2:=xlAscending, _

Header:=xlGuess, _

OrderCustom:=1, _

MatchCase:=False, _

Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal, _

DataOption2:=xlSortNormal

End Sub

Link to comment
Share on other sites

Okay.... I have Excel '03 and the instructions you gave me worked to sort the column, but how do I get it to move the all the other parts of that row with it....

EXAMPLE

If I have the following each in their own colums, how do I get it to sort the entire table due to the value of a certain column

Month # of sales % increase revenue profit

January 27 11 $5000 $285

February 19 09 $3200 $134

etc....

I think you get the idea. How do I get it to sort by the revenue column or # of sales column AND take the other values of that row with it?

Thanks again

Highlight the all the columns that have the data you want sorted. If you have data in columns B, C, and D, click on the B column at the top and highlight C and D with it by holding down the left mouse button on B and dragging across C and D. Or, just highlight all the cells that have the data you wish to sort.

Go to 'Data' on the toolbar and then click on 'sort' in the drop down menu.

In the window that opens select which column you wish to sort by and if you want it ascending or descending.

Link to comment
Share on other sites

Thanks for the info guys, but I have tried all of your suggestions on my work computer Excel 03 and my home computer which is excel '07 on Vista. I still haven't been able to get it to work.... not sure where to go from here. I appreciate all the help... Thanks again.

Link to comment
Share on other sites

Thanks for the info guys, but I have tried all of your suggestions on my work computer Excel 03 and my home computer which is excel '07 on Vista. I still haven't been able to get it to work.... not sure where to go from here. I appreciate all the help... Thanks again.

Maybe it would help if you describe to us exactly the process you are trying to use?

I have both Excel 03 and 07 and the sort works as others have described.

If you simply want to resort the whole table, just click the square box in the upper left corner of the sheet (just to the left of column A, and just above the row numbers). This should cause the whole sheet to be selected or "highlighted", if you will. Then click the "Sort" button and a dialog box opens asking how you want to sort the data.

Link to comment
Share on other sites

In upper right hand corner of the spreadsheet there is a small cell that is the select whole worksheet. Press that and then go to data and sort and pick the column you want to sort by. That will sort your spreadhsheet by that column. If you want to sort automatically as you edit it.... If found this on the web. Basically you need to create VB script.

Quote:
There shouldn't be a macro to run. Dave's code runs on the WorkSheet_Change event. i.e. this code is run whenever a cell is change. You would need to tweak it to only run when data is entered into column "G" e.g.:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

'Don't run this code unless a value is last entered in column "G"

If Not Target.Column = "7" Then Exit Sub

'Prevents endless loops

Application.EnableEvents = False

'Assumptions

'1. Data only housed in Columns A:G

'2. The first row contains headings or labels

'3 Column "G" is used as the sort criteria

Me.UsedRange.Sort Key1:=Columns("G"), Order1:=xlAscending, _

Header:=xlYes, OrderCustom:=1, MatchCase:=False, _

Orientation:=xlTopToBottom

Application.EnableEvents = True

End Sub

Now, to use this code:

1. Right click on the name tab of the worksheet that will use house the data

2. Select "View Code"

3. Copy and paste the above code into the worksheet module that appears

4. Hit ALT+F11 to return to the spreadsheet

5. Type away merrily safe in the knowledge that your data is getting sorted based on teh order of values in column "G".

NOTE:: Check my assumptions in the comments and change the code to reflect what you want.

Link to comment
Share on other sites

Highlight it all, and insert a list.

Okay/next all of the pop up box questions. It will box in all of your data.

Go to the column you want to sort by and click the up/down triangles on the column header. It will let you then sort by what you want and the rows will follow accordingly.

I think this is what you are asking for.

Link to comment
Share on other sites

Join the conversation

You can post now ↓↓↓ or ask your question and then register. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.



×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use and Privacy Policy. We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.