Excel question...

[Chuck Wintle]

Here is the problem...2 excel files some with old info and some with new info....how to merge these together and keep only good data?

[Mike Trent]

Create a new file, then copy over data manually? Could be automated with macros if you’ve got a ton of data.

[Chuck Wintle]

Create a new file, then copy over data manually? Could be automated with macros if you’ve got a ton of data.

Thanks but I was really hoping to merge 2 files using the compare and merge feature of Excel to save what could be hours of work. A little experimenting with this feature is showing me that it is very basic and probably not suited to saving data where certain conditions have to be met...such as new dates and other changed variables plus the addition of new data.

[Chuck Wintle]

Create a new file, then copy over data manually? Could be automated with macros if you’ve got a ton of data.

yes i will need to setup a macro to do this.

[Mike Chance in Iowa]

You could probably do some If/then formulas, but unless we had some actual data to look at, there is no way to guess what type of formula you need.

[Grant Wilkinson]

I believe that ASAP utilities has a feature that will do what you want.

Find it here
http://www.asap-utilities.com/

The home and student version is free.

I've used it a lot, but not for what you are trying, so I may be mistaken, but it's worth a look.

[Alan Rutherford]

It would help to know what the data looks like. Without knowing that, my first thought is to combine both sets of data into a single spreadsheet, whether it's a 3rd Excel file, new page in one of them or whatever. Just copy and paste but the columns have to be the same of course. Be sure you have at least one good backup copy of everything before you start.

Then, depending on what your criteria are, you can sort by a criterion column, add one or more columns to calculate yes/no values or something else into, etc, etc.

Once you've identified which rows to keep and somehow flagged them, sort by the column(s) with the flags and delete the ones you don't want.

Before you delete, make a copy of that page so you get another chance in case it didn't come out right and don't forget about the ctrl-Z undo feature. Save your work as you go and make backups.

[Chuck Wintle]

It would help to know what the data looks like. Without knowing that, my first thought is to combine both sets of data into a single spreadsheet, whether it's a 3rd Excel file, new page in one of them or whatever. Just copy and paste but the columns have to be the same of course. Be sure you have at least one good backup copy of everything before you start.

Then, depending on what your criteria are, you can sort by a criterion column, add one or more columns to calculate yes/no values or something else into, etc, etc.

Once you've identified which rows to keep and somehow flagged them, sort by the column(s) with the flags and delete the ones you don't want.

Before you delete, make a copy of that page so you get another chance in case it didn't come out right and don't forget about the ctrl-Z undo feature. Save your work as you go and make backups.

Alan and others thank for your answers...
the new excel file is derived from a master data base with new calibration and dates in this file

here is what i need to do step by step....i was trying to squish the question too much at the beginning....

assuming the new file and old file in the same folder
a macro is run such that it will update the old file with new information and serial numbers are used to do the comparison
some info in the old file is to be skipped(not updated).
thats it.

[Alan Rutherford]

Copying into the same spreadsheet simplifies your cell references but won't really change the result.

So for every row in "SheetOld" you update columns X, Y and Z with data from "SheetNew", except that you skip some rows in SheetOld?

I like to do things one step at a time and new columns are cheap. Can you create a column in SheetOld with flags to indicate whether to update each row? Then check that column in your macro. Or you could just delete those rows after saving them somewhere, run the macro on everything that was left in the old file and then put them back.

If comparing the serial numbers is difficult, you could write a macro to put Y or N in a column indicating whether the serial number was found in the other list. Once it's done, you can check it and have confidence it's correct.

This is an easy problem with a database and MS Access is one of their finer products, IMHO. It will work with Excel files as the data source.

[Chuck Wintle]

Copying into the same spreadsheet simplifies your cell references but won't really change the result.

It will be 2 separate work sheets with information taken from sheet old and put into sheet new..

So for every row in "SheetOld" you update columns X, Y and Z with data from "SheetNew", except that you skip some rows in SheetOld?

Yes...it is updated with cal factors and expiry dates.

Some info in the sheetnew was entered manually initially and must not be altered becuase it is needed .

I like to do things one step at a time and new columns are cheap. Can you create a column in SheetOld with flags to indicate whether to update each row? Then check that column in your macro. Or you could just delete those rows after saving them somewhere, run the macro on everything that was left in the old file and then put them back.

The columns are in a specific format which cannot be changed because this excel file is used by another computer program. No extra columns can bed added.

[Chuck Wintle]

Essentially with the 2 worksheets, 1 new and 1 old, the old must be updated with info from the new but the old contains data which cannot be altered and therefore must not be touched...

maybe 150 lines of data with expiry dates and cal factors of the transducers. it is this complication that makes it difficult.

Otherwise with a new file generated from the database it would be a straight copy and paste into the right folder of the computer program and bob's your uncle the job is done.

But with the data that must remain untouched it complicates it a lot and hence all of my questions and vba and how to merge the data and produce what is needed.
But in thinking a little more I have a workaround that might just work...and your suggestion made it possible....for the serial numbers in each row I can add a prefix to the number and then do a sort, and copy and paste this info back to the new file generated and in theory I will have a workable file. Thanks Alan.

[Alan Rutherford]

...The columns are in a specific format which cannot be changed because this excel file is used by another computer program. No extra columns can bed added.

I'm assuming this is a one-time thing, not that it needs to be part of a regular data entry or update process of some sort. If that's the case, forget everything I've said and ask for twice what you were expecting to be paid. (Yes I know how much 2 x 0 is.)

Putting on my programmer hat, what the user doesn't know won't hurt him. You can add columns and then delete them and do anything else you want as long as it all looks the way it should when you're done. Then replace the working copy of the spreadsheet with the new one you created.

A trick that's sometimes helpful: add 2 columns. Fill the first one with an identifier for the sheet e.g. "A" or "B". In the next one insert the row number. I assume you know you can do that by entering 1 or 2 numbers at the top of the list and dragging down. Then if you sort and need to get back to the original order, you can do it.

Good luck.

[Chuck Wintle]

I'm assuming this is a one-time thing, not that it needs to be part of a regular data entry or update process of some sort. If that's the case, forget everything I've said and ask for twice what you were expecting to be paid. (Yes I know how much 2 x 0 is.)

Putting on my programmer hat, what the user doesn't know won't hurt him. You can add columns and then delete them and do anything else you want as long as it all looks the way it should when you're done. Then replace the working copy of the spreadsheet with the new one you created.

A trick that's sometimes helpful: add 2 columns. Fill the first one with an identifier for the sheet e.g. "A" or "B". In the next one insert the row number. I assume you know you can do that by entering 1 or 2 numbers at the top of the list and dragging down. Then if you sort and need to get back to the original order, you can do it.

Good luck.

this is an update process to the excel worksheet that will be repeated at periodic intervals to keep up to date on cal factors and expiry dates. the program that uses this info checks the date for expiration and will red flag any transducer past due. Thus I was trying to create a macro for the update process.....but in a few months that data that cannot be touched will not be needed and then the update process willl be a copy and paste of the new worksheet into the correct folder of the computer program.