+ Reply to Thread
Results 1 to 4 of 4

Create unique list from multiple sheets with variable

  1. #1
    Forum Contributor
    Join Date
    02-24-2010
    Location
    BC, Canada
    MS-Off Ver
    Excel 2010
    Posts
    174

    Thumbs up Create unique list from multiple sheets with variable

    I have sheets A through Z in a workbook with lists of employee IDs and other data. I want to be able to pull the data from those sheets onto another sheet to compile a master list. Sheets A through Z represent pay periods so the list stays similar each pay, it just changes based on who quits or gets hired.

    The catch is that I need two lists, one of them with all the employees from every sheet EXCEPT for those who have the value "25" in column E, and the other list will be made up of ONLY those with "25" in column E. The example I'm attaching should make it clearer. The sheets "Main" and "Lib" are the sheets that need the lists and there is an explanation on the sheets for anyone who can help. This goes beyond my expertise.

    Thanks!!
    Attached Files Attached Files

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Create unique list from multiple sheets with variable

    You're working from the wrong direction, in my opinion. I would maintain the MAIN and LIB sheets as your database. Whatever it is that makes you think you need 26 other smaller versions of that main listing, you could simply apply an AUTOFILTER to the Main sheet to see the sub groups.

    My two cents.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Forum Contributor
    Join Date
    02-24-2010
    Location
    BC, Canada
    MS-Off Ver
    Excel 2010
    Posts
    174

    Re: Create unique list from multiple sheets with variable

    Quote Originally Posted by JBeaucaire View Post
    You're working from the wrong direction, in my opinion. I would maintain the MAIN and LIB sheets as your database. Whatever it is that makes you think you need 26 other smaller versions of that main listing, you could simply apply an AUTOFILTER to the Main sheet to see the sub groups.

    My two cents.
    I'm open to other suggestions. Basically what's going to happen is our payroll system is going to spit out a report with 5 columns of data. I am trying to make this simple for the computer illiterate that I work with so my idea was to just have them paste the data from payroll A into the "A" sheet, and have it autofill the main and lib sheet.

    If I could create the main and lib databases as a whole I would, but I can't see a way for our software to support that. So I'm using excel to take the payroll info one pay period at a time and then I can create the database that I need, which would be the results of "Main" and "Lib".

    There are other reporting functions that I plan on adding to this sheet and I think it will serve my purposes well, I just don't know how to generate that list. However, like I said I'm open to suggestions for completely reworking my approach. If I understand your suggestion correctly then the problem I see with that is that I don't have the MAIN and LIB databases to begin with, I only have the 26 smaller ones. And I only get a new one every two weeks so we update the main one as we go.

    Thanks!

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Create unique list from multiple sheets with variable

    And if it were I, I would add the incoming data into the one MAIN sheet.

    With all the data in a single growing database, it is a simple matter to run a PivotTable, summation formulas, even AutoFilter the data as a whole in one place.

    The only difference between your MAIN and LIB sheets is the column E value, so in a single database sheet, you could AutoFilter the data on column E to see the two sets of data (including printing, editing, reporting, etc)... without having to maintain the plumbing of two separate sheets.... much less the additional 26 sheets.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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