Sorting just a selection of rows on a spreadsheet
Hello Excel Experts
I have a spreadsheet with names and addresses. The spreadsheet is initially arranged by zip code (In the attached, the column is called Property Zip) (there is also a Mailing zip, but I am ignoring that)
Basically what I want to do is to be able to select a block of rows and sort that block by (in this case, by the Square footage column.
For example, in the attached sheet I have 3 zip code rows in the Property Zip column 90064, 90066 and 90402
First I want to be able to select the 90064 rows, and sort those rows by the Square Footage column. (and not affect any other rows in other zip codes, of course -- that is why I am selecting the 90064 rows). :)
Then I want to select the 90066 rows and sort those rows by the Square footage column.
Finally I want to select the 90402 rows and sort those rows by the Square footage column.)
In summary -- I know how to sort, but I don't know how to sort within a row selection -- and not affect any other row(s) that are not selected.
Any suggestions? The attached is a small file, but I have much bigger files that I have to do this with.
Is this core basic Excel. OR do I need a formula to do this?
Thanks!
sorting-for-experts-exchante.xlsx
I have a spreadsheet with names and addresses. The spreadsheet is initially arranged by zip code (In the attached, the column is called Property Zip) (there is also a Mailing zip, but I am ignoring that)
Basically what I want to do is to be able to select a block of rows and sort that block by (in this case, by the Square footage column.
For example, in the attached sheet I have 3 zip code rows in the Property Zip column 90064, 90066 and 90402
First I want to be able to select the 90064 rows, and sort those rows by the Square Footage column. (and not affect any other rows in other zip codes, of course -- that is why I am selecting the 90064 rows). :)
Then I want to select the 90066 rows and sort those rows by the Square footage column.
Finally I want to select the 90402 rows and sort those rows by the Square footage column.)
In summary -- I know how to sort, but I don't know how to sort within a row selection -- and not affect any other row(s) that are not selected.
Any suggestions? The attached is a small file, but I have much bigger files that I have to do this with.
Is this core basic Excel. OR do I need a formula to do this?
Thanks!
sorting-for-experts-exchante.xlsx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
To convert to a table-
Select the entire block of data, column headers and all. Go to the Insert tab where you will see a Table button. Click that.
You will be asked to confirm the range of cells for the table and if the range you have selected includes headers. Click okay and the data range will be converted to a table.
To do the custom sort, as I mention above, you can right-click on one of the column headers and choose Sort -> Custom sort and then replicate the sort settings in the example. You will see at the top of the custom sort dialog that you can add different levels of sorting.
You can also filter the records in a table by clicking on the arrows in the column headings and selecting which records you wish to view or filter out.
Select the entire block of data, column headers and all. Go to the Insert tab where you will see a Table button. Click that.
You will be asked to confirm the range of cells for the table and if the range you have selected includes headers. Click okay and the data range will be converted to a table.
To do the custom sort, as I mention above, you can right-click on one of the column headers and choose Sort -> Custom sort and then replicate the sort settings in the example. You will see at the top of the custom sort dialog that you can add different levels of sorting.
You can also filter the records in a table by clicking on the arrows in the column headings and selecting which records you wish to view or filter out.
ASKER
Got it! I will be back at the office in the a.m. and will do the table and short.
Thanks!
Thanks!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The reason (I assume) I need a custom sort is I need to sort within zip codes.
Main sort - zip code then square footage.
I will be trying it this morning on a big file -- so we can see if it the custom sort takes care of my issue.
Main sort - zip code then square footage.
I will be trying it this morning on a big file -- so we can see if it the custom sort takes care of my issue.
Multiple sort levels only sort within the group.
Level 1 sorts whole data set, subsequent levels sort within the level 1 groups.
Level 1 sorts whole data set, subsequent levels sort within the level 1 groups.
ASKER
Hi
Sorting it via multiple sort seemed to do the trick. You can see the attached.....
Is there anything else I need to know?
BTW I have done multiple sorts before, but not applied to a table. Can you explain the advantages, in mutlple-sorts, with using a table compared to just ordinary multiple sorts in this particular spreadsheet???
Rowby
for-experts-exchange-2.xlsx
Sorting it via multiple sort seemed to do the trick. You can see the attached.....
Is there anything else I need to know?
BTW I have done multiple sorts before, but not applied to a table. Can you explain the advantages, in mutlple-sorts, with using a table compared to just ordinary multiple sorts in this particular spreadsheet???
Rowby
for-experts-exchange-2.xlsx
ASKER
One more question.
Is there a way to do multiple column sort to sort by COLOR.
As you can see I have three main color backgrounds. As I look at it now I see that the colors have multiple zip codes,
Can I sort by background color, followed by Square footage? Excel 2007.
Rowby
Is there a way to do multiple column sort to sort by COLOR.
As you can see I have three main color backgrounds. As I look at it now I see that the colors have multiple zip codes,
Can I sort by background color, followed by Square footage? Excel 2007.
Rowby
ASKER
I tried it. I did multiple sort, first selecting the 3 cell colors, and then the square footage. Did it in a table.
And it worked perfectly.
So I guess that handles this question?
Any other comments I might want to keep in mind.
And it worked perfectly.
So I guess that handles this question?
Any other comments I might want to keep in mind.
I haven't had much use of tables so wouldn't like to comment on their advantages.
Are the colours in your cells hard coded or conditional formatting?
If hard coded, a user could change the colour and that row could end up incorrectly sorted.
I don't how you would do the Conditional Formatting for ZIP codes unless you are going to group them eg 80000 to 89999, 90000 to 99999 etc.
Are the colours in your cells hard coded or conditional formatting?
If hard coded, a user could change the colour and that row could end up incorrectly sorted.
I don't how you would do the Conditional Formatting for ZIP codes unless you are going to group them eg 80000 to 89999, 90000 to 99999 etc.
ASKER
Hi
I'm the only one using the spreadsheets.
So it is basically working with the solution here.
I"ll be awarding the points later today.
Thanks all!
Rowby
I'm the only one using the spreadsheets.
So it is basically working with the solution here.
I"ll be awarding the points later today.
Thanks all!
Rowby
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for that info Bob H.
SInce tables are so easy to create -- and based on what you found out, then tables are the way to go.
Rowby
SInce tables are so easy to create -- and based on what you found out, then tables are the way to go.
Rowby
ASKER
Thanks and sorry for the huge delay in awarding the points!
Rowby
Rowby
ASKER
Thanks for helping.
Looks good. So the steps I would take is to
1) Convert the entire sheet into a table. Looks like it is named the default Table 1. Remind me how to convert a spreadsheet into a table.
2) You created a custom sort. I know how to do a custom sort by two columns manually, but it looks like you built the two column sort that into the table.
Can you explain how you built in that custom sort
Then I will be able to use your solution on other similarly structured excel sheets.
Thanks!!
Rowby