The Evil Within 2 review-in-progress
In Access you can compare two lists to find duplicates. I am trying to merge two lists in Excel and would then like to find any duplicates in a specific column. Is there an easy way to do this? Would there be a simple piece of VB that could do the task?
Secondly in my search to find this solution I did find a piece of data validation that could be used to highlight if a duplicate entry was added to a column. It does work and it could be useful but the question I have is: How does it work?
The formula to put in the custom validation is =COUNTIF($A$2:$A$20,A2)=1. Should you try to enter a value that is a repetition of earlier in the column, it will error, just can't see how it does it.
Here you go:-
Merge the two lists into one column, lets say column 'A'.
Use 'Data' and 'Sort' on this column, this will put all the duplicates together.
In another column enter the following:
Filter your new column on the blanks and you will have just one of everything..
Hope this helps,
Thanks for the quick replies,.
Gandalf, your download looks ideal - unfortunatley PCs at work do not allow anything to be loaded to them.
Terry, I think I will have to resert to your solution but it is not easy as the two sets of data are whole spreadsheets in their own right. It will be a little complicated merging them. I just wanted to run a comparison of one column in each to see if there were any duplicates.
I may even resort to the Access solution and put them in two tables.
As to how that statement above works - anyone got a simplistic descrition of how it works?
If you do a search in Excel's help menu like "Filter a list by using advanced criteria" you may find the answer to your question?
Craig, I may have mis-interpreted your question.? My formula will identify the duplicates but it does require the list to be in sort order...
The formula you have found counts the number of times an entry appears in a list regardless of its position and turns it into a TRUE / FALSE statement.
COUNTIF counts the number of entries that meet a condition.
$A$2:$A$20 is the range of cells the formula will check (the $ sign fixes the position).
A2 is the condition to be met.
=1 turns it into a TRUE or FALSE statement
So, if whatever appears in A2 appears anywhere else in the range A2 - A20 it will count the number of times it appears, and because it is more than 1 it will say FALSE.. if it only appears once (=1) it will say TRUE..
The only problem with this is, if you set your range $A$2:$A$100 and there is nothing in the cells from say, A70 down, when you copy your formula down it will show FALSE for the empty cells....
Hope this helps, Terry..
I was thinking of using the formula in a validation. I can't understand the formula in that it seems to compare A2 to the range, but it does stop any repetition happening anywhere in the list not just a recurrence of the A2 value.
It is just getting it straight as to how it works so that I can adapt it to other situations
A simple (and possibly off the wall) solution would be to import the streadsheets into Access while not allowing duplicates to be listed.
You would then be left with a single list which you could set to Excel.
Sorry for the 'slightly' belated reply....
A2 is not a fixed reference (no $ sign) and therefore as you copy it down the row it becomes A3, A4, A5, etc..
Sorry, should have been 'copy it down the column' not 'row'...
This thread is now locked and can not be replied to.