+ Reply to Thread
Results 1 to 3 of 3

Advice on managing data from 150 tabs in Excel

  1. #1
    Registered User
    Join Date
    02-20-2019
    Location
    Sydney
    MS-Off Ver
    Office 2016
    Posts
    1

    Advice on managing data from 150 tabs in Excel

    Hi there, I am analyzing the debt of around 150 companies and really need some help/advice as I've hit a brick wall in terms of what to do next!

    I have created a spreadsheet that has a tab for each company, so 150 tabs. In each tab there is a row for each debt instrument a company has (some companies have up to 100 debt instruments). I have several columns that show various items including:
    The maturity date of the debt
    Percentage of overall company debt that this instrument represents
    The interest rate of that instrument
    What sector that particular company is from
    What country that particular company is from
    (these last two are the same all the way down the column in each tab and are there to label each debt instrument, so I can analyse debt across countries and sectors.)

    I'd like to consolidate the data so that I can create various matrices; e.g. countries on the X axis, Sectors on the Y axis, and have the average Interest rate of all the debt instruments in each combination of country and sector. Or a similar matrix with the average maturity instead of the average interest rate.

    I tried using AVERAGEIFS but it didn't like doing it across 150 tabs. The next idea was therefore to consolidate all the tabs into one tab so that I could then use AVERAGEIFS within one tab, but I couldn't work out how to efficiently consolidate all the tabs, into one tab with all the data below each other.

    I'm therefore looking for advice on where to turn next? Whether to manually link the data into a single tab , try and write a macro to do this, or is there something I'm missing!

    Any help would be very much appreciated!

    Thanks!

    Sonski

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    82,933

    Re: Advice on managing data from 150 tabs in Excel

    Welcome to the forum.

    You can consolidate all tabs using PowerQuery:

    https://docs.microsoft.com/en-us/pow...urrentworkbook
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Advice on managing data from 150 tabs in Excel

    Another option.

    VBA can help you add all data in 1 worksheet.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

+ 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. Managing PDF data with Excel
    By heyz123456 in forum Excel General
    Replies: 3
    Last Post: 01-08-2018, 12:47 PM
  2. VBA code advice/Excel advice for summing groups of numbers
    By paulblower in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-17-2014, 05:47 AM
  3. Managing Data on excel
    By S2000 in forum Excel General
    Replies: 17
    Last Post: 03-29-2014, 05:41 PM
  4. Managing Tabs in large Workbooks
    By jjock in forum Excel General
    Replies: 1
    Last Post: 09-24-2013, 12:01 AM
  5. Programming approach: managing external data with an Excel application
    By Alex Simmons in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-02-2013, 08:11 PM
  6. [SOLVED] Need advice on data processing formulas in Excel 2010
    By robinc1969 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-15-2013, 05:32 PM
  7. [SOLVED] Need advice : consolidating data from multiple CSV files in Excel - External data handling
    By Matthieu Gaillet in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-01-2005, 05:10 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