Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
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
add comment

2 Answers

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.

share|improve this answer
    
I'm also assuming that since csv files are plaintext, I have to do this for each corresponding spread sheet from the different xls files? –  GrinReaper Jul 2 '13 at 16:02
    
Yes, you will have to save each of the spreadsheets in csv format manually. Edit: Here's a handy application for batch converting xls to cs –  matan129 Jul 2 '13 at 16:05
add comment

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.

share|improve this answer
    
Ah, matan129's answer is much more elegant than mine. –  Lenwood Jul 2 '13 at 16:04
    
Hah. I don't know R anyway, but thanks. –  GrinReaper Jul 2 '13 at 16:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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