+ Reply to Thread
Results 1 to 6 of 6

Newbie: Auto Updating database Files ?

  1. #1
    Registered User
    Join Date
    04-10-2014
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    14

    Newbie: Auto Updating database Files ?

    Hello Experts. So I am kind of an average Matlab User, mostly used to command line stuff. I got a task in Excel and as a newbie I post my query here. In the attachment you find a example database of power units within a single European country. I would like cluster the power units per country based on
    1. unit type and in this cluster class
    a. find and deleted the units that stopped e.g in and before 2010.
    b. then the number of installed units,
    c. the sum of the unit capacities and
    c. the average of the unit efficiency.

    Manually I was able to do it on the same sheet but for a large database I prefer a solution in excel which is updating itself may be on another sheet, when a change in the database occurs.

    I shall be thankful for any comments.

    Best Regards
    Ali
    Attached Files Attached Files
    Last edited by luckyali; 04-23-2014 at 08:27 AM.

  2. #2
    Forum Contributor
    Join Date
    11-27-2009
    Location
    Austin, Texas
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    407

    Re: Newbie: Auto Updating database Files ?

    Luckyali,

    How does this look, a quick pivot table will give you the answers need!
    On sheet1, I have created a table which is linked to the pivot table, just add to the bottom and your data will refresh.
    Attached Files Attached Files
    Last edited by bigroo1958; 04-20-2014 at 09:01 PM.
    ==========
    Bigroo1958
    Austin, Texas
    ==========

  3. #3
    Registered User
    Join Date
    04-10-2014
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Newbie: Auto Updating database Files ?

    Hi Bigroo;
    Thanks for this helpful suggestion. that Sounds exactly what I need, I will read in detail about the pivot tables.
    what I get from your changes is that you first sorted by country name, then by unit type, then by unit build, name and retire. the Total Result shows the Total capacity of that unit.
    1. for now, can I also add e.g the count of gas units and average unit efficiency per gas_unit class to this pivot table and so on.
    2. can the same process be done to a database file containing more countries with the same fields and can be sorted out in the same manner.

  4. #4
    Registered User
    Join Date
    04-10-2014
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    14

    Thumbs up Re: Newbie: Auto Updating database Files ?

    Pivot Table, That's exactly what I needed, Thanks A lot dear.

    Have wonderful day

    Best Regards

    Quote Originally Posted by bigroo1958 View Post
    Luckyali,

    How does this look, a quick pivot table will give you the answers need!
    On sheet1, I have created a table which is linked to the pivot table, just add to the bottom and your data will refresh.

  5. #5
    Forum Contributor
    Join Date
    11-27-2009
    Location
    Austin, Texas
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    407

    Re: Newbie: Auto Updating database Files ?

    Hi Luckyali,

    Glad to help you!!
    If, this answers your question, please mark this question as solved!!

    Thanks!

  6. #6
    Registered User
    Join Date
    04-10-2014
    Location
    Germany
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Newbie: Auto Updating database Files ?

    HI bigroo, one more related question:

    in the pivot table as you have arranged selected things in separate columns. Now can I sort the year and capacity per range. e.g in year 1970-1990 and rang of capacity between 0-300 or something like this. I had a look on group elements and slicers but could not get them easily.. any good tip in this regards.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Auto Updating of 100+ Files from Data File on Monthly Basis
    By ats1312 in forum Excel Programming / VBA / Macros
    Replies: 31
    Last Post: 10-09-2013, 11:53 AM
  2. [SOLVED] Error when opening excel files with auto-updating list boxes
    By toxikon in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-18-2013, 01:49 PM
  3. Auto updating of Database in MS-excel 2007
    By Renildrah in forum Excel General
    Replies: 0
    Last Post: 08-04-2011, 03:31 AM
  4. Auto-updating access database?
    By UNWATCHABLE in forum Access Tables & Databases
    Replies: 0
    Last Post: 01-21-2010, 11:12 AM
  5. Database Newbie
    By G3£Wh\)zZ@ in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 10-01-2005, 02:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1