Combining Multiple Excel Files into One Using RDBMerge
When you need to consolidate specific data from dozens of Excel files into a single dataset, manually opening and copying from each file (Ctrl + C, Ctrl + V) not only takes a lot of time but also increases the risk of data errors due to momentary lapses in concentration. To address these issues, I introduce a feature that allows you to merge numerous Excel files into one seamlessly.
1. Install the program; ‘RDBMerge’ into the Excel file.
download
http://www.rondebruin.nl/win/addins/rdbmerge.htm
Visit the website mentioned above and download ‘RDBMerge’ to your desktop. After downloading the program, install it in your Excel file. I’ll explain this based on Excel 2016.
File -> Option
The following options window appears:
Here, click on Add-Ins.
Then, the window shown above appears. At the bottommost dropdown menu, select Excel Add-Ins, and click Go.
Open Browse, select the ‘RDBMerge’ program located on your desktop, check the Add-Ins available option, and click OK. You should then see ‘RDBMerge Add-Ins’ installed at the far right of the Data tab in your Excel file.
2. Integrating Data.
Create a folder on your desktop where you will save the consolidated data. I will create a folder named ‘consolidate’ on the desktop, and then save the data to that folder
For example, I have three files named ‘Plot No_1,’ ‘Plot No_2,’ and ‘Plot No_3.’ Let’s assume the data inside each file is as follows:
The data format consists of four columns in each of them.
Be careful!! When consolidating Excel files, ensure that the number of columns is the same.
I would like to consolidate this data from the three different files into a single Excel file. While copying and pasting with Ctrl + C and Ctrl + V may be easy for just three files, it would become a very laborious and inefficient method if there were 300 files.
Now, let’s use the RDBMerge program to consolidate them into one Excel file. Open a new Excel file and run the ‘RDBMerge Add-Ins.’ This will bring up the following window.
Select browser and specify the folder where the files to be consolidated are located. In my case, I created a ‘consolidate’ folder on the desktop and placed the files there, so I selected that folder.
The data to be consolidated is on the first sheet, so I chose ‘1,’ and the first cell where the merged data will start is A1. This value is set as the default, so you don’t have to change it if you don’t want to. Later, as you become more familiar with the program, you can adjust it to match your specific data situation.
Now, let’s click ‘Merge.
Great!!! The data has been consolidated as shown in the image above. This is a powerful feature that makes it easy to merge data from numerous Excel files.
Is there a way to combine data through another program without manually merging data in Excel? While there are various methods available, I’ll introduce using MS Access and R.
□ Combining Multiple Excel Files into One Using Access
□ How to Upload and Combine Multiple Files In R?
