+ Reply to Thread
Results 1 to 2 of 2

Combining Data advice

  1. #1
    Forum Contributor
    Join Date
    05-28-2016
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    106

    Combining Data advice

    I have to monitor staff attendance at mandatory training. There are 17 facilities that record the same data into spreadsheets, that is, Date of attendance & staff name - recorded in a table.
    At the moment there is one table for each facility as different people enter the data.

    I have looked into power queries, pivot tables etc but need some guidance on the best method to combine the data for reporting.

    My aim is to minimise error during data entry and to be able to report on training numbers by date. Similar to a pivot chart and a slicer, I would love to be able to filter just by date range and have each facility numbers displayed in a table. The problem I have is that I'm unable to combine multiple tables which filter via one "slicer".

    How can I dynamically combine all tables and filter via date ranges? Any help will be greatly appreciated. Thanks.

  2. #2
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,443

    Re: Combining Data advice

    If there are only 17 locations and presumably fewer than 100 individuals at each location, I'd advise adhering to the KISS Principle.

    I assume column labels would be used in row 1 to indicate what each column contains.

    Have the 17 different locations e-mail you their separate spreadsheets, then you create a new spreadsheet for all locations combined. In column A of this new spreadsheet enter the location ID for the 1st location. Open that location's separate spreadsheet, copy its contents, switch back to your new spreadsheet, paste the data beginning in column B. Close the 1st location's workbook. Go down to the last row copied into the new spreadsheet, move to the row below it, move to column A, enter the next location's ID. Open the next location's separate spreadsheet, copy its contents, switch back to your new spreadsheet, past the data beginning in column B. Close the 2nd location's workbook. Repeat this process for all 17 locations. That should take less than 5 minutes.

    If each location used the same layout for recording attendance, and if you fill the location IDs in column A in the new spreadsheet down into blank cells to just above the ID for the next location, you should wind up with a table including location ID in column A and all data recorded from each location in column B and right. Apply an autofilter to the entire range in the new spreadsheet, and filter whatever you want.

    If you absolutely MUST automate this, then use 2 worksheets in the new spreadsheet/workbook. The 1st worksheet would have 2 columns: 1st column would contain location ID you'd want to use in a table including attendance data from all locations, and 2nd column would contain filenames for each location's workbook stored in the working directory. Then use a macro to iterate through that table performing the location entry, file open, data copy+paste, and file close operations detailed above.

    I may just be hopelessly jaded, but 17 locations should be child's play to consolidate into a single combined workbook.

+ 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. Replies: 8
    Last Post: 08-06-2017, 07:45 AM
  2. Replies: 8
    Last Post: 02-09-2015, 09:45 PM
  3. 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
  4. Replies: 2
    Last Post: 11-30-2012, 06:59 PM
  5. Advice for Web data extraction
    By gordonf35 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 08-21-2012, 01:48 PM
  6. Need advice on how to extract data.
    By Cifi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-11-2011, 09:46 AM
  7. 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