1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: EXCEL - Consolidate data from multiple sheets

Discussion in 'Business Applications' started by maxpowerdiaz, Oct 1, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. maxpowerdiaz

    maxpowerdiaz Thread Starter

    Joined:
    Aug 21, 2012
    Messages:
    43
    I have an issue I have not been able to find an answer to, so I hope someone can help. I have a workbook, where there are many worksheets that i would like to consolidate into one sheet. The problem is I'm dealing with a lot of data. Each worksheet represents a day of the month, so there are 30-31 worksheets for each workbook.

    Ultimately what i'm looking to acheive is to some how find a way to compile the date from all worksheets so i can do a quick search and pull from all the data from all the worksheets and see the results in one place. The search results would often find many results (hundreds even), and i still need to see all the data from those results from that row. I like the idea of using a pivot table, but once you use it for multiple sheets i cant get it to show the actual data.

    I hope I have been clear with what i'm trying to do, if not, please let me know. Thanks for any help you can provide.
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi, your explanation does lift a very small tip of the veil covering the whole scenario.
    Without seeing any data and or knowing how it is written in the sheets I would suggest a macro that will pull all the data from the different sheets and compile a mastar data sheet.
    As fas as I understand you have one file for each month of a year and each file contains 30-31 or even 28-29 (February) number of sheets and one main sheet where you want all the data for that particular year-Month, correct?

    I suggest a small sample sheet for one month with some dummy data.
    Create the sheet as you would like it to be created and I, or one of the other feloow members will surely pick this up and come up with a practical and workable solution for you.
    I do suggest you tell us what version of Excel you're using, that comes in handy when writing macro's, etc.
     
  3. maxpowerdiaz

    maxpowerdiaz Thread Starter

    Joined:
    Aug 21, 2012
    Messages:
    43
    Thanks for your reply Keebellah,

    You are correct about my needs, and here is a sample sheet to give you a small idea of the data i have. There are only 3 tabs on this book, but like we established it would normally be 30-31 tabs (28-29 for Feb.). And normally there would be around 100 items in each tab (but there are only 4 in this sample sheet).

    What i need to accomplish is Finding all the data for a specific agent for that month. I want to be able to see all the date in the row that has the searched agent. For example, I want to search John smith, i want to compile each row that has "John Smith" as the agent. It would need to show me the everything from that row (account number, serial number, address, reference number, date, debit/credit, status).

    Also, I am using Excel 2007.
     

    Attached Files:

  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi,
    I'll take a look, is there any particular reason why you're saving the file in a 2003 version (xls instead of xlsx?)
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I've attached a sample file based upon your three months.
    I added a sheet named September 2012 and I suggest, before you do anything else that you add another 27 sheets to complete the 30 days for September, with or without data
    Once you have done that then you can press the button Upadte Agent List which will create a list of unique agent names taken from the 30 sheets
    A pick-list will be cretade in cell B2 where you can select an Agent, and once selected all the records for that agent will be shown.

    If you select another agent the list gets appended with the new name.
    No check for duplicate selection by the user (you) and no question asked if you wisdh to delete the previous list, all this is editable.

    Just try and tell me if this is the idea, maybe more than you expected but with some tailoring and another approach you will be able to use it for all your files using a master file, but thats of later worry.

    I use Office 2010 but tha's no problem, and the same macro's will run if the file is saved as a 2003 version.

    DO NOT FORGET TO ENABLE MACROS
     

    Attached Files:

  6. maxpowerdiaz

    maxpowerdiaz Thread Starter

    Joined:
    Aug 21, 2012
    Messages:
    43
    This is EXACTLY what i was looking for! You did a terrific job.

    If i may be so bold to ask another question. You did exactly what i wanted and asked, but to go a little further, is there anyway the tab number can be added to each row that it was pulled from so that i can Know the date? For example: Add a column in front of the "Agent" column, such as "Tab" and have it input the tab number that row was pulled from?

    Thank you!!!
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Everything is possible :)
    You'll still have a jog to implement it accross all variations
    I'll see if I can get to it this evening (no promises) and post it.

    It's really quite simple though
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Okay, added a column in the September sheet named Tab
     

    Attached Files:

  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I noticed that when using Excel 2007 and / or 2010 Excel sometimes shows the message "not responding" and if you hava a counter in the statusbar it stops too but the calculation continues, it seems like the process waits for some time and the goes on in small steps.

    Simplifying the code does not always solve this.
    Patience is the essence. I tink that only somebody really into the 'thinking' process behind Excel will know a reason why, I don't I just take it as it comes
    Maybe changing some code around could make it 'easier' but I honestly don't know.
     
  10. maxpowerdiaz

    maxpowerdiaz Thread Starter

    Joined:
    Aug 21, 2012
    Messages:
    43
    Keebellah - Thanks so much for your work on this. I couldnt have asked for more!
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1071017

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice