none
Merge multiple excel files(Only required columns) into one excel RRS feed

  • Question

  • There are around 20-30 excel files with more than 40 columns and I need data from only 5 columns(Column heading: First Name,Last Name, Class, Section,Region) which are not specifically at same position.

    Request for VBA script to run above requirement.

    Thank you in-advance.

    Regards,

    Kartik 

    Thursday, November 1, 2018 3:35 AM

All replies

  • Hi Kartikdesai,

    Are the columns in each Excel different? Do you want to put 5 columns of data to a new worksheet?

    Best Regards,
    Bruce

    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Thursday, November 1, 2018 9:04 AM
    Moderator
  • Excel 2010 to 2019 Power Query.
    Example with 4 files, 10 columns each and 5 selected columns.
    No formulas, no vintage VBA macro.
    http://www.mediafire.com/file/ujwss851kf6zfrm/11_01_18.xlsx/file
    http://www.mediafire.com/file/as73eugt1aiq2u9/11_01_18.pdf/file


    Thursday, November 1, 2018 3:28 PM
  • Thank you for reply.

    Columns are located in different positions but the headings are same (One example below)

    First Exel : Col A :: First Name, Col B:: Last Name, Col C:: Class, Col D:: Section, Col E::Region

    Second Excel : Col C:: First Name, Col E:: Last Name, Col D:: Class, Col B: Section, Col A:: Region

    Similar way positions is not same each excel.

    Yes, I preferring to be stored in New Workbook completely.

    Regards,

    Kartik Desai

    Friday, November 2, 2018 5:40 AM
  • Hi kartikdesai,

    You can put Excel file names into a single column in a worksheet, then loop the column to read all Excel files.

    You can determine which Excel file to read by using loop to go through all the files.  For Loops will read each column of first row of all files.

    For example, the first line of this column of data is “Class”, putting all the data after this column into a "Class" column in another workbook. 

    Until all Excel files are finished looping.


    Best Regards,

    Bruce

    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.


    Tuesday, November 6, 2018 7:46 AM
    Moderator