PDA

View Full Version : Help! Excel Nightmare



Peter Wells
27th February 2008, 05:09 PM
so i just discovered that a co-worker has been filling out an excel spreadsheet, but has actually been working on 3 diffferent version of that spreadsheet. so info is all over the damn place.

so i was wondering is there a way of merging the spreadsheets?

all the columns are the same if that helps..

please, i really dont want to have to spend tonight cutting and pasting... :(

thanks.

bartron
27th February 2008, 05:44 PM
export to csv, concatenate the 3 different versions, re-open in excel and sort by something appropriate so you can see the duplicate records.

You'll still probably need to go through and delete any multiple items but should be fairly easy to spot.

cut and paste the result back into the original spreadsheet.

or something like that....without knowing the specifics of the spreadsheet and how big/small it is

Peter Wells
27th February 2008, 06:21 PM
its about 2000 rows, 4 columns...


how do you concatenate the csv files?

bartron
27th February 2008, 06:35 PM
its about 2000 rows, 4 columns...


how do you concatenate the csv files?

open terminal

cat file1 file2 file3 > file4

snark
27th February 2008, 07:10 PM
export to csv, concatenate the 3 different versions, re-open in excel and sort by something appropriate so you can see the duplicate records.


Won't that operation just create a long spreadsheet that has the 3 different versions one below the other? In which case, selecting the rows from each, copying and then pasting into a new spreadsheet would be better, because you'll keep all the formatting, formulas, etc.

I wish I could think of a way to properly merge them, based on which cells /rows had been edited last, but I can't - sorry.

swoffa
27th February 2008, 07:32 PM
You could copy the four columns of each file into a another spreadsheet. File 1 in the columns in ABCD, file 2 in FGHI, file 3 in KLMN. Then use the match formula to pit one column of data against the other to see which cells have differing values. Easily found using auto-filter. Then sort ascending/descending to get the mismatched data together and edit away.
Your still stuck with knowing which data in which file is actually correct though.

Peter Wells
27th February 2008, 07:34 PM
oooh, that sounds promisingg.. basically the data is all "correct" just some of it has been entered on one file, some on another, some on another..

so the comparison would just be override data.

teamshaw
27th February 2008, 07:37 PM
You could copy/paste or concatenate as suggested, then sort by each of the 4 columns. You could then use a nested if function that to fill in a fifth column with either a 1 or a 0 depending if it was equal to the line above, something like:

=if(A2=A1,if(B2=B1,if(C2=C1,if(D2=D1,1,0),0),0),0)

Then sort descending by this 5th column and delete all the 1s, then Bob's your uncle!

mjankor
27th February 2008, 07:55 PM
This sounds like the sort of thing I'd use BBEdit for.