Ask Your Question
0

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

asked 2019-04-12 17:50:11 +0200

vondemhund gravatar image

updated 2019-04-12 17:53:22 +0200

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?

Thanks in advance.

edit retag flag offensive close merge delete

Comments

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

Opaque gravatar imageOpaque ( 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)

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

1 Answer

Sort by » oldest newest most voted
0

answered 2019-04-12 19:56:41 +0200

karolus gravatar image

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

>Data>Filter>Standardfilter … with Option: [✓]no Duplicates
edit flag offensive delete link more

Comments

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).

vondemhund gravatar imagevondemhund ( 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)

Opaque gravatar imageOpaque ( 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"

karolus gravatar imagekarolus ( 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.

vondemhund gravatar imagevondemhund ( 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.

vondemhund gravatar imagevondemhund ( 2019-04-19 13:30:33 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-04-12 17:50:11 +0200

Seen: 39 times

Last updated: Apr 12