Daryl has attended:
Excel Advanced course
Comparing Lists
I have too worksheets of similar data (client ref; name, address, balance, dates).
There are some clients on one sheet but not the next and vice versa.
What is the easiest way to make the comparison? I currently am do the laborious method of going through it line by line (which takes several hours!)
RE: Comparing Lists
You can use the MATCH function to match the records from the one list against the other.
The answer you will get will be a number. This is a row number from the area you have chosen to match. If you do not get a number, that means that the value was not found, hence giving you your answer.
Here are some details regarding MATCH (you can also click on HELP in Excel to view similar info.)
------------
MATCH(lookup_value,lookup_array,match_type)
------------
Lookup_value is the value you use to find the value you want in a table.
Lookup_value is the value you want to match in lookup_array. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want.
Lookup_value can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.
Lookup_array is a contiguous range of cells containing possible lookup values. Lookup_array must be an array or an array reference.
Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.
If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.
If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.
If match_type is omitted, it is assumed to be 1.
RE: Comparing Lists
Thanks for your answer
When I do this, the first result #N/A is the first difference. I assume I then need to move the list order and reapply the formula to the rest of the list to get the correct match (or until get anohter #N/A result and repeat the process).
Is there any way of doing this comparison to automatically identify the differecnes without the manual altering of the list as I work down it
Regards
Daryl
RE: Comparing Lists
Daryl
If you modify your original formula with an ABSOLUTE REFERENCE, before copying it down to the other values, then the range you will be looking into will remain the same.
So if your list is A2:B45
then you need to modify it woth $ signs to look like this
$A$2:$B$45
That locks the column and row references so that it does not change when copied.
I would check the first few results to make sure the formula is working properly before trusting the rest of them.
Regards
Richard
