How to organize data?

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.
0
rfox001 (2)
9/4/2003 4:07:22 PM
excel 39879 articles. 2 followers. Follow

3 Replies
1260 Views

Similar Articles

[PageSpeed] 41

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" <rfox001@go.com> wrote in message
news:2a6b0f47.0309040807.6d7f0c14@posting.google.com...
> 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.


0
9/4/2003 6:25:05 PM
"JohnI in Brisbane" <john.iseppi@inet.spamfooler.au> wrote in message news:<u4uElHxcDHA.2372@TK2MSFTNGP09.phx.gbl>...

> 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.
0
rfox001 (2)
9/5/2003 4:41:40 PM
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?
>
> > 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.

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.
>
> > 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.

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


0
9/5/2003 5:32:31 PM
Reply:

Similar Artilces:

Show external organizations within an organizations
Hi, Can anyone tell me how to show external organizations like courier services (who perform daily delivery services and are part and parcel of daily processes in an organization) in an organizational chart? Thanks for your support. Regards. -- akk ...

Taking data from a worksheet
Hi, I want to put a range of data into a worksheet. I want the user to input the values, integers (Child IDs) into the F column. From the F column, I want my subroutine to take these values, turn them into a comma separated list string, and put it into another string so it will look like this: SQL String = " WHERE ChildID IN ( " & Comma spearated list string & " ) " My question is, how do I create a comma separated list string with dynamic ranges? This should get you started: It assumes column F contains no data below the list and no gaps in the list. You may ...

Excel Data form
I am using the function data-form in excel on a blank data table with column heading in it only. whilst this worked ok on a previous version of the excel file and created a data form, it has stopped working, and returns the error: " database or list range is not valid". I have not made any changes to the file , but have added more worksheets. what is causing this and how can i resolve ? many thanks ...

Earn $200 daily with offline data entry job
Earn $200 daily with offline data entry job register now to start earning at http://moourl.com/fc7vo http://moourl.com/fc7vo http://moourl.com/fc7vo http://moourl.com/fc7vo ...

Sum, when data in adjoining column changes
I have spreadsheet with thousand of lines. Column A lists a name, and Column lists a percentage. When the name changes, I need to sum the percentages (they are supposed to add up to 100%) in Column C. Example: Column A Column B Column C AAA .50 AAA .25 AAA .20 AAA .05 1.00 BBB .10 BBB .10 BBB .75 BBB .04 BBB .01 1.00 (Sometimes the data in Column could be a number, such as 99-9999999.) Doing this by hand will take many hours, and I have to periodically repe...

How do I create a list in excel that contains external data?
I have spreadsheets linked to external data sources but I also need to add data to them. Whenever the data source is refreshed rows are correctly added or deleted from the columns containing external data but not from the columns containing my data. My rows end up not corresponding to the external data rows. I thought designating the entire data range as alist might help but I recieved an error that says you can't make a list that contains external data. Any one have suggestions? You could store the related data on a separate sheet. Then, on the worksheet with the external data...

Excel data disappeared after getting message about compatibility M
I tried to save changes to a spreadsheet, and received the following message: "Compatibility Report for New Customer List.xls Run on 4/6/2010 19:52 The following features in this workbook are not supported by earlier versions of Excel. These features may be lost or degraded when you save this workbook in an earlier file format. Minor loss of fidelity Some cells or styles in this workbook contain formatting that is not supported by the selected file format. These formats will be converted to the closest format available." I clicked OK, because fidelity is not imp...

Multiple Forms for Data Entry; re-loading data
Hi All, I have 15 forms set up to enter data in Excel which then gets stored in a hidden worksheet in the workbook. Right now it is set up so that the user can scroll through all 15 forms, enter the data, and then they are prompted to save to the database, which copies all the data over and erases the forms. I think it will be useful to allow them to re-load the data they've already entered, in case they need to make changes. So I'm imagining some kind of drop box or something that will display all the IDs associated with the information they've entered, and then they...

