+ Reply to Thread
Results 1 to 6 of 6

Extracting Data from Multiple Files into a Single Place

  1. #1
    Registered User
    Join Date
    08-08-2019
    Location
    UK
    MS-Off Ver
    MS Office Professional Plus 16
    Posts
    6

    Unhappy Extracting Data from Multiple Files into a Single Place

    I am currently trying to set up a spreadsheet which will draw in data from several files (in several folders) into one ‘master’ sheet.
    I have drawn the filenames for the data spreadsheets into the master sheet, as below (Fig.1), using the INDEX function.
    Fig.1
    1.jpg
    I want the end product to look like this (Fig.2):
    Fig.2
    2.jpg
    In each file, the data for each individual column is in a consistent cell number, and in a consistent format. I have been able to draw in the data by manually typing the filename (e.g. ='[AB001-001.xlsx]Sheet1!$A$1), however, I haven’t been able to link the formula to the filename in the left-hand column.
    Is there a way to make this work, or possibly an alternative route which could work better?
    I looked into setting up a Query which drew in the data into the ‘master’ spreadsheet well, however returned the data in multiple rows for a single filename as below (Fig.3).
    Fig.3
    3.jpg
    I then couldn’t work out how to draw the data from Fig.3 into the format shown in Fig.2.
    If anyone can help or if more details are needed, please let me know!

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Extracting Data from Multiple Files into a Single Place

    Hello BenMWilliamson and Welcome to Excel Forum.
    Perhaps that following, as modeled in the attached file, will help.
    1. The Filename column is populated using: =IFERROR(IF(MATCH(0,INDEX(COUNTIF(I$1:I1,A$2:A$20),,),)>COUNTA(A$2:A$20),"",INDEX(A$2:A$20,MATCH(0,INDEX(COUNTIF(I$1:I1,A$2:A$20),,),))),"")
    2. The Customer and Quantity columns are populated using: =IF($I2="","",INDEX(C$2:C$20,AGGREGATE(15,6,(ROW($A$2:$A$20)-ROW($A$1))/($A$2:$A$20=$I2)/($B$2:$B$20=J$1),1)))
    3. The Product column is populated using: =IF($I2="","",INDEX(F$2:F$20,AGGREGATE(15,6,(ROW($A$2:$A$20)-ROW($A$1))/($A$2:$A$20=$I2)/($D$2:$D$20=L$1),1)))
    4. The Dimensions column is populated using: =IF($I2="","",INDEX(G$2:G$20,AGGREGATE(15,6,(ROW($A$2:$A$20)-ROW($A$1))/($A$2:$A$20=$I2)/($F$2:$F$20=M$1),1)))

    For future reference, you will usually get faster/better results if you will upload a sample file rather than screen shots.
    To upload a sample workbook click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.

    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    08-08-2019
    Location
    UK
    MS-Off Ver
    MS Office Professional Plus 16
    Posts
    6

    Re: Extracting Data from Multiple Files into a Single Place

    That's really useful, thank you! I think it should work, but will come back and let you know tomorrow. And noted the attachment advice - good to know for the future.

    If I were to have the data point below the category (see 'Type' in Col 8 in attachment), could you use the same functions? or must I re-configure the form so that they work horizontally?

    Thanks,
    BMW
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Extracting Data from Multiple Files into a Single Place

    Assuming that you want xyz in cell O2 and abc in cell O3 paste the following into cell O2 and double click the fill handle to copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  5. #5
    Registered User
    Join Date
    08-08-2019
    Location
    UK
    MS-Off Ver
    MS Office Professional Plus 16
    Posts
    6

    Re: Extracting Data from Multiple Files into a Single Place

    Right I see how you've done that. Thanks for your help, I should be able to re-format it for the spreadsheet I'm working on.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Extracting Data from Multiple Files into a Single Place

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ 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: 2
    Last Post: 10-29-2018, 01:48 PM
  2. [SOLVED] Pull data from the same cell across multiple csv files and place into a single excel file
    By rbmrrbmr in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-18-2013, 10:53 AM
  3. Replies: 0
    Last Post: 11-13-2012, 02:26 AM
  4. extracting data from cells in excel files and collect them into a single one
    By Nukles in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-22-2011, 06:11 AM
  5. Replies: 3
    Last Post: 11-05-2010, 03:06 PM
  6. Extracting data from multiple files
    By ctote in forum Excel General
    Replies: 1
    Last Post: 07-17-2008, 08:09 PM
  7. Replies: 0
    Last Post: 07-31-2006, 07:33 AM

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