how to sort the rows based on data in one column

M

maria86004

Version: 2004 Operating System: Mac OS X 10.4 (Tiger) How do I explain this?
I have an excel spread sheet that I got from the county assesor.
They gave me a list of names and addresses.
Name is in one column, street address in another,city in one, state in one, zip code in another.
In Column D is the city. Most of the cities are listed as Flagstaff. I am trying to sort these out for two mailings. First I want to make a list of all of the rows that list in Column D as any city But Flagstaff.
Then I want a second list of all rows that list in column d as Flagstaff.
Is there a way I could perhaps sort this list by having all of the Flagstaff rows at the top of the page and then all of the other city rows at the bottom?
I am trying to do two mailings and the printer said to give her two lists on an excel spread sheet.
Or do I just have to do this manually.

If anyone has any ideas on how to make this task simpler, I sure would appreciate it. I have 5000 rows of addresses to sort.
 
H

Harvey Waxman

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)

How do I explain this? <br>
I have an excel spread sheet that I got from the county assesor. <br>
They gave me a list of names and addresses. <br>
Name is in one column, street address in another,city in one, state in one, zip code in another. <br>
In Column D is the city. Most of the cities are listed as Flagstaff. I am trying to sort these out for two mailings. First I want to make a list of all of the rows that list in Column D as any city But Flagstaff. <br>
Then I want a second list of all rows that list in column d as Flagstaff. <br>
Is there a way I could perhaps sort this list by having all of the Flagstaff rows at the top of the page and then all of the other city rows at the bottom? <br>
I am trying to do two mailings and the printer said to give her two lists on an excel spread sheet. <br>
Or do I just have to do this manually. <br><br>If anyone has any ideas on how to make this task simpler, I sure would appreciate it. I have 5000 rows of addresses to sort.

Maybe find/replace? Add a space to the beginning of all Flagstaff and sort the sheet on column D. Then they will be at the beginning (or the end) of the sort.
 
C

CyberTaz

Well, there are several ways but quite frankly it shouldn't be necessary. If
the "printer" knows anything about Excel they should be able to do this as a
part of the service. Or if she's using a program such as Word to generate
the mailings the one list can be filtered as a part of each Merge. But, if
you really need to do this here's one of the simplest ways:

For the Fairfield list;

1- Select a cell in the list then go to Data> Filter> AutoFilter,
2- Open the dropdown for the City field & select Fairfield,
3- Select & copy the filtered list, paste to a new sheet

For the non-Fairfield list;

1- Reopen the City dropdown & select Custom Filter,
2- Select 'does not equal' in the left list, Fairfield in the right, OK
3- Select & copy the filtered list, paste to another sheet

HTH |:>)
Bob Jones
[MVP] Office:Mac
 
J

John McGhie

Well, there are a wide variety of different ways to skin this particular cat
:)

However, since you want them as two different lists, the easiest way is:

1) First, sort the entire sheet into order by City. You may like to add a
second Sort criteria so the names are in some predictable order within each
city, but it doesn't matter.

2) Copy the entire list

3) Create two new worksheets

4) Right-click in Cell A1 of each and choose Paste Special... Paste Link

That will give you three sheets that contain exactly the same thing. The
second two will update anytime the first one (which is the master) does.

In Sheet 2, turn on AutoFilter and choose to see ONLY "Flagstaff" in Column
D

In Sheet 3, Choose to see everything EXCEPT Flagstaff in Column D.

There you are: two lists, sorted by City, one containing only Flagstaff, the
other containing everything else.

Hope this helps

Version: 2004 Operating System: Mac OS X 10.4 (Tiger) How do I explain this?
I have an excel spread sheet that I got from the county assesor.
They gave me a list of names and addresses.
Name is in one column, street address in another,city in one, state in one,
zip code in another.
In Column D is the city. Most of the cities are listed as Flagstaff. I am
trying to sort these out for two mailings. First I want to make a list of all
of the rows that list in Column D as any city But Flagstaff.
Then I want a second list of all rows that list in column d as Flagstaff.
Is there a way I could perhaps sort this list by having all of the Flagstaff
rows at the top of the page and then all of the other city rows at the bottom?
I am trying to do two mailings and the printer said to give her two lists on
an excel spread sheet.
Or do I just have to do this manually.

If anyone has any ideas on how to make this task simpler, I sure would
appreciate it. I have 5000 rows of addresses to sort.

--

The email below is my business email -- Please do not email me about forum
matters unless I ask you to; or unless you intend to pay!

John McGhie, Microsoft MVP (Word, Mac Word), Consultant Technical Writer,
McGhie Information Engineering Pty Ltd
Sydney, Australia. | Ph: +61 (0)4 1209 1410 | mailto:[email protected]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top