Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Compare from 2 lists of data (Excel 2000)

    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!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Compare from 2 lists of data (Excel 2000)

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

  3. #3
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Compare from 2 lists of data (Excel 2000)

    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?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Compare from 2 lists of data (Excel 2000)

    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.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Compare from 2 lists of data (Excel 2000)

    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.
    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Compare from 2 lists of data (Excel 2000)

    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.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Data Compare from 2 lists of data (Excel 2000)

    Here is a "simple way", no macro:
    In C2 enter the formula:
    <pre>=A2&B2</pre>

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

    In G2 enter the formula:
    <pre>=ISNUMBER(MATCH(E2&F2,$C$2:$C$16,0))</pre>


    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:
    <pre>=ISNUMBER(MATCH(E2&F2,$A$2:$A$2:$A$16&$B$2:$B $16,0))</pre>


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

  8. #8
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Compare from 2 lists of data (Excel 2000)

    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?

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Data Compare from 2 lists of data (Excel 2000)

    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

  10. #10
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Compare from 2 lists of data (Excel 2000)

    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.

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Compare from 2 lists of data (Excel 2000)

    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.
    Legare Coleman

  12. #12
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Compare from 2 lists of data (Excel 2000)

    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!

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Compare from 2 lists of data (Excel 2000)

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

    <hr>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.
    <hr>

    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:

    <hr>
    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"}.
    <hr>
    Legare Coleman

  14. #14
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Brantford, Ontario, Canada
    Posts
    2,391
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Compare from 2 lists of data (Excel 2000)

    Thanks for that explaination Legare --- that's what I was just struggling to do in Excel!
    Christopher Baldrey

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •