Solved

Sorting just a selection of rows on a spreadsheet

Posted on 2013-11-26
15
139 Views
Last Modified: 2014-08-03
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
0
Comment
Question by:Rowby Goren
  • 9
  • 4
  • 2
15 Comments
 
LVL 12

Assisted Solution

by:duttcom
duttcom earned 167 total points
ID: 39679410
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
 
LVL 9

Author Comment

by:Rowby Goren
ID: 39679540
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
 
LVL 12

Expert Comment

by:duttcom
ID: 39679582
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
 
LVL 9

Author Comment

by:Rowby Goren
ID: 39679651
Got it!  I will be back at the office in the a.m. and will do the table and short.

Thanks!
0
 
LVL 31

Assisted Solution

by:Rob Henson
Rob Henson earned 333 total points
ID: 39680584
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
 
LVL 9

Author Comment

by:Rowby Goren
ID: 39680693
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39680843
Multiple sort levels only sort within the group.

Level 1 sorts whole data set, subsequent levels sort within the level 1 groups.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 9

Author Comment

by:Rowby Goren
ID: 39680940
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
 
LVL 9

Author Comment

by:Rowby Goren
ID: 39681001
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
 
LVL 9

Author Comment

by:Rowby Goren
ID: 39681043
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 39681403
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
 
LVL 9

Author Comment

by:Rowby Goren
ID: 39681453
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
 
LVL 31

Accepted Solution

by:
Rob Henson earned 333 total points
ID: 39683264
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
 
LVL 9

Author Comment

by:Rowby Goren
ID: 39683660
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
 
LVL 9

Author Closing Comment

by:Rowby Goren
ID: 40237400
Thanks and sorry for the huge delay in awarding the points!

Rowby
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now