Data Compare from 2 lists of data (Excel 2000)

Topic

New Reply

Hi, I work in Finance and at month end, must compare data from 2 lists and identify matches whereby both the payee, gl and amount in List 1=payee, gl and amount in list 2. What I have been doing is taking the data and placing on the same worksheet (list 1 in columns a-g and list 2 in i-o), sorting and manually matching by =a1=b1 kind of thing. This is good to a point, however invariably one list of data will contain more rows than the other and I have to manually move the data up and down the worksheet to keep the matches lined up and isolate the non-matches. Does anyone know of a macro/other that would benefit me to identify all matches and non-matches?

Thanks!

Reply | Quote

Replies

You can download a free add-in for comparing workbooks from Chip Pearson’s site.

Reply | Quote

Hans, I certainly appreciate your reply. This is a really neat utility; however, it does not do exactly what I want it to. This comparison looks at specific cells for matches. For example, does E2=E2. The data may appear in different cells in my case even though it is sorted (E2 really = E9). Since I am comparting data from 2 different sources, there may be more data in one set of data than the other. For example, there may be 200 rows in one list, and 150 in the other. At maximum there will only be 150 matches but the way this compare works even though there may be 150 matches (in my example) unless the 2 are arranged with the data in the exact same locations in the worksheets – E2 (worksheet 1) and E2(worksheet2) contain the same values it will identify exceptions when really the worksheets contain the same data . The data is just in different locations ($100 may be in E2 on wks1 and in E9 on wks2). What I attempt to do each month is look at what is in my accounting system (one worksheet) and what has been paid by Accounts Payable (worksheet2). The difference must be accrued to record a liability in the proper period. That is why the worksheets may be a little different and if I sort for example on dollar value, the AP system may have more records than the accounting system and as a result what is in e2 on one worksheet may actually be in e10 on the other.

I hope this makes sense but what I want to do is compare data within worksheets regardless of what cell row location (column will always be the same) the data is in. For example if we have paid person A $100 and this is in cell E2 on wks 1(accounting system) and paid person A $100 on wks 2 (Accounts Payable) and this is in cell E9 on wks 2 I have a data match and this should not be an exception.

Any additional ideas?

Reply | Quote

Perhaps the formulas near the bottom of Duplicate And Unique Items In Lists (alos on Chip Pearson’s website) can be adapted to your needs. John Walkenbach also has a tip that may be useful: Comparing Two Lists With Conditional Formatting.

Reply | Quote

Perhaps the formulas near the bottom of Duplicate And Unique Items In Lists (alos on Chip Pearson’s website) can be adapted to your needs. John Walkenbach also has a tip that may be useful: Comparing Two Lists With Conditional Formatting.

Reply | Quote

Hans, I certainly appreciate your reply. This is a really neat utility; however, it does not do exactly what I want it to. This comparison looks at specific cells for matches. For example, does E2=E2. The data may appear in different cells in my case even though it is sorted (E2 really = E9). Since I am comparting data from 2 different sources, there may be more data in one set of data than the other. For example, there may be 200 rows in one list, and 150 in the other. At maximum there will only be 150 matches but the way this compare works even though there may be 150 matches (in my example) unless the 2 are arranged with the data in the exact same locations in the worksheets – E2 (worksheet 1) and E2(worksheet2) contain the same values it will identify exceptions when really the worksheets contain the same data . The data is just in different locations ($100 may be in E2 on wks1 and in E9 on wks2). What I attempt to do each month is look at what is in my accounting system (one worksheet) and what has been paid by Accounts Payable (worksheet2). The difference must be accrued to record a liability in the proper period. That is why the worksheets may be a little different and if I sort for example on dollar value, the AP system may have more records than the accounting system and as a result what is in e2 on one worksheet may actually be in e10 on the other.

I hope this makes sense but what I want to do is compare data within worksheets regardless of what cell row location (column will always be the same) the data is in. For example if we have paid person A $100 and this is in cell E2 on wks 1(accounting system) and paid person A $100 on wks 2 (Accounts Payable) and this is in cell E9 on wks 2 I have a data match and this should not be an exception.

Any additional ideas?

Reply | Quote

You can download a free add-in for comparing workbooks from Chip Pearson’s site.

Reply | Quote

Could you upload a sample workbook that shows what your are trying to do sanatized of any sensitive data or course. It sounds like this could be done fairly simply with a little VBA code, but it would be much easier with something to look at.

Reply | Quote

Here goes. The non matches are x, y and z . All of the other values equal even though they are in different locations on worksheet. This is the type of data I try to compare. I can place it on separate worksheets or the same – whatever makes programming easiest. See sample file.

Reply | Quote

Here is a “simple way”, no macro:
In C2 enter the formula:

=A2&B2

Fill C2 down the column by dbl-clicking the “fill handle” and the lower right corner of cell

In G2 enter the formula:

=ISNUMBER(MATCH(E2&F2,$C$2:$C$16,0))

Dbl-click on the fill handle to copy down the column. Items with FALSE are not in the list, I get:
X 9, Y 10, Z 11 and (you didn’t mention): A 15 (in row 16)

You could eliminate the intermediate column in C if you use an ARRAY formula (confirm with ctrl-shift-enter) in G2:

=ISNUMBER(MATCH(E2&F2,$A$2:$A$2:$A$16&$B$2:$B$16,0))

You could also create a User function if desired or a macro (if desired)
Steve

Reply | Quote

Steve, this does most of what I want to do. But what if there were extra data in the first list – in columns a and b (that don’t exist in the list on the right) that I wanted to identify with a “false” as well as I would want to do here. Is there anyway to do this using what you have already posted?

Reply | Quote

Not usre exactly what you mean.

Are you asking to check the list in Cols A/B to see if it has items NOT in the other list? If so you can do a similar thing: add an extra column for the other list, and create the match to look for it.

If it is something else, could you be a little more specific about what you need?

Steve

Reply | Quote

Steve, thanks. I tried this and it works like I need it to. Will you be so kind as to provide me with an explanation of how the ISNUMBER(MATCH(E2&F2,$C$2:$C$16,0)) statement works?

Thanks.

Reply | Quote

The MATCH function takes the first argument (E2&F2) and searches for it in the array specified by the second argumant ($C$2:$C$16). The 0 (Zero) third argument says to search for an exact match. If a match is found, then the function returns the position number of the match in the array. If no match is found, then the function returns the error #N/A (which is not numeric). The ISNUMBER returns TRUE if a match was found and the MATCH function returned a position number and it returns a False if the MATCH function returned the non-numeric #N/A error.

Reply | Quote

Thanks. What would the 3rd argument be (other than 0) if you were not looking for an exact match?

If match is found you say returns the position number of the match in the array. Position number is cell number?

Thanks again for the help!

Reply | Quote

Yes, the third parameter can be -1, 0, 0r +1. From the Excel Help file:

[indent]

---

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.

---

[/indent]

No, it is not a cell number, it is the position in the array. One is the first element in the array (first cell if the array consists of cells). Again, from the Help file:

[indent]

---

MATCH returns the position of the matched value within lookup_array, not the value itself. For example, MATCH(“b”,{“a”,”b”,”c”},0) returns 2, the relative position of “b” within the array {“a”,”b”,”c”}.

---

[/indent]

Reply | Quote

Thanks for that explaination Legare — that’s what I was just struggling to do in Excel!

Reply | Quote

Thanks for that explaination Legare — that’s what I was just struggling to do in Excel!

Reply | Quote

Yes, the third parameter can be -1, 0, 0r +1. From the Excel Help file:

[indent]

---

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.

---

[/indent]

No, it is not a cell number, it is the position in the array. One is the first element in the array (first cell if the array consists of cells). Again, from the Help file:

[indent]

---

MATCH returns the position of the matched value within lookup_array, not the value itself. For example, MATCH(“b”,{“a”,”b”,”c”},0) returns 2, the relative position of “b” within the array {“a”,”b”,”c”}.

---

[/indent]

Reply | Quote

Thanks. What would the 3rd argument be (other than 0) if you were not looking for an exact match?

If match is found you say returns the position number of the match in the array. Position number is cell number?

Thanks again for the help!

Reply | Quote

The MATCH function takes the first argument (E2&F2) and searches for it in the array specified by the second argumant ($C$2:$C$16). The 0 (Zero) third argument says to search for an exact match. If a match is found, then the function returns the position number of the match in the array. If no match is found, then the function returns the error #N/A (which is not numeric). The ISNUMBER returns TRUE if a match was found and the MATCH function returned a position number and it returns a False if the MATCH function returned the non-numeric #N/A error.

Reply | Quote

Steve, thanks. I tried this and it works like I need it to. Will you be so kind as to provide me with an explanation of how the ISNUMBER(MATCH(E2&F2,$C$2:$C$16,0)) statement works?

Thanks.

Reply | Quote

Not usre exactly what you mean.

Are you asking to check the list in Cols A/B to see if it has items NOT in the other list? If so you can do a similar thing: add an extra column for the other list, and create the match to look for it.

If it is something else, could you be a little more specific about what you need?

Steve

Reply | Quote

Steve, this does most of what I want to do. But what if there were extra data in the first list – in columns a and b (that don’t exist in the list on the right) that I wanted to identify with a “false” as well as I would want to do here. Is there anyway to do this using what you have already posted?

Reply | Quote

Here is a “simple way”, no macro:
In C2 enter the formula:

=A2&B2

Fill C2 down the column by dbl-clicking the “fill handle” and the lower right corner of cell

In G2 enter the formula:

=ISNUMBER(MATCH(E2&F2,$C$2:$C$16,0))

Dbl-click on the fill handle to copy down the column. Items with FALSE are not in the list, I get:
X 9, Y 10, Z 11 and (you didn’t mention): A 15 (in row 16)

You could eliminate the intermediate column in C if you use an ARRAY formula (confirm with ctrl-shift-enter) in G2:

=ISNUMBER(MATCH(E2&F2,$A$2:$A$2:$A$16&$B$2:$B$16,0))

You could also create a User function if desired or a macro (if desired)
Steve

Reply | Quote

Here goes. The non matches are x, y and z . All of the other values equal even though they are in different locations on worksheet. This is the type of data I try to compare. I can place it on separate worksheets or the same – whatever makes programming easiest. See sample file.

Reply | Quote