Results 1 to 14 of 14
  1. #1
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Compare 2 lists of data (2002 SP3)

    Hi,

    I have to compare two lists of data which are in two sheets. When there is a mismatch certain info should be put in a third sheet.

    Please see the attached example file sheet 3. I know what to do to get the last 3 columns. The first 3 columns will never change except that they may be added or missing in the new month. I hope there is a possibility to do this without using code.

    It seems a fairly simple operation but I cannot find a solution. Can anyone help?

    Regards Marcel
    Attached Files Attached Files

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

    Re: Compare 2 lists of data (2002 SP3)

    Should the sheets be matched on column A? Or on the combination of columns A, B and C? Or something else?

  3. #3
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Compare 2 lists of data (2002 SP3)

    Hi Hans,

    Match on column A would be enough because the other two columns are just there for information. Column A is the key.

    Regards Marcel

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

    Re: Compare 2 lists of data (2002 SP3)

    And on what do you want to report mismatches? Only on the Amount column or also on the BC column?

  5. #5
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Compare 2 lists of data (2002 SP3)

    Hi Hans,

    The third sheet is exactly the output I want. So on amount the difference should be there and on BS the old and new value should be there.

    Regards Marcel

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

    Re: Compare 2 lists of data (2002 SP3)

    Why are E3 and D5 blank on the Differences sheet?

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

    Re: Compare 2 lists of data (2002 SP3)

    I think I got it. The attached version uses a combination of ISNA and VLOOKUP.
    Attached Files Attached Files

  8. #8
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Compare 2 lists of data (2002 SP3)

    Because there is no data for that field. So no data means blank.

  9. #9
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Compare 2 lists of data (2002 SP3)

    Hi Hans,

    The formula's you give me are somewhat the sollution I was thinking about. But my problem is that I only want to have data in "differences" that have differences in the two month sheets. As you can see number 500 has no changes between month 1 and 2 and there for is not in the differences sheet.

    What I need is a way to determine what data should be in column "A" and what data not.

    When I got that right I will use the lookup funtions to fille out the list.
    I think a pivot table might work as well but I dont know how.

    Regards Marcel

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

    Re: Compare 2 lists of data (2002 SP3)

    I don't think you can do this without code. It would be more appropriate to do this in Access, where you can use a series of queries to obtain the result you want.

  11. #11
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Compare 2 lists of data (2002 SP3)

    I thought about access but I don't know if that is available.

    Thanks for the help.

    Regards Marcel

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

    Re: Compare 2 lists of data (2002 SP3)

    The attached version contains a button that will run a macro to create the list of differences.
    Attached Files Attached Files

  13. #13
    Star Lounger
    Join Date
    Jan 2004
    Location
    Westervoort, Netherlands
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Compare 2 lists of data (2002 SP3)

    Hi Hans,

    Thanks for the code, but I am sorry to say that it has errors. But before you start updating, there was a specific reason why I did not want code. My knowledge of VBA is as follows: I record a macro and then fine tune the parts I understand which is not very much.

    My current job is financial professional. I am sent to different companies all the time. So when I want to create a workbook to make thing easier I need to be able to make it as full proof as possible since I won't be with this company for long. I cannot have the situation that when I am gone the workbook does not work anymore.

    Since my understanding of VBA is so little I think the risk of that happening will be great.

    The sheet I sent was just the basics. The source data for month 1 and 2 has more columns.
    The output data for the sheet differences is also very basic. The next step would be to interpret the data and tell why there is a difference.
    My expectation is that it would be better to stop here and first write down the compete story or risk rewriting code over and over again.

    So again thanks for the help.

    Since you live in the Netherlands can you give me advise for a good VBA course?

    Regards Marcel

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

    Re: Compare 2 lists of data (2002 SP3)

    I'm afraid I don't know which companies offer good Excel VBA courses. If you'd like to learn from a book, I'd recommend those by John Walkenbach.

    And I fear that what you want cannot be accomplished by formulas alone, or if it is possible, it'll require very complicated formulas that are easy to mess up.

Posting Permissions

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