Column data comparison - what has been deleted and what has been added

[AA Arens]

Hello,

There has been quite some similar questions, but slightly different with that what i am looking for.


I have four columns A - D

A and B consists of name and email address - previous situation
C and D consists of name and email address - at a new stage

I am looking for a formula that displays in column E and F the name/email that has been deleted in new stage list.

And I am looking for a formula that displays in column G and H the name/email that has been added to the new stage list.

Name can occur more often, but the email address is unique.

Thank you for you help.

Bart

(Excel 2013)

 

[Claus Busch]

Hi Bart,

Am Fri, 8 Nov 2013 20:57:15 -0800 (PST) schrieb AA Arens:

A and B consists of name and email address - previous situation
C and D consists of name and email address - at a new stage

in E1 try:
=IF(AND(C1<>"",COUNTIF($A$1:$A$1000,C1)=0),C1&" added","")
if G1 try:
=IF(AND(A1<>"",COUNTIF($C$1:$C$1000,A1)=0),A1&" deleted","")

for the names. Modify the formulas for the email addresses


Regards
Claus B.

 

[AA Arens]

Hello,



There has been quite some similar questions, but slightly different with that what i am looking for.





I have four columns A - D



A and B consists of name and email address - previous situation

C and D consists of name and email address - at a new stage



I am looking for a formula that displays in column E and F the name/email that has been deleted in new stage list.



And I am looking for a formula that displays in column G and H the name/email that has been added to the new stage list.



Name can occur more often, but the email address is unique.



Thank you for you help.



Bart



(Excel 2013)

Thanks Claus.

Your formulah gives a good impression if people has changed their email address.

What I was looking for if the combination name + email address, which represent only one person, has been deleted or added comparing the previous situation (A+B).

FYI, I am doing this to compare two exported Linkedin CSV files, in order to which connections were added, and, more important, who has disconnected.

 

[Claus Busch]

Hi Bart,

Am Sat, 9 Nov 2013 02:04:41 -0800 (PST) schrieb AA Arens:

What I was looking for if the combination name + email address, which represent only one person, has been deleted or added comparing the previous situation (A+B).

then try:
=IF(ISERROR(MATCH(A1&B1,$C$1:$C$1000&$D$1:$D$1000,0)),A1&" deleted","")
=IF(ISERROR(MATCH(C1&D1,$A$1:$A$1000&$B$1:$B$1000,0)),C1&" added","")
Both formulas have to be array-entered with CTRL+Shift+Enter


Regards
Claus B.

 

[AA Arens]

Hello,



There has been quite some similar questions, but slightly different with that what i am looking for.





I have four columns A - D



A and B consists of name and email address - previous situation

C and D consists of name and email address - at a new stage



I am looking for a formula that displays in column E and F the name/email that has been deleted in new stage list.



And I am looking for a formula that displays in column G and H the name/email that has been added to the new stage list.



Name can occur more often, but the email address is unique.



Thank you for you help.



Bart



(Excel 2013)

The formula does not give any value in all fields. I have added the array properly - the formula will be surrounded by { and }.

 

[Claus Busch]

Hi Bart,

Am Sat, 9 Nov 2013 03:12:31 -0800 (PST) schrieb AA Arens:

The formula does not give any value in all fields. I have added the array properly - the formula will be surrounded by { and }.

can you post a sample file?
All answers from me are tested and work.


Regards
Claus B.

 

[AA Arens]

Hello,



There has been quite some similar questions, but slightly different with that what i am looking for.





I have four columns A - D



A and B consists of name and email address - previous situation

C and D consists of name and email address - at a new stage



I am looking for a formula that displays in column E and F the name/email that has been deleted in new stage list.



And I am looking for a formula that displays in column G and H the name/email that has been added to the new stage list.



Name can occur more often, but the email address is unique.



Thank you for you help.



Bart



(Excel 2013)

Hi Claus,


I listed it here as sample:

(n=name / e=email)

old situation new situation

A B C D

n1 e1 n5 e5
n5 e5 n1 e1
n3 e3 n4 e4
n7 e7
n1 e1

added:

n4 e4
n7 e7


deleted:

n3 e3


Bart

 

[Claus Busch]

Hi Bart,

Am Sat, 9 Nov 2013 05:53:55 -0800 (PST) schrieb AA Arens:

A B C D

n1 e1 n5 e5
n5 e5 n1 e1
n3 e3 n4 e4
n7 e7
n1 e1

added:

n4 e4
n7 e7

deleted:

n3 e3

look here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "ColComp"


Regards
Claus B.

 

[AA Arens]

Hello,



There has been quite some similar questions, but slightly different with that what i am looking for.





I have four columns A - D



A and B consists of name and email address - previous situation

C and D consists of name and email address - at a new stage



I am looking for a formula that displays in column E and F the name/email that has been deleted in new stage list.



And I am looking for a formula that displays in column G and H the name/email that has been added to the new stage list.



Name can occur more often, but the email address is unique.



Thank you for you help.



Bart



(Excel 2013)

It does not work but I know the cause
If I make an array, the last section of the formula remains the same:

....."Added: "&C1&" "&D1,"")}
....."Added: "&C1&" "&D1,"")}
....."Added: "&C1&" "&D1,"")}

instead of

....."Added: "&C1&" "&D1,"")}
....."Added: "&C2&" "&D2,"")}
....."Added: "&C3&" "&D3,"")}

I have no idea how you performed this.