So here's the situation:

I have multiple versions of the same spreadsheet-- each one has the exact same row and column labels.

The difference between any two given spreadsheets is that data in one spreadsheet shouldn't be in the other (but sometimes it might.)

Is there anyway to merge all of them into a "master copy" (or just a blank version) of the spreadsheet? (basically, using the data from various versions of that worksheet to fill out the main one)

Copy-pasting is extremely tedious, and doesn't allow me to copy blocks of rows IF the row numbering is non-contiguous. (For example, Rows 1, 2, 3, 6 are in a block, but row 4 and 5 just don't exist.)

Ideas? Googling hasn't turned up anything that seemed directly relevant to this problem.

  • Is each version in different file? – matan129 Jul 2 '13 at 15:33
  • @matan129 yes, each version of the spreadsheet is in a different xls file. – GrinReaper Jul 2 '13 at 15:40

From here:

To merge Microsoft Excel files together, it is best to save them as CSV files first. Open the Excel files and in the menu bar, click File, Save As. In the Save as type drop-down list, select CSV (comma delimited) from the list.

Do this for each Excel file you want to merge, then place all the CSV files in the same folder. For ease, place them in a folder in the root of the C: drive (e.g. c:\csvfiles).

Open the Windows command prompt and navigate to the folder containing the CSV files. Type dir to view the files in the folder and ensure all the files are there.

Type in the below command to merge all CSV files in the folder into a new CSV file titled "newfile.csv" (any name could be used).

copy *.csv newfile.csv

After the new file has been created open the new CSV file in Microsoft Excel and save it as an Excel file.


I don't know of a way to do this with Excel. If you save as a CSV file, this is very easy to do in R.

file1 <- read.csv("file1.csv", header=TRUE, nrows=50000) # read the file into memory
file2 <- read.csv("file2.csv", header=TRUE, nrows=50000)
file3 <- read.csv("file3.csv", header=TRUE, nrows=50000)

merge12 <- merge(file1, file2, all=TRUE)                 # merge the files
final <- merge(merge12, file3, all=TRUE)

write.csv(final, "merged-data.csv", quote=FALSE, row.names=FALSE) # save the output as CSV

Once you have the CSV output, you can import back into Excel, save as XLSX and you're on your way.

On the read.csv() statements, nrows is a way of limiting the amount of memory allocated to this data. I usually round up to the next 1,000 (so if your file has 1,200 rows, I'd put 2,000). This isn't necessary, but I find it helps with R performance if you're working with a lot of data.

  • Ah, matan129's answer is much more elegant than mine. – Lenwood Jul 2 '13 at 16:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.