1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: VBA Compare data columns

Discussion in 'Business Applications' started by maxpowerdiaz, Apr 29, 2014.

Thread Status:
Not open for further replies.
Advertisement
  1. maxpowerdiaz

    maxpowerdiaz Thread Starter

    Joined:
    Aug 21, 2012
    Messages:
    43
    I've been at this for what seems forever so any help would be greatly appreciated!

    I want to compare data from one sheet to another and for any exceptions (non matches) I want it to copy and paste the entire row into a 3rd sheet.

    I've searched and found various codes about comparing columns, but my need seems to be a little more complex. I want it to compare 2 columns on sheet 1 (columns A & B) against 2 columns on sheet 2 (columns C & D) and it does not what row they are in. I also would like the sheet name to be included in the results.

    I've attached a sample book with the data. Sheet3 shows what data should generate from this code.

    These would be the results because the names (both first and last) do not appear on both sheets. I dont have a lot of experience with code so any help would be great.

    Thank you!
     

    Attached Files:

  2. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    I've taken your spread sheet and produced the results your were expecting. Not being a vba excel macro wiz I took a different approach which might match your skill level better any way.

    Quick and dirty explanation
    1 Concatenated the names on both sheets
    2. Did Vlookup of Names to other sheet
    3. Evaluated Vlookup for error
    4. Sheet3 Checks the error evaluation and populates data if Vlookup failed.

    Going forward you can copy and paste(value only) sheet3 to a new sheet, sort by Column A, Delete all the "Good" and you have your requested answer.

    The formulas should copy and fill down to support more data.
    I have another similar approach if you need to keep sheets one and 2 "Clean" for some reason.
     

    Attached Files:

  3. maxpowerdiaz

    maxpowerdiaz Thread Starter

    Joined:
    Aug 21, 2012
    Messages:
    43
    Hi Dan,

    Thank you for taking a look into this. I wont be able to use this solution since my actual data will comprise of numbers (instead of first and last names). I also would prefer to keep this in code since the length of data will vary from time to time.
     
  4. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    I don't quit understand your response. Vlookup works on numbers and not sure what length as to do with anything? Are you referring to rows? Perhaps posting more realistic data will help the next person that takes a stab.
     
  5. maxpowerdiaz

    maxpowerdiaz Thread Starter

    Joined:
    Aug 21, 2012
    Messages:
    43
    Sorry, i meant it wouldnt work because part of your process was concatenated the two cells. So I didnt think it would work to do that when its not actually names.

    Yes i was referring to the length of rows. I was hoping to get this in vba rather than using the vlookup so it would be easier on the user. The user would need to perform the function for each report (there are several).

    I've updated the workbook to reflect more accurately the data i'm working with. Thank you
     

    Attached Files:

  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,550
    First Name:
    Hans
    Hi Max,
    I think I get the idea, and yes and my prefrence is a macro that does that.
    I think I can put it togetehr, I don'r think it's that hard but just one thing.
    Just to make sure; can it happen that an identical combination occurs more than once in one and the same sheet?
     
  7. maxpowerdiaz

    maxpowerdiaz Thread Starter

    Joined:
    Aug 21, 2012
    Messages:
    43
    Hi Hans,

    Thats a very good question. One i hadnt considered. Yes it is possible, but very unlikely that an identical combination will happen more than once.
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,550
    First Name:
    Hans
    This is just a quick one, No check on duplicates but it works

    The macro is called CompareSheets
    If you rerun the macro and Sheet3 is not empty it will duplicate the output.
    This is another option to consider and include in the code
     

    Attached Files:

  9. maxpowerdiaz

    maxpowerdiaz Thread Starter

    Joined:
    Aug 21, 2012
    Messages:
    43
    Looks great! Thanks Hans, is is exactly what i was looking for.
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,550
    First Name:
    Hans
    I added the part that also checks the results sheet to avoid duplicates.
    If you run the same macro more than once the result remains 3 rows
     

    Attached Files:

  11. melaniemilwee

    melaniemilwee

    Joined:
    May 5, 2014
    Messages:
    1
    I was searching for something similar to use - took over as band booster president, have all of these excel sheets with a ton of info that need to be cleaned up, consolidated, or deleted and my code writing is rusty. So a big thanks! ~
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1125060

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice