Combine multiple Excel files into one file

Asked By 0 points N/A Posted on -
qa-featured

I am having multiple Excel files.

I want them to compile into one Excel file.

Let's say for example, I do have region wise Client List along with their details maintained in different Excel sheet, I want to consolidate these regional files into one Excel file: 'Client Master List.'.

Kindly suggest the simplest way to do this.

SHARE
Best Answer by Sharath Reddy
Answered By 0 points N/A #125535

Combine multiple Excel files into one file

qa-featured

Hello

It’s crucial you know that 65,536 is the row limit to Microsoft Excel. Therefore it is impossible to store all the files in one Excel document if they exceed this limit. Otherwise if the data you have only consists of text columns and no graphs and fonts, the process isn’t that complicated.

Please follow this simple procedure that I have provided below:

1). Export every file you have into the CSV file format.

The CSV file format can be described as a file consisting of only texts and can be accessed by any text viewer. You can do this by choosing it in the format menu. It’s below the file name witch you can access by choosing the save as option.

2) Make use of the same solution to combine every text file to one another in order to create one file.

3) Open the file again using Excel then save it as a XLS file.

This saves the data in all kinds of binary representations that are supported by all spread sheet applications with text taking a small part.

NB: The limits to the rows only apply to the sheet and not the workbook. Also note that tough the limit has been increased in Microsoft Excel 2007, you will not be able to use that data on older Excel’s.

Here is another way around this which involves making use of a macro.

Private Sub ConcatenateAll()

Workbooks.Add
ChDir "C:"
ActiveWorkbook.SaveAs Filename: ="C:ConcatResults.xls"

CopyTargetBookmark = 1

For Each Workbook In Application.Workbooks
If Workbook.Name <> "ConcatResults.xls" And Workbook.Name <> "PERSONAL.XLS" Then
Workbook.Activate
Workbook.Worksheets(1).UsedRange.Copy
Workbooks("ConcatResults.xls").Activate
Range("A" & CopyTargetBookmark).Select
ActiveSheet.Paste
CopyTargetBookmark = CopyTargetBookmark + Workbook.Worksheets(1).UsedRange.Rows.Count
End If
Next Workbook

Execute the macro after you have opened all the workbooks at the same time.

The macro copies all the data from the fist sheet of each workbook, creates a new workbook and names it ConcatResults.xls then saves the copied data into it until when there are no more open workbooks that are being copied from. This new work book is saved in C:

This last solution seems complicated but its worth giving it a short.

Best Answer
Best Answer
Answered By 566795 points N/A #125534

Combine multiple Excel files into one file

qa-featured

You can use the Worksheet tab found at the bottom of Microsoft Excel. This can help you compile and organize different Excel files. No more opening of different Excel documents, you can just copy all the contents of different files into one Excel file and organize them in different Worksheet names.

  • Open both Excel files that you wished to merge. Decide which of the two the Master List is and which is the Source List.
  • Switch to the Source List. Highlight all the data you wish to copy by pressing SHIFT + arrow keys. Press CTRL + C afterwards.
  • Now, switch to the Master List. Rename the first worksheet on the Master List as shown in the image below.
          
  • After renaming the sheet, click on the Sheet2 tab beside it. Press CTRL + V to paste the data you copied from the Source List.


 

  • Rename the second sheet, for example, Clients.

And there you have it.

You now have your Excel Master List.

Just save it and you’re done.

Answered By 0 points N/A #125536

Combine multiple Excel files into one file

qa-featured

Hi,

You have to make a file with a Master tracker, in the other sheet is the regionwise Client list to compile your work in only one file.

For example:

1. Open a new excel file then follow this.

Rename the sheet 1 to CLIENT MASTER LIST.

2. Then rename again the sheet to according to your tracker. Example.

Rename again the another sheet and so on.

3. Take a look in this example. The sheet 1 is WIMAX LUZON, sheet 2 is RRU change out and the last sheet is GAIN PLUS.

After you do this, save it.

Thanks.

Login/Register to Answer

Related Questions