Setting up account headings for sorting data that is dynamic

[Lizzie M]

I need advise on how to set up a list of accounts. Currently the account
heading is in a row of merged cells and the rows below hold columns of
information. The amount of rows is always changing as projects are added. I
want to be able to sort the accounts according to site location rather than
alphabetically and back again. There are about 100 accounts each fluctuating
between 1 and 100 projects over a month. How can I include the account
heading and the data in the sort. For example:

Account A Chicago Rep: Jon Smith Billing: Jane Doe
Project # Job# Qty Due to Ship Status
11111 1 50 2/2/08 D
12345 2 75 2/4/08 C

Account B Cincinati Rep: Jon Smith Billing: Jane Doe
Project # Job# Qty Due to Ship Status
22222 4 60 2/3/08 D
45678 8 70 2/10/08 C

Account C Chicago Rep: Jon Smith Billing: Jane Doe
Project # Job# Qty Due to Ship Status
33333 5 55 2/5/08 D
89123 3 80 2/11/08 C

So how do I sort through each account within the entire document to include
all the account data? It is not necessary to sort the data, just the account
headings. I would really appreciate any suggestions. Thanks!

 

[Lizzie M]

That is correct, that is how the report appears now, not how I want it to
appear. And there is a line break before the next heading and data set.

 

[Lizzie M]

Also, each day this data is updated to add or remove projects from each
Account. The accounts are listed in a workbook as shown in the example, but
each account is from a different site, perhaps 25 various accounts per sight.
What I would like to do, for example is sort all the jobs for all the
accounts located in Chicago, eliminating the need to scroll through data that
doesn't pertain to the accounts in Chicago. Does that clarify? Thanks!

 

[Lizzie M]

The document begins with the column headers for project, job, qty, etc, then
each account has its own header with a single text string in a merged cell
followed by the columns of data directly under it. Each account begins after
the data from the last account with a single text string in a merged cell and
a space between the data and the new account header. The information in the
account header is consistent. I am curious to know more about pivot tables
and if this is the way to do it, then also, can each account header have a
label, one for the actual name of the account and the other for the site
location, so it can be sorted on site and back again to be sorted on account?
Thanks!

 

[Lizzie M]

I actually can't do that. I have to keep the text string at the head of the
columns for each account since I don't actually maintain the spreadsheet, but
just use the data. We want to make it easier for our site to find all its
accounts and filter out other account information for quick reference. Can
this be done the way the worksheet is formatted currently? Thanks for your
help!

 

[Lizzie M]

Hey, this works!! I just added a separate column where I inserted the word
Chicago next to all data and headings pertaining to Chicago. I can turn the
filter on and off as needed. Is there any way to avoid having to fill in all
the cells with the word Chicago each time we want to sort it? Can that be
automatically populated?