#### Doing Analysis from large amounts of DATA

```Hi all,

I have an attachment that's apart of this message & within it I have 3
tabs 1) Revenue 2) Expenses & 3) Net Position. I would like to do some
Analysis on the above three thou a way where I can manipulate the Data.
How would I go about doing this? I've tried a Pivot Table thou it didn�t
work out in the sense that it was messy & just failed in calculating
stuff, not sure if I did it right thou.. Would really appreciate all
the help as I actually have about 20 of these sheets which from that
fall into 4 groups being 5 sheets per group. I hope this all makes
sense, would appreciate all the help.

Cheers
Will

+-------------------------------------------------------------------+
|Filename: Book44.zip                                               |
+-------------------------------------------------------------------+

--
william4444
------------------------------------------------------------------------
william4444's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33991

```
 0
5/12/2006 1:44:55 PM
excel.newusers 15348 articles. 2 followers.

3 Replies
263 Views

Similar Articles

[PageSpeed] 34

```What kind of analysis are you trying to do?
Where does the data come from?

--
bgeier
------------------------------------------------------------------------
bgeier's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=12822

```
 0
5/13/2006 11:10:40 AM
```The data comes from all places then i dump it in Either
1) Revenue
2) Expenses
3) Net Position
So for an example i might have 10 business centres that will have
Revenue, Expenses & Net Position. I copy & paste each one of them to
the correct one above, & what you see is ALL Business Centres in
1)Revenue
2) Expenses
3) Net Positon
So now my question is the Analysis side of it. What i'd like to do is:
A) FOR EXAMPLE THE TOP 6, in Revenue, Expenses & Net Position i'd like
to now who they are?
B) I'd also like to now the Top 6 again for Revenue, Expenses, & Net
Position comparing 2004 year to 2005 year. I'd like this to show the %
incease between the 2 years. This will only be Actuals.
c) Also again the top 6 comparing Actuals Vs Budget the Top 6 for
Revenue, Expenses & Net Position. This will be for the 2 years combined
i think that makes sense. It will show what business centre has over
budget or under budgeted for whatever the top 6 product is.
I really don't now what else or what type of analysis i can do with
this data am really new to this would love some help, not only in the
above thou also in what way i can sort the data out so i can do the
above analysis simple & easier as i will be doing this every month.
Anyway just like to say thank you for taking the time to reply much
appreciate it.
Take care
Will

--
william4444
------------------------------------------------------------------------
william4444's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33991

```
 0
5/15/2006 3:51:54 AM
```The first thing I would do is find a way to have Excel automatically
import the data (database, another workbook, etc.).  This can be tricky
depending on the source, but is not really all that difficult
(especially if it is from another workbook).

Next, decide how you want/need to use the data.  This will make
defining what you need to do much simpler, and in the process make your
analysis easier because you already have an idea of what the finished
"report" will look like.

It is difficult to determine your needs from your description and by
confidentiality and reluctance to "publish" such).

What you want to do is doable without much effort once you fully define
what you want, how you want it, and what you are going to do with it.

Please feel free to contact me, through this forum, or through private
e-mail (see my profile). If I can be of any further assistance.

--
bgeier
------------------------------------------------------------------------
bgeier's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=12822

```
 0
5/15/2006 8:01:39 AM

Similar Artilces:

SQL in Excel data
Hi all, Is there a possibility/way to run an SQL query in an excel data sheet? I have quite some data like the sample below, now i would like to have the sum of spending for each person. Like it is possible in Access. A1 B1 Field1 Field2 Chuck 12,89 Mike 23,09 Jean 9,34 Chuck 30,00 Mike 3,80 Chuck 22,00 Mike 7,23 Jean 10,55 Jean 10,75 Jean 31,45 Chuck 19,99 Result Field1 SumOfField2 Chuck 84,88 Jean 62,09 Mike 34,12 Advice would be appriciated. Cheers, Ludovic Hi You could use a formula like this ...