Repeating data when data matches
When the data column C matches the data in column A, I need the data in columns C and D to be repeated for all matching rows. For example: This is my data: a b c d 009701449-2 071712 009701449-2 071712 009701449-2 071712 009701452-4 008710 009701449-2 071712 009701455-7 008710 009701449-2 071712 009701460-1 008710 009701449-2 071712 009701465-6 058617 009701449-2 071712 009701467-8 059602 009701452-4 008710 009701468-9 059602 009701452-4 008710 009701455-7 008710 009701455-7 008710 009701460-1 008110 009701465-6...

Drive for data
I have two partitions for 2 OS. One of partition is for Windows 7 64-bit Home Premium. I would like to create a D drive in this partition to store data only. Currently software and data are stored on drive C. Do I need additional software to create D drive or there is built-in utility to accomplish it. Thanks, Scott Hi Scott, build in disk management (Computer -> Manage -> Disk Management or diskpart) can do several actions for you : Create, Shrink, Extend, Delete.... Disk Management - General Overview http://technet.microsoft.com/en-us/library/dd163558.aspx ...

Sot data and make it apperar in other sheet?
Hi. i have all my data in a "List table" in sheet1. How can i sort that data and make it appear in sheet2 automatically? is there a simply and obvious way that im missing? Thanks a lot SpeeD Sorting on a sheet won't magically make data appear on another sheet. Is the second sheet linked to the first? Sorting sheet1 will sort linked data on sheet2. Elsewise you will need VBA code to copy sorted data to sheet2 Gord Dibben MS Excel MVP On Thu, 1 Apr 2010 10:25:02 -0700, SpeeD <SpeeD@discussions.microsoft.com> wrote: >Hi. > >i ha...

