Julie has attended:
Excel Advanced - Formulas & Functions course
Excel Advanced - Formulas & Functions course
Outlook Advanced course
Outlook Advanced course
Formula to compare two colums
I need to ensure that a person only attends a location once.
Can you advise on a formula which can check two columns of data,one with a unique code and the other with the location - That would identity if any duplicate area have been assigned to a person.
Person Location
A58 Tyrone
A58 Fermanagh
A66 Fermanagh
A66 Fermanagh
Eg A58 is fine as they are at different locations
A66 is a false return as the two locations are the same.
There are about 900 people to be checked and over 1000 different locations, each person could have between 1 -5 locations.
Any help would be appreciated.
Thanks
RE: Formula to compare two colums
Hi Julie,
Thank you for the forum question.
You can use a COUNTIFS function.
Add a new column. If you have the unique code in the range A2:A200 and the location in the range B2:B200 and want the to find duplicates in column C the function should look like this:
=COUNTIFS($A$2:$A$200,A2,$B$2:$B$200,B2)
Copy the function down and if the function return 1 the record is unique otherwise it will display how many duplicates of the record you have.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
