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
LVL 9
Rowby GorenAsked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
I have just discovered one advantage of table rather than standard list.

You can apply an AutoFilter to a standard list but you ncan only have one AutoFilter active on a particular sheet.

If you have multiple tables on a sheet, each table can have its own filter. However having multiple tables on a sheet has its own problems.

If the tables are side by side, filtering on one table hides the whole row so hides entries in the other table.

If the tables are one above the other, adding data to one doesn't push the other table down unless you physically insert a row rather than just typing below existing table and automatically getting included in the table.

Pressing "Ctrl +" within the table inserts a row without the need for the subsequent popup for selecting whether the insert pushes cells right or down or inserts whole row or column.

Thanks
Rob H
0
 
duttcomConnect With a Mentor Commented:
Attached is a modified version of your file. I have converted your data into a table and have added a custom sorting that sorts by Zip and then square footage so the sort result looks exactly like your example.

To see the custom sort, right click on a table heading such as Property Zip and pick Sort-> Custom Sort from the menu.

Tables are a much more powerful way to control your data.
sorting-for-experts-exchange.xlsx
0
 
Rowby GorenAuthor Commented:
Hi  duttcom,

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
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
duttcomCommented:
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.
0
 
Rowby GorenAuthor Commented:
Got it!  I will be back at the office in the a.m. and will do the table and short.

Thanks!
0
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
Why do you need to add a Custom Sort?

Surely you are just sorting on two columns. Select the data and choose the Sort button.

First Sort Level - Property ZIP
Click Add Level button
Second Level - Square Footage

This will put all ZIPs together and sort within the group on Square Footage.

Converting to a table will help with addition of data, formulas and formats will be copied down as new entries are made but the sort will still have to be re-applied.

Thanks
Rob H

Edit - I have just tried the Custom Sort file and it does exactly the same as a Standard Sort. Adding new data to bottom of list and then clicking Sort, the previous Sort criteria are still there so just click OK.
0
 
Rowby GorenAuthor Commented:
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.
0
 
Rob HensonFinance AnalystCommented:
Multiple sort levels only sort within the group.

Level 1 sorts whole data set, subsequent levels sort within the level 1 groups.
0
 
Rowby GorenAuthor Commented:
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
0
 
Rowby GorenAuthor Commented:
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
0
 
Rowby GorenAuthor Commented:
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.
0
 
Rob HensonFinance AnalystCommented:
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.
0
 
Rowby GorenAuthor Commented:
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
0
 
Rowby GorenAuthor Commented:
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
0
 
Rowby GorenAuthor Commented:
Thanks and sorry for the huge delay in awarding the points!

Rowby
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.