Finding the odd one out

[Assadniazi]

Hi

Need help.

I have attached a working sheet. What I want to do is, you will see 2 column with NAME and AGE. Both containing same names but different age.

So I want a formula in column G that If the age of the pupil is SAME e.g. JOHN is 10 in both columns, Than DO NOTHING, however if the age is different like for PETER than in column G, write the complete thing including name and age so the column G should contain PETER, 15 ...because the age in other column for Peter is 28.

Please advice

Thanks

[Glenn Kennedy]

Will the matching names always be in the same row?

[Glenn Kennedy]

If so, this will work:

=IF(A4&B4=D4&E4,"",A4&", "&B4)

[Assadniazi]

Will the matching names always be in the same row?

Hi Glenn,

Thanks for the reply.
No. It will not always be in same row.

[Glenn Kennedy]

OK then. Use this Array formula, which must be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

=IF(ISNUMBER(MATCH(A4&B4,$D$4:$D$6&$E$4:$E$6,0)),"",A4&", "&B4)

[Assadniazi]

Hi Glenn,

MANY THANKS! This worked ! you are a star ! :D

[Glenn Kennedy]

Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.