Excel: Exact Function
Published on 8th August 2006
This month we are going to look at a way of showing if two cells in an Excel spreadsheet are the same.
But first a quick tip on password security. The most secure passwords have a mixture of letters, numbers and symbols. The best symbol to use is £ because most hacking software has been written using an American or European keyboard, which don't have a £ sign on them! So, shoes£10aPair, is a very strong password and when you have to change it, just put the price-up! That's inflation for you.
To return to the main topic, the testing for similar cells is useful if you have a list of data that you need to check for duplicates, for example.
The function to use is the EXACT function, which tests if two cells are the same. It is case sensitive and so is more accurate than using IF to test if two cells are the same - it is also simpler than IF.
The Exact Function
The following example shows a formula testing whether A1 and A2 are exactly the same:
=EXACT(A1,A2)
If they are the cell will display the word TRUE and if not it will say FALSE.
Finding Duplicates
To find duplicate values in a list use the following procedure:
- Sort the list by the column where there may be duplicates, so that the duplicates are together.
- In a blank column put in the EXACT function to test for duplicates. For example if you are starting on row 2, =EXACT(B2,B3) will test the values in column B for the current record and the next to see if they are the same.
- Copy that down the rest of the column. Remember, if you double-click instead of dragging it will automatically copy down the whole column.
- The new column will now show 'TRUE' where that row is the same as the next one.
- Carry-on to quickly get the duplicates together: Give the command to Copy the duplicates column.
- In the next blank column give the command Edit - Paste Special - Values. This converts the TRUE's to text rather than formulas.
- Now Sort by this new column and you'll find all the rows marked TRUE will be together, so you can delete them in one go.
Using Exact With Multiple Columns
If you need to test for duplicates and duplication can be indicated by more than 1 column you can nest the EXACT function into AND or OR functions:
=AND(EXACT(B2,B3), EXACT(E2,E3)) will show TRUE if the values in columns B and E are repeated.
=OR(EXACT(B2,B3), EXACT(E2,E3)) will be TRUE if either of the pairs are duplicated.