Error Handling: When adding data where data is incomplete/incorrect.
Greetings, I added my error checking (see code below) on the Form "On Current" event as I believe this code will run upon any action on screen being actioned. Errors happen when users are adding incomplete/incorrect data then pressing the next navigation button which adds a record if it is the last record. Where is best to put my error validation? Thanks Rob Private Sub Form_Current() On Error GoTo MyErr ExtenuatingCount = DCount("[StudentID]", "tblStudentsResultsDelivery", "[StudentId]= '" & [txtStudentId] & "' AND [Extenu...

Link Data
Hi All, I have a table in a spreadsheet with a large amount of data in it. I then have a metrics page that calculates about 200 statistical calculations. I would like to seperate the metrics sheet from the main page as when I update the data, it has to recalculate the metrics page for each change. What is the best way to seperate the metrics page (I have considered turning on / off re-calculation but this is not acceptable). The metrics page utilises dynamic ranges and the name of the data file changes daily. I can think of a number of ways to do it but am sure that one is significantly ...

Public Calendar, Meeting Owner/Organizer
I have searched and searched for an answer to this question, and hopefully even if the answer is "no" somebody will answer. My question is, is there any way to see who created or is the owner of a particular meeting in a Public Folder/Shared Calendar? Thank you. if you change the view on the calendar to "by category", and make sure the "from" field is there, you should be able to tell who made an entry... -- Susan Conkey [MVP] "Christine Lisi" <ChristineLisi@discussions.microsoft.com> wrote in message news:0DA4B3D5-ED9D-4715-BC3E-0620B...

Data Base Link
I have a workbook with numbered data base items that I use along with other workbooks, which are calculators for this data base information. I currently go to the data base copy the whole row of information then go to the calculator workbook and paste this information. This is tedious and has the potential for errors because in my calculator workbook I have protected some functions in certain cells staggered across the destination row, so I would like to create a macro that will respect the protected cells and enable to data base information to transfer with the entry of the Item numb...

data range is too complex.
I am just changing over to excel 2007. . Most of my data will update to the embedded chart just fine but one tells me "the data range is too complex to be displayed. I don't know how to fix it -- Thanks for all your help. God Bless, Frank I had one like it and all I did to fix it was select the chart and right click on it, select data-> click on the icon at far right of Chart Data Range, use the mouse to select the data range, click on the icon at far right then OK. Note: If there are multiple ranges and the ranges are not adjacent, select the first range then hold the Ct...

Great Plains has no data integrity , PLS do not sell it
we get a many problems with Great Plains , no data integrity in program , also there are many dupliacate in transactions , it is very slow , they are saying it can use by 1000 users and 2M transaction , and it is no correacr word , if use just 5% you will get hang in system. i think it vere bad to microsoft to have this progra ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open ...

Convert One Column Data to Multiple Columns Data
I have a Query I am trying to Build that has the Following data: Name WO Days Old AD K-12345 10 AD K-12346 23 AD K-12368 02 AD K-12351 05 AD K-12386 20 What I am needing to is move the Days old Data to three Colums based off of the Days old Name WO 1-5 days 6-12 days 13+ Days AD K-12345 10 AD K-12346 23 AD K-12368 02 Something like that if it is possible. I have searched for this and could not find any other topics like...

Remote access to My Money data
I might be misunderstanding what the "Plus" product is, but it appearred to me that it allowed me to access my "Money" (accounts, transactions, budget, etc.) information online from any other computer. I thought it was saying that my "Money" data maintained within MSN online allowing me to access it from anywhere. Am I making stuff up? If I am, is there another solution to this that is simple and doesn't require a VPN or any other technical solution that I'd have to concoct? In microsoft.public.money, Give me Mo' Money <Give me Mo' Mo...

organizer
What hand held or pocket organizer syncs most easily with Outlook 2000 Thanks I think it really depends on the software you're using for the sync. See if the information on the following page helps: http://www.slipstick.com/addins/olpda.htm -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:F6BF3A5E-AD09-420B-8081-C9B26AB4C17D@microsoft.com, scorpiotwins wrote: > What hand held or pocket organizer syncs most easily with Outlook > 2000? Thanks ...

Cell data changes when copying and pasting
I've got Excel 2002 SP2. Another person in our office has Excel 2000. She entered a bunch of data into a spreadsheet for me (the data is dates in mm/dd/yyyy format). When I copy and paste her data into a new spreadsheet in one of my existing files, the dates change. This seems to be the rule of what is happening: when I copy and paste a cell into my sheet, it adds 1 to the day and 4 to the year. For example, if she typed 12/18/2003, it pastes it as 12/19/2007; if she typed 4/15/2004, it pastes as 4/16/2008. We are both baffled by this. There are no formulas being used, just a basic...

How to retrieve source data from data plots only?
Hi, I would appreciate any help on this. Basically, I have got some plots in Excel from my colleague. He had deleted all the source data used to generate those plots since he only cares about the plots. However, the source data is what I really need. I know by clicking on each data point in a plot, you can view the value of that data point. But is there a way to retrieve all the source data at once? There are simply too many data points in a plot for me to do this one by one. Thanks, Fang I have found a macro for doing this. I am posting the link here in case someone runs into the same p...

clipart organizer
I have one computer that automatically searches the web for clipart that matches the search criteria. I have two others that do not, and I would like them too. This is fairly important as a lot is done in Publisher. Does anyone have an idea of where or how to tell Publisher to check the net for clipart each time I search? Which is the version of Publisher? If it's 2003 then it automatically searches the web (Or you will have to select the web collections) "Em" wrote: > I have one computer that automatically searches the web for clipart that > matches the search ...

How to pass data from main appliaction to CDocument class
Hi, I am working on an MDI application. In this aplication, I have some global settings/configurations in the main aaplication (A dialog is used to set the configurations). I am adding Doc/View/Frame to applicatin from different DLLs (by using AddDocTemplate (fnGetMenu (), fnGetDoc (), fnGetFrame (), fnGetView ()) method. In this aaplication, I want to set some parameters in main application, and then pass these paramters to any new document created. Can anybody tell me how can I do this? You could do it in OnNewDocument() m_pSomeData = ((need to cast here to your main app class)AfxGet...

Getting data from a webpage
Okay, I'm needing to do this same thing again, but can't figure out where the item/innertext I need is: The webpage is: http://www.gocomics.com/calvinandhobbes/2010/01/16 ....and what I need is the image (on this particular page it's): http://imgsrv.gocomics.com/dim/?fh=83fba46523c3b2f31aeb078a548010a8&w=900.0 thanks, SouthernAtHeart "Joel" wrote: > You don't need to get the source. You can use the IE explorer like below to > help you filter the data. The source is under the IE.Document.all property. > You can get these lines...