General discussion

Locked

Merge data in Excel 2k without pasting

By rapell ·
Hi Pals,
greetings and salutations! Recently I was asked by two users who were working on the same workbook(not shared)that they wanted to put their data together, and I did it manually. But look at this scenario, there are two pple working on exactly the same workbook, but saved differently. Let?s say each of them is entering data in particular rows in their respective copy of the workbook. Now we want to copy the data from person 1?s workbook to person 2?s workbook, without overwriting. How can I do this without the laborious copy-and-paste routine, just in case the rows were far apart and randomlydistributed?

This conversation is currently closed to new comments.

11 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by sgt_shultz In reply to Merge data in Excel 2k wi ...

do you have your hands on a sample copy of each workbook. and...you have rejected setting this up as a shared, um, workbook, right?
have you played around with what you can get from each workbook using File/Export. Pick maybe Tab delimited Text file for a test. You could import the results into a third Excel workbook to do your dastardly deeds.
How to merge them? That is the $64 question all right. We need really exact details to help answer that. a description of the fields and the data in 'em. my 2 cents is: This is a job for Access. You got access to Access?
but Excel can do lots...

Collapse -

by rapell In reply to

Poster rated this answer.

Collapse -

by chitosunday In reply to Merge data in Excel 2k wi ...

Try ms query (data , import external data, new database query. Search in the help of excel for more details.

Collapse -

by rapell In reply to

Poster rated this answer.

Collapse -

by rapell In reply to Merge data in Excel 2k wi ...

well of course most of my files are shared if more than one person uses them. However this was a scenario that got me thinking, you know how it is. so the temlates were sent to Bob the acct and Roy the manager in their e-mail so they include data about the upcountry office budget. So Bob saves the attachment to his PC as draft1 and fills up his rows, Roy does the same thing on his draft2. Then they call Geekie and say, now we want all our data for this sheet to be on one doc, which will be Finaldraft, please help us out. Now, can the wheels start turning....Thank you

Collapse -

by collignond In reply to Merge data in Excel 2k wi ...

First make a copy of both files into a directory, Create a new file, so for test purposes I used Book1(Source 1), Book2(Source 2) and Book3 merged data.

In book1 and Book2 I created two columbs of data the entry in the cell in book3 for book1's data looks like this "[Book1.xls]Sheet1!$A2" where Book1.xls is the file name,Sheet1 is the sheet name,A2 is the cell, you can copy this formula and past it to update the next rows data ie:A3

The simplest will be to recreate the original form with two cell for each entry in the merged info, NB:remember to copy only values where you are using a formula in the source to calculate data, or the formula will be copied and give you strange result.

Create a simple form for testing.

Collapse -

by mulliganm In reply to Merge data in Excel 2k wi ...

Just because they did not share is no reason I can see why you should not and and then use the Compare and Merge off the Tool Menu option. One has to assume one thing here; They will not be entering data at the time you are ready to merge. This is the time to do it correctly.

Collapse -

by rapell In reply to

Poster rated this answer.

Collapse -

by jdclyde In reply to Merge data in Excel 2k wi ...

You can't get there from here.

When users make their own sheets and they are wrong or not concistant, sometimes the BEST solution is to start over and make it right.

This will give you an opportunity to see if there are ways that it could be BETTER.

It takes more time on your part, but will pay off in the long run.

I know, not the answer you were looking for, but I have spend WEEKS at a time "fixing" files. what used to take them a few HOURS a week now take them about 30 minutes, once a month.

End of year roll over also used to take about a week, now it takes about two hours.

It will pay off in the long run.
(I DREAD everytime they bring a new one for me to look at)

Collapse -

by rapell In reply to

Poster rated this answer.

Back to Software Forum
11 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums