# Is there a way of comparing new sheet with old sheet and returning the differences?

Just say I have a worksheet with a list of names and addresses. These are supplied externally. e.g.

John Smith   2 High Street
Jane Smith   2 High Street
Fred Johnson 4 High Street
Sue Johnson  4 High Street
David Jones   6 High Street
Audrey Murray 8 High Street
Billy Moran      10 High Street
Mary Moran      10 High Street


Let's say I am then sent a new version of the sheet, with all the above, plus some additional names/addresses, e.g.

John Smith   2 High Street
Jane Smith  2 High Street
Andy Lodger 2 High Street
Fred Johnson 4 High Street
Sue Johnson  4 High Street
David Jones   6 High Street
April Jones    6 High Street
Audrey Murray 8 High Street
Billy Moran      10 High Street
Mary Moran      10 High Street
Eileen Murphy  12 High Street
Michael Murphy 12 High Street
Able Baker        14 High Street
Hylda Baker       14 High Street
...


Now, what I want is some way of "subtracting" the first sheet from the second sheet, to give me just the updates, i.e. I want to end up with a sheet that contains just the following:

Andy Lodger 2 High Street
April Jones    6 High Street
Eileen Murphy  12 High Street
Michael Murphy 12 High Street
Able Baker        14 High Street
Hylda Baker       14 High Street



Is there a (hopefully easy) way to do that?

edit retag close merge delete

For the solution - where are the cell boundaries

"first_name | name | number | street" -- or --

"first_name name | number | street" -- or --

"first_name name | number street"

or anything else

( 2019-04-12 18:13:05 +0200 )edit

Sorry, Opaque, I did not make that clear, but it is as your 3rd line, i.e. Cell A1="John Smith", A2="2 High Street"
etc

What I typed in was a simplified version of the real worksheet, which also has a postcode in cell A3 etc. I left it out for simplicity. I also realised later that my (made up) example addresses were not characteristic of the real data. The people listed are only a small percentage of the people living in the street (and other streets, not shown), so would be more spaced out, geographically, more or less at random.

When I am sent a new version of the sheet (as mentioned above), the "new" people will be equally randomly spaced out, interspersed among the original people, and not just added at the end. Sorry, I should have explained this better.

The object of the exercise then is ...(more)

( 2019-04-13 10:24:23 +0200 )edit

Sort by » oldest newest most voted

Copy and paste the whole new Data just below the old, and do:

>Data>Filter>Standardfilter … with Option: [✓]no Duplicates

more

Interesting. I'm trying to think whether that would give me what I want or not. First time I read this, I thought not, but maybe it can. Worth trying, anyway. (I don't have the new real data yet, but I will experiment on some test data. Thank you).

( 2019-04-13 10:28:04 +0200 )edit

Hmm - no Duplicates is not Updates only (which I'd assume the answer to "What is new in the consecutive file" and that's how I understand OPs question)

( 2019-04-13 10:58:06 +0200 )edit

@Opaque yes youre right it's not the same.
But it seems easy to hide manually the "old" Data which are still exactly in the same Range above the "Updated"

( 2019-04-13 12:14:22 +0200 )edit

Thanks Karolus. Yes, that was my understanding of your answer. Still working on this problem (in between other things). Will report back on any progress made.

Thanks to all responders, and if anyone else has any further ideas, I would be interested to read those too. Thanks in advance.

( 2019-04-18 15:07:40 +0200 )edit

Karolus at al: Indeed this worked fine with my (not very numerous) test data. Now to try it with the real thing (many more records, so potentially tricker.

( 2019-04-19 13:30:33 +0200 )edit