How to organize data?

[RFox]

I am a frustrated Excel newbie. I grit my teeth every time I
have to use Excel. But I need to create a small database of
records and chart it's monthly progression, and I think
Excel is probably the best program for the job.
Only, I don't know who to do this.

Please bear with me as I really need help with this.

Say that I have a short list of vendors in different cities.
For each vendor I have a total # of items in their inventory
and another # of the # of items sold from the inventory, and
a percentage of that.

So I have the vendors listed in Column A.
# of items in inventory in Column B.
# of items sold in Column C.
% of items sold in Column D.
At the bottom (last line) after all the vendors, I have a
Sum total for Columns B,C,D.

Now I have a second set of data.
On the same worksheet, below the vendor numbers,
I have lines for each city in Column A.
Columns B,C,D are the same but for each city.
And another sum total line below that for all cities.

Now that's great. But I want to do this for each month.
How do I represent subsequent month's data?!
Do I have to use a separate worksheet for each month?
Is there no way to list each month together on the same sheet?

I will have a similar problem when it comes to charting this.
But I suppose the 3D charts will work with this, but there
are so many different chart options?!
Which ones work well?

Can anyone please help me with some advice?!
Thanks.

 

[JohnI in Brisbane]

RFox,

The answer will depend on the following-

How many vendors, inventory items, & cities do you have? What total volumes
of data do you expect each month?
Also how do you want to graph it, and how many months are to be shown. What
future requirements are expected by the Client?

The size & complexity of your data & graphs will determine-

- the structure of the data & whether it is better to use something else
like MS Access.

I love MS Excel but I know it has its limitations.
In one of my projects, I kept all the data in MS Access - and produced the
charts in MS Excel. This was because the people receiving the charts wanted
them in that format, but the data structure was way too complex with higher
volumes to handle easily in Excel.
I originally wrote the App in Excel, but maintaining it thru Client change
requirements became difficult.
If there had been no requirement for emailing Excel charts, I may have
written the second version totally in Access.

regards,

JohnI

 

[RFox]

How many vendors, inventory items, & cities do you have? What total volumes
of data do you expect each month?

The data pool itself is very small.
Just a handful of vendors each in about 5 cities.
Inventory items run totals run in the thousands and
ten-thousands, depending on the city.
So the data table is very small, which should make things
much easier to work with.

It's how to display successive months' totals that has
me stumped. I have the vendors and cities listed in each row,
and the numbers and percentages run down 3 columns.
How do I represent time?

Also how do you want to graph it, and how many months are to be shown. What
future requirements are expected by the Client?

I'm not sure how to graph it. I suppose the 3D bar graphs are
the most simple. But it seems like if you have a lot of data,
the bars are hard to read.

Maybe the simplest thing is to graph only the percentages
for each month, though that doesn't give a clear picture
of how the inventory changes.
I can have separate charts for vendors and cities.

The size & complexity of your data & graphs will determine-

It's a very small pool of data, which should simplify things.
I just don't know how to represent 3 different dimensions of
data in a 2-dimensional table.

Graphs are easier to represent in a 3D fashion.
But I don't know what type of graph works best.

- the structure of the data & whether it is better to use something else
like MS Access.

If I were to use Access, I suppose I can create each month
as a separate record, and put all the data in each month's record.
But I'd still need to import it to Excel to do the graphs.

I love MS Excel but I know it has its limitations.

I hate Excel.

In one of my projects, I kept all the data in MS Access - and produced the
charts in MS Excel. This was because the people receiving the charts wanted
them in that format, but the data structure was way too complex with higher
volumes to handle easily in Excel.

The data structure should be simple.
It's a small amount of data that I need to chart every month.
I just don't know who to represent 3 dimensions in a single
worksheet.

 

[JohnI in Brisbane]

RFox,

Thanks for replying.

The data pool itself is very small.
Just a handful of vendors each in about 5 cities.
Inventory items run totals run in the thousands and
ten-thousands, depending on the city.
So the data table is very small, which should make things
much easier to work with.

The problem with Excel is its limit of 65536 rows per sheet, so your data
format has to ensure it never can exceed that limit.
In your earlier post you said you had vendors & cities in the same sheet -
Could it be better to separate them into two separate sheets?

Adding the Month dimension, ( you mentioned below) can be handled in a
number of different ways-

1) It can be a separate worksheet or even workbook for each month. This will
require you to get the month's data together somehow for charting later.
In Excel I usually like a separate Workbook for each month, and use VBA
macros to manipulate the data into the form I want for say charting.
Removing historical data is handled the easiest this way.

2) or a separate column for each month, that is across Row 1 - Vendor, #
Inv, # Sold, % Sold, <Month1>, <Month 2>, etc. ->>>> where <Month 1> etc are
month names (Sep 2003).
This is a good format for the charts.

3) or you can have a column called Month - ie - Month, Vendor, # Inv, #
Sold, % Sold
where you insert the month name in each row of your data.
This is a great format for Filters and/or Pivot Tables.

It's how to display successive months' totals that has
me stumped. I have the vendors and cities listed in each row,
and the numbers and percentages run down 3 columns.
How do I represent time?


I'm not sure how to graph it. I suppose the 3D bar graphs are
the most simple. But it seems like if you have a lot of data,
the bars are hard to read.

Yes, you'll need 3D graphs - the I think Custom Types are best.

See "Area Blocks", "Columns with depth, and others.

Maybe the simplest thing is to graph only the percentages
for each month, though that doesn't give a clear picture
of how the inventory changes.
I can have separate charts for vendors and cities.


It's a very small pool of data, which should simplify things.
I just don't know how to represent 3 different dimensions of
data in a 2-dimensional table.

For the graphing, my second suggestion above for the data layout will be the
easiest. (the months are a separate dimension).
Try creating some sample charts, and see how the data has to be formatted to
produce that chart.
That'll give you a better idea of your data layout for that purpose.

Graphs are easier to represent in a 3D fashion.
But I don't know what type of graph works best.

I hate Excel.

What do you usually use? Access?

The data structure should be simple.
It's a small amount of data that I need to chart every month.
I just don't know who to represent 3 dimensions in a single
worksheet.

see above

regards,

JohnI