CODEWITHSUNDEEP
excel
Mohammad Qamar
Mohammad Qamar

Reputation: 9

Excel column compare and match

I have a report which shows the ID#s and the names corresponding to those IDs.

I am trying to write a formula which check the ID# and see if the same ID#s have the same Name;

If the same ID #s are listed more then once and they have different names then it should show that the IDs and names do not match.

The idea is that same ID#s should have the same name; if the same IDs have different names then it should show that ID have different names.

Col A     Col B       Col C

ID        Name         Desired Output
3         Peter        ID with multiple names
3         Ken          ID with multiple names
5         Chris        match
5         Chris        match
5         Chris        match
6         Dave         match
6         Dave         match
7         Lisa         match
8         Mark         match
10        Ken          match 
12        Frank        ID with Multiple names
12        Randy        ID with Multiple names  
12        Frank        ID with Multiple names 
12        Mike         ID with Multiple names

enter image description here

Upvotes: 0

Views: 42

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Use COUNTIFS():

=IF(COUNTIFS(A:A,A2,B:B,"<>" &B2),"ID with multiple names","match")

enter image description here

Upvotes: 2

Related Questions