Going through my many Excel Functions that I use I remembered one that I think you’ll like. It helps to find data that you need to work on and highlights it in a different way to using Conditional Formatting which I showed you some time ago. The function in question is called ISNA.
It can be used in many different ways so I am going to give you just one example that may be very useful to know.
Here is the scenario: You have a small database in an Excel workbook that is used by several people who create their own versions. After a time you have been tasked with making sure that the data in all workbooks are the same. This could be a very time consuming activity, but with the use of IF and ISNA, it will make your work less arduous.
I am going to split this exercise down into small parts so that you can take from it what you like.
So here is a typical database or list of items.
Someone else’s worksheet looks like this.
This is just an extract of the data, which is over 1,000 rows.
Finding the differences
- Copy and paste into a new sheet, the second list next to the first list a few columns apart. Start your list in column C. I have started in line 4.
We will use the VLookup function which works on the first column of information. To do this we need to create a ‘key’ in the first column and the formula goes into the second column. You then place your data in the third column. This makes it easier to then save this as a master sheet that you can reuse.
- In cells A4 and L4 type the word ‘Key.
- In cells B4 and M4 type the word ‘Check’.
We need to have a key to work with to check and match information which at the moment is in separate cells in the list.
- In the Cell A5 type the following.
This gives you the last name and the first name and the persons Employment no. So that you can compare the correct information in both lists.
- Fill this formula down the page.
The result looks like this.
- Do the same thing for the second list to the right.
- Go back to the first list.
- In the cell B5 type a VLookup formula. Where the lookup value is the key in the first list, the table array is the whole second list and the column index number is the first column from that list. We need an exact match so type in False at the end before the final closing bracket.
- Autofill down the list.
This is the result.
From this you can see that there are a number of entries that do not match the first list.
Do the same thing for the second list on the right.
The result will look like this.
This is great because you can now sort by the Check column for both lists and have the #N/A rows together in both lists at the top of the lists.
Having this result you can easily update each list with the new or changed data.
Hiding the data you don’t need
It’s great to have all the data there but would look more inviting if you only saw the data that was different. So far I have used the VLookup now we can use the IF and ISNA functions to only show what we need to work on. Keep the VLookup formula and add the IF and ISNA parts like this.
=IF(ISNA(VLOOKUP(A3,$G$3:$J$27,1,FALSE)),”Missing”,””) for the left list and
=IF(ISNA(VLOOKUP(L5,$A$4:$H$20,1,FALSE)),”missing”,””) for the right list in cells B5 and M5 respectively.
Then autofill these formulas down the column and the result will look like this.
To add a little more emphasis you could colour the text in say Green.
Now add the data from one list into the other that was missing before you did this exercise, and vice versa so that both lists are exactly the same.
Hope you have enjoyed this function as much as I have. I do like simplifying my work.