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!

 

[HKaplan]

To understand clearly, in the example data you provided, Row 1 is a
single merged "heading" cell spanning all the data columns. Row 2
contains the field headings, and the subsequent rows are the data?
And you have a line break before the next heading and data set? Or is
this an example of how you want the report to appear.

First thoughts are using a pivot table and custom sort.

Can you elaborate a little?

 

[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!

 

[HKaplan]

Assuming the account, site, rep, and billing person are fields
(columns) in the table (along with project, job, qty, etc.), you can
get exactly what you want with a pivot table. If not I recommend
starting a new table with those columns added.

You could even have a single "header" field in your table that has the
account, site, rep, etc. data as a single text string (as you showed
above), assuming those are consistent. Meaning Account A rep is
always Chicago Rep John and billing Jane. If this is an ever changing
combination then they need to be in separate fields in the table.
Then Pivot tables are the ticket.

Is the "report" you showed above, actually how the data is entered
now? Excel prefers the data in neat columns and rows.

If I know how the raw data entry is structured I can suggest a pivot
table design for you.

 

[HKaplan]

For simple filtering use Data | Filter | Auto Filter. Then click on
the column header and select "Chicago" to diplay only Chicago.
However, this assumes your data is in a single table, with one header
at the top. Not formatted as you showed.

And you might consider creating a new field to "close" the project,
rather than deleting this. Then you will have all the data - open and
closed projects.

 

[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!

 

[HKaplan]

If you can restructure your table to have the following heading, and
(I suppose) discipline the data entry people to follow that structure,
then your options are wide open. They can even have their own tables
that you aggregate into a single table if that works for you. And
don't delete records, marke them as completed:

Account Location Rep Billing Project Job Qty Due to Ship Status

Then you can use Data | Filter | Auto Filter as I mentioned before,
you can sort any columns you want, you can summarize by account,
location, etc. using pivot tables. As I understand how your current
table is set up, Excel will have a tough time reading it as a single
database. Excel databases don't like blank rows and merged cells in
the table.

If this works for you, I can show you how to summarize using pivot
tables.

 

[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?

 

[HKaplan]

You really need to restructure the table as I described before, into a
continuous (no line breaks, no merged cells) table. Your options go
up 300% if you do this.

You can create a macro or a little VBA code to copy the cell entries,
but assuming your current data is not too large, just copy what you
need and recommend your users adopt the new table style. As you can
see, they will only see their info (with just a few mouse clicks to
filter), and you will have many more reporting options.