DPM forces a Consistency Check on data
Hi all, I have a DPM 2007 SP1 server in one office ? and are trying to backup data in another office, connected by T1 (1.54Mbps) link. It works, but every time there is an issue ? DPM forces a Consistency Check on XGB( >100) of data, which takes 5-7 days to complete. can any tell abt this. thanks in advance gopal Submitted via EggHeadCafe - Software Developer Portal of Choice Useful ASP.NET Exception Engine http://www.eggheadcafe.com/tutorials/aspnet/52012346-36db-4a47-ac61-8b9c9b4ce9a0/useful-aspnet-exception.aspx Hi Gopal, Can you please eloborate on "Ev...

How to get XML data out of an XML file
I am trying to retrieve the Parameters first or second (0, 1 ,2) node from the following XML file: <?xml version="1.0" encoding="utf-8" ?> <Robot xmlns="http://tempuri.org/RobotDefaults.xsd"> <Parameters> <Name>Decker</Name> </Parameters> <Parameters> <Name>A</Name> </Parameters> <Parameters> <Name>B</Name> </Parameters> </Robot> There will be more data than just a name for each Parameters node. Here is my code: Dim node As XmlNode = xmlDocument.SelectSingleNode("/P...

Erase data, preserve formula's
I have a an excel file with 12 worksheets for the financial year and an additional worksheet for yearly totals. I need to get a blank copy of this and was wondering if anyone knew a way to delete all the user inputted data while keeping the formatting and formula's intact. Any help is much appreciated. -- urbanfox ------------------------------------------------------------------------ urbanfox's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=22826 View this thread: http://www.excelforum.com/showthread.php?threadid=519004 Hi Hit F5 and select Special a...

Locking cell color while allowing data changes in cell
In excel 2000, I created an attendance worksheet for my classes.(Alphabetized names down left vertical column. Dates across top of horizontal row.) I added a different color to all cells in every other row to make for easier reading of each student's name and absences. Every other row stays with a white background. My question: I wondered if it was possible to lock row colors while allowing data to change on top of them. If a new student is added to my class in alphabetical order, the alternating color pattern is often lost. It is a pain to rechange row and cell colors. Any shortcut ...

How refresh imported data automatically?
In Excel, I've imported data into a worksheet. I know I can click Data.. Refresh Data to requery the source, but I want to be able to do it in a more automated way. Is there some sort of macro I can write when opened or something? Thanks, Ron Ron, Select the cell the data starts in, select Data/Import External Data/Data Range Properties Check the box "Refresh on file open" and anything else that needs checking off... Other than that, you could record a new Macro that selects your cell, refreshes data, and assign the macro to a keystroke, or even an icon. Beege ...

I have a spreadsheet with five columns that I enter data to. I then have a blank row at the bottom of these columns. Below the blank row I have several formulas pertaining to each row. How do I add more data to the columns and have the formulas adjust for these new rows without highlighting rows and using the insert rows command to make room (empty rows) where I can then add the additional data to the columns. Is there a formula that would always leave one empty row even when new data is entered in the columns? Thanks Put the formulas at the top of the columns. You can even use a Freeze ...

So, this is probably really easy, but I just want to ask and see if I may be missing something here. Some data on vendor numbers changed. Let’s say IBM used to have a vendor number 12345 and now it’s vendor number is 56789. I can identify IBM as IBM, but I really want to use the number, not the name. Should I set up a table that ties the numbers together, so that Access knows 12345 = 56789? Or, should I do some kind of Update Query and change all incidences of 12345 to be 56789? Or, is there some other, method, like a ‘best practices for missing data’? Thanks! Ryan--- ...

Outlook Data Files #4
I've done some reading but I'm still confused about the use of Office Outlook Personal Folders File (.pst) versus Outlook 97-2002 Personal Folders File (.pst). I understand the basic "Office Outlook Personal Folders File (.pst) to create a new Outlook 2003 .pst file. Click Outlook 97-2002 Personal Folders File (.pst) to create a new Outlook .pst file that is compatible with earlier versions of Outlook." When we have upgraded to Outlook 2003 or set up a new PC with Outlook 2003, when adding PST's you can chosse either of the two file type options and browse to chos...

Pivot table and organizing data
This one is really making me scratch my head. Here is the story. I have a list of information which I am pulling in via a query from SQL. Data is good and it correctly comes into Excel (03 or 07). I have five columns with data: Date, Time, AccountID , Status. First two are self-explanatory; third is a 3-letter ID, forth is a status (pass/fail). Ok, now that you have an idea, here is what I need: 1. List the account IDs as a column 2. List the dates as rows 3. Place the alert into the location that corresponds to the appropriate data and account I know this 'sounds' like a strai...

Copy data from one record to a new record on a sub form
I have a form showing patient details with a sub form displaying all associated referral details for the patient. One patient can have more than one referral and I would like to be able to copy data from some of the fields on the current referral when adding a new referral for the same patient. Is this possible? Thank you for your help I am fairly new to Access code and I am having some problem following your suggestion could you please simplify. Also some of the fields on the referral records are selected via a combo box will these fields still copy? "Klatuu" wrote: > ...

Prevent Hidden Column data from being copied/pasted?
A student came up with a question that I haven't been able to figure out yet in a recent Excel class. They are hiding a column and protecting the worksheet in the correct manner. They want to allow some users to access and enter information in some cells. They do not want the users to be able to copy and paste the information from the hidden column. The question is how can this be prevented? For example, Column B is hidden. When they copy a range such as A1:C10 and paste it to another worksheet, they are getting the "hidden" data in Column B in B1:B10. Any suggestions wou...

New Named Range Created Each Time Data Imported into Excel via Macro
I have noticed that each time I import data into an Excel spreadsheet via a macro, a new named range (for the same range) is created. This does not pose a problem, but after a while, I'll have a huge number of named ranges that will never be used. Why does Excel name the range and how can I stop this? I noticed in the recorded macro, there was a line .Name = "drd_5". I commented this out to see what would happen, but it just renamed the range "ExternalData_5". The next one was "ExternalData_6", etc. Thanks, Carroll Rinehart You could refresh with differen...

How do I break a large word document into smaller components?
I have a very large Word document (over 40 MB) that I update several times a year. I would like to break it into smaller components so that only the affected component would have to be backed up online. However, I still wish to maintain the ability to create a table of contents and index for the whole document. Is this possible and if so, what is the process to do it. I am using MS Office 2010 beta running MS Windows 7 Home Premium on a 64 bit Dell desktop. Hi RoadRoy, You can simply make multiple copies and delete whatever you don't want from each copy. For the mul...

Find what control is using a data item
I built a form, then deleted 2 columns from the source table and now I get a popup asking for the value of those 2 columns. The problem is, I don't use those columns so I need to find what on the form is refferencing the deleted columns. I have looked at the control drop down on the properties window and the tab order window and can't find a control with either one of the missing column names. How do I determine what is trying to refference the deleted columns? I found the problem, the column was still referenced in the underlying query that fed the form. "MeSteve"...

Data Validation Greyed out #2
The spreadsheet is not protected -- jcm122 ----------------------------------------------------------------------- jcm1229's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1484 View this thread: http://www.excelforum.com/showthread.php?threadid=26479 Do you have more than one worksheet selected? (Look in the title bar for [Group]. If you see it, rightclick on any worksheet tab in the grouped sheets and select Ungroup Sheets. jcm1229 wrote: > > The spreadsheet is not protected. > > -- > jcm1229 > ------------------------------------------...

Protecting user from changing previous data
Hi All, I have a block of data A1:D10 with Protection using ALLOW USER TO EDIT RANGE. The user enter data with the following order: Input 1: A1, B1, C1, D1 Input 2: A2, B2, C2, D2 Input 3: A3, B3, C3, D3 I would like to Automatically prevent the user from changing ROW A1-D1 when Inserting NEW ROWS (Row A2-D2, Row A3-D3). Thank you. This can be a very complex issue if you don't think things out really well ahead of time. What happens if, after they've typed data in A1:D1 and while entering more data in rows below row 1, they realize they made a mistake...

Split single cell data into multiple colums
i have a huge data, converted from image to Excel. After converting the image to Excel all the data are stored in one cell( A1). But i want that to be split into different colums Example A1 September 15,2006 Name Email ID Father's Name Address City State Pincode Phone1 Phone2 Contact Time Area Code City Code State Code Amount Discount Total Occupation Emp no Remarks The above headers are the data in one single cell, but have too many spaces between each word. Help me get this resolved -- Thanks and...

Large distribution lists
Hi all, Is there a way to have a distribution list send a message to each user individually instead of having all the emails stuffed into the "To" field? We are a federation and we send emails to our members regularly via a distribution group and there are a lot of bounces saying: #5.6.0 smtp;552 5.6.0 Headers too large (32768 max)> I tried splitting the distribution list by regions, but some regions still contain too many email addresses. Do I have to look at a third-party application just to do that? TIA for any suggestions/ideas! Mail merge in Word. "Eric Ca...

merging data
Hi all:) I'm new to this forum and to to Excel (2000) and not sure that I'm asking this question correctly but here is what I'm trying to do. The data on my work sheet is pasted from another sorce and includes information: name, ref #, time, date. I use this information looking up the status of an order and enter a "status note" in 5th (last) column. As the day progresses, the source that the data comes from is copied and pasted under the current list I already have going and much of the data on the lower list is a duplicate. How can I merge the to list so that I hav...

Accessing Outlook data from database or programatically from ASP.NET
Hi all! I'm not sure whether I post this in the correct group but nevertheless, here is my problem: I would like to manipulate Outlook data (tasks, contacts, calendar, etc.) programatically from an ASP.NET apllication, more precisely I would like to be able to add and change contacts and enter events to calendar in the Outlook from a web application. Now, the logical way seemed to be to link from an MS Access database to objects within Outlook and then to access those tables from within my ASP.NET code. However, I've noticed something strange: for example, the linked table &qu...

Error: no data exchange control with ID
I have a CView, and trying to create a dialog for user input. Unfortunately, I am getting the message & assert Error: no data exchange control with ID 110 Here is my code class CDlgGoto : public CDialog { // Construction public: CDlgGoto(CWnd* pParent = NULL); // standard constructor virtual ~CDlgGoto(); CString m_userGoto;// the number as input by the user //HWND m_DlgGoto; int m_iGoto;// goto line virtual int DoModal(); // Dialog Data //{{AFX_DATA(CDlgmsgqguiGoto) enum { IDD = IDD_DIALOG_GOTO };//110 // NOTE: the ClassWizard will add data members here //}}AFX_DATA // ...

auto fill in data when changing fields
i new to crm 4 and i would like to; once i selected the account i need it to grab the main phone and put it in the phone field, and could you tell me where i need to do this, thanks -- j.hardy you can modify the mappings of the corresponding relationship "moon" wrote: > i new to crm 4 and i would like to; once i selected the account i need it to > grab the main phone and put it in the phone field, and could you tell me > where i need to do this, thanks > -- > j.hardy ...

How can I make data entry easier?
I am creating a catalogue of information resources and need to classify each according to the subject(s) it covers. I have created two tables, tblResources and tblSubjects, and a table that joins them. I can easily create a form for the resources with a subform to list the subjects with a drop-down box from which to pick. However, with a very long list of subjects, it's a bit unwieldy. I know that the people who will be undertaking the classification would love check boxes on a form they can tick. Is there a simple way of setting this up for them? Would I have to writ...

Documenting the backup of data
This may not be the place to ask however i am looking for resources on how i may document data I am backing up from an oracle database to MS Access. Basically I have my oracle database and i have an Acess database. I have to back up 3 tables from oracle into access. I have no idea how i would document this. I want to say Columns 1, column 2 from table A in oracle will be backed up to Column 1a and Column 1b in Access. Has anyone had similar exeperiences? Does anyone know of any resources on the net or example? ...