Extracting non matching records to a new book

[simjambra]

Hi there. I have had a search and there are similar requests to my problem but not quite exactly.

Basically I have 2 reports in 2 separate workbooks, week 1 and week 2, listing holiday properties.

Anything that appears in week 1 but not in week 2 can be copied into a sheet in another workbook called Data and placed in the "To Delete from Database" sheet.

Anything that appears in week 2 but not in week 1 can be copied into a sheet in the Data workbook and placed in the "To Add to Database" sheet.

I did hope there would be some Advanced Filtering option or Formula as I don't really know any VB.

I have added a basic sample but feel free to ask me to expand.

Thanks for your help.

[simjambra]

Is the difficulty to do with all being separate workbooks?

If there is a more straight forward way by rearranging my data into the same file for example, I will happily work around.

Thanks guys

[simjambra]

Ok I am working on this and I have more or less found a solution, the trouble is it's a bit long winded and the calculation takes forever. It really seems like a workaround.

I would be everso grateful if someone could cast an eye over what I am doing and perhaps give me an idea or two about where I am not bein as economical with my process.

HEre we go:

1. I have 2 separate week files of data, roughly 35000 rows. Each file has exactly the same layout (headings).
2. There is a unique ID column identifying each record within the list of data.
3. Where the ID is present in the first file and not in the second, I need to remove all records and place them in a new sheet called “Remove” (or similar).
4. Where the ID is present in the second file and not in the first, I need to remove all records and place them in a new sheet called “Add” (or similar).
5. These new sheets are going to be used to add and remove records from a larger database.
6. This is the way I am currently achieving this:

I. Copy the sheet from the second file into the first file so there is a single file with two weeks worth of data.
II. Copy the ID field from Sheet 2, into Sheet 1.
III. Copy the ID field from Sheet 1 into sheet 2.
IV. On Sheet 1, between the two ID fields, I insert a column (labelled “Status Remove”) with the following formula:

=IF(ISNUMBER(MATCH(C2,E:E,0)),””,”REMOVE”)

V. I then Copy the formula down and so it doesn’t continually recalculate, Copy and Paste Values.
VI. Using AutoFilter, I then filter out all the Removes and click into A1, Ctrl&A to Select All, Copy and Paste into a new sheet, which I name “Remove”.
VII. I do roughly the same process on Sheet 2, except these are the “Add” records.
VIII. I then have 4 sheets, Week 1, Week 2, Remove and Add.
IX. Finally I remove the 2 columns from each sheet that I pasted/added so each record has the original amount of columns.

Thanks, as always

Simon