formula compare two colums

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

• Home
• Courses
• Promotions
• Schedule
• Formats
• Our Clients

Forum home » Delegate support and help forum » » Formula to compare two colums

# Formula to compare two colums

Resolved · Medium Priority · Version 365

### 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

Training courses

Training information:

 Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions What does 'Resolved' mean? Any suggestions, questions or comments? Please post in the Improve the forum thread.

### Hide columns in an Excel 2010 Worksheet

If you don’t want part of the Excel worksheet to be visible or when you don’t want certain data to appear in print outs, then a simple solution is to temporarily hide a column or multiple columns.

Hiding a single column:

1)Right click on the column header of the column you want to hide (this is the grey bar along the top edge of the worksheet)