Merge multiple Excel Workbooks in one Excel sheet

Question

Dear Community members,

i need help for Merge multiple Excel workbooks in one Excel sheet as like below snap showing.

in the snap have multiple workbooks there is highlighted columns ( A & B ) just i need to that columns and put in culmn (A) the name of FR which showing in example.

if FR column is put in (C) column will be also ok.

Thanks and Regards,

ABAS

Answer 1

Multiple ways. Is this a one time operation, or something you'll have to repeat.

.

PowerQuery is a good way to go if you have to repeat an action. It is the "modern" version of VBA.

Merge excel files into a single excel spreadsheet 2017 04 07In a continuation Excel VBA tutorial series of data manipulation using Macros; Today, we will try to merge excel files in a single excel spreadsheet. The source of the data may be from the same worksheet or from the closed workbook. We will merge excel files into a single sheet. For example, individual states data...
.

! Introduction To Excel Power Query Neale Blackwood      2021 05 13                 Neale Blackwook
https://player.vimeo.com/external/548711986.hd.mp4?s=bba6fc7e089a701fb8d9a50ba92c7a9c942b68be&profile_id=175&download=1
https://a4accounting.com.au/wp-content/uploads/2021/04/Materials_PQ_2021.zip
e pq- Introduction To Excel Power Query Neale Blackwood 2021 05 13.mp4  Downloaded
e pq- Introduction To Excel Power Query 2021 05 13 Neale Blackwood Materials.zip (downloaded)
.
Getting data into Excel and in the correct layout and format is the important starting point for any Excel project.
Power Query can automate the importation process. It is now a vital skill to possess.
This is a video of a free live training session I ran in May 2021 – over 100 people attended – please share this resource with friends and colleagues. No editing has been done to the video.
Use the button below to download the pdf manual and example files so you can follow along.
We need to get the word out on this powerful Excel feature.
.
Power Query and Multiple Fileshttps://player.vimeo.com/external/551778588.hd.mp4?s=1fffc5e510eab63db7378ced324881ea4ead2e26&profile_id=175&download=1
https://a4accounting.com.au/wp-content/uploads/2021/04/Materials-PQ-Multi-2021.zip
e pq Power Query and Multiple Files Neale Blackwood 2021 05 17.mp4  (downloaded)
e pq Power Query and Multiple Files Neale Blackwood Materials 2021 05 17.zip (downloaded)
.
Power Query allows you to automatically perform data cleansing routines on your data sources - no manual intervention required. Simply refresh and your data is ready to use. You can use csv files; txt files; databases and existing Excel tables as your data sources.
Building on the skills covered in the Introduction session, we will start working with multiple files. For example you may have 12 separate CSV files in a folder. All with the same layout, one for each month of the year. Power Query can import all 12 files as if they were a single file and create a table for the whole year
This session covers
.  • importing multiple CSV files from a folder
.  • a technique to capture the file name in a field (column)
.  • importing multiple Excel files
.  • merging data from multiple tables
.  • Extracting header information into a column from multiple CSV files
.

! Getting data from the latest file in a folder using Power Query            2018 06 13              Wojciech Krzywiec
https://wkrzywiec.medium.com/getting-data-from-the-latest-file-in-a-folder-using-power-query-51dfa4bff711
One of the most common tasks of Power Query is to set up automated data refresh from multiple sources. One of them are folders, which retrieve basic information about files (e.g. create date, extension, size, etc.), but also allows to enter one or more files, which is very powerful feature. On a daily basis I download/receive similar reports for processing so to avoid reoccuring tasks (copy/past, filtering and transforming data) I use Power Query to get the latest file from the folder and prepare it for me for further analysis. .  1. Create “Get Data from Folder” query .  2. Extract the latest file from the folder .  3. Enter the latest file from Query Editor.
Passing source folder path as parameter to query code in Power Query                    2018 06 13    Wojciech Krzywiec
https://wkrzywiec.medium.com/passing-source-folder-path-as-parameter-to-query-code-in-power-query-19ec60797d94
During Power Query report creation you probably face the problem that you need to change data source file directory. It requires to go to the editor and manually, change static value of the source directory. Usually this approach will works fine, but it could be time consuming and error prone. So to overcome it, I externalize path to a file from query code into Excel table, which makes changing path directory more simple. .  1. Prepare standard query table .  2. Create table in working Excel file .  3. Create custom function to load folder path from config table .  4. Create intermediate query table to load folder content .  5. Replace static data source path with paths from dynamic table.

Easiest way to COMBINE Multiple Excel Files into ONE (Append data from Folder)   2020 05 21    Leila Hharani
https://www.youtube.com/watch?v=fHFUh6EhBcw&t=388s
This is the easiest way to combine multiple Excel files from a folder into one Workbook and create a table or a a pivot table report based on the consolidated data. Many of us send out templates to colleagues and are later faced with the challenge to somehow consolidate multiple Excel files. To append the data into a single Excel workbook can be quite a troublesome and lengthy process.
Sure, a solution for this has always been Excel VBA. But learning VBA takes time and it can be too difficult for a lot Excel users.
In this tutorial, I'll show you a much easier approach! We'll use Excel Get and Transform aka Power Query to combine data from multiple Excel workbooks. We will directly connect to a folder that contains the files and use the feature Combine Files in Power Query. Any time you drop an Excel file in that folder, the data will be appended and consolidated with the rest. You can then create one Excel table for the appended data or create a pivot table from the multiple Excel files.
.

Answer 2

Hi ABASKHAN,

In your workbook, right click on the letter A, then select Insert:

copy the text by right clicking on the cell it is in and select copy

then go to the blank cell you are going to paste what you copied then press Ctrl and keep holding it then press V to paste what you copied.

then pointing at the lower corner of the cell

until you see a + sign:

double click that and it should copy all the way down the bottom