Welcome to MacTalk Australia

the largest Australian community for Apple discussions and topics

Join the discussions, Register Now!
Results 1 to 9 of 9
  1. #1

    Join Date
    Jan 2005
    Location
    Sydney, Australia, Australia
    Posts
    4,318

    Default Help! Excel Nightmare

    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.

  2. #2

    Join Date
    May 2005
    Location
    Inner Space
    Posts
    5,522

    Default

    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
    This opinion intentionally left blank.

  3. #3

    Join Date
    Jan 2005
    Location
    Sydney, Australia, Australia
    Posts
    4,318

    Default

    its about 2000 rows, 4 columns...


    how do you concatenate the csv files?

  4. #4

    Join Date
    May 2005
    Location
    Inner Space
    Posts
    5,522

    Default

    Quote Originally Posted by fulltimecasual View Post
    its about 2000 rows, 4 columns...


    how do you concatenate the csv files?
    open terminal

    cat file1 file2 file3 > file4
    This opinion intentionally left blank.

  5. #5

    Join Date
    Apr 2005
    Location
    Melbonia
    Posts
    2,192

    Default

    Quote Originally Posted by bartron View Post
    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.
    Live life with Blue Sun

  6. #6

    Join Date
    Feb 2007
    Location
    Melbourne
    Posts
    1,365

    Default

    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.


    Drifting dry's down current lanes.

  7. #7

    Join Date
    Jan 2005
    Location
    Sydney, Australia, Australia
    Posts
    4,318

    Default

    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.

  8. #8

    Join Date
    Mar 2007
    Location
    Canberra
    Posts
    285

    Default

    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!

  9. #9

    Join Date
    Oct 2004
    Location
    Near Norwood, Adelaide
    Posts
    2,760

    Default

    This sounds like the sort of thing I'd use BBEdit for.
    Grrrr, Arrrrghhh!!!
    http://www.dpreview.com/galleries/8259491826/photos
    My Photo Gallery

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •