Copy Matching Data From 2 Worksheets

  • I have a workbook with 3 sheets. AP, Move, Match. AP & Move will be about 40,000 lines.
    I need a macro that will take the data from those two sheets and match them up on the match sheet by column A Line by line with all data moving over. An example is attached.
    I'm not sure if this is possible, but please let me know if you need more info because I really don't want to do this manually. Anything that doesn't match I just need a blank line. I can add the true-false formula, but if it would fit in the macro that would be cool to.


    Thank you very much for your time and help.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Does anyone have suggestions as to how I could accomplish this matching? I'm not good enough with vba to do it on my own.
    I'm open to ideas.
    Thank you for your time and help.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]bump

  • Re: Macro To Match Data Line By Line


    You mean if cell A1 is the same on both sheets AP and Move it means those lines are the same, and you want one of those lines (either, since theyre the same?) copied into match? If the lines dont match, just leave a blank line?


    if I got that right:


  • Re: Macro To Match Data Line By Line


    Hi,


    The macro you are descibing sounds like a simple job, but if this is a one-time mapping (?) then I would instead of writing a procedure just use VLOOKUP from sheet AP to Move and vice versa to get matches both ways, then add the lists together and filter for uniques. The ones that don't show up on that list don't have a match.

  • Re: Macro To Match Data Line By Line


    Quote

    You mean if cell A1 is the same on both sheets AP and Move it means those lines are the same, and you want one of those lines (either, since theyre the same?) copied into match? If the lines dont match, just leave a blank line?


    No, If A1 is the same then I just want them next to each other on the match sheet separated out by a column (E). If no match still display in match sheet. I don't know how else to explain, sorry. Check out my example. Basically, all data from both sheets will be on the match sheet, I just need ones that match by A next to each other.


    Quote

    The macro you are descibing sounds like a simple job, but if this is a one-time mapping (?) then I would instead of writing a procedure just use VLOOKUP from sheet AP to Move and vice versa to get matches both ways, then add the lists together and filter for uniques. The ones that don't show up on that list don't have a match.


    I was hoping for a macro because this is atleast a twice a month routine on this sheet. Plus, I was hoping to edit the macro to use on 5 different sheets. Would be a great time saver. Currently, I have been doing the lookups and such, But before I even do all that I have to other formats like columns A to remove spaces... etc. It's a time consumer. especially since sheets are so large.
    Thank you both for your replies.

  • Re: Copy Matching Data From 2 Worksheets


    I was hoping for a button that would run the procedure, and do all the formatting, so others can easily handle the sheets.

  • Re: Copy Matching Data From 2 Worksheets


    How about if you tell us all the requirements (regarding formatting etc.) you have for this macro, perhaps that'll help us evaluate what would be the best solution for your case?

  • Re: Copy Matching Data From 2 Worksheets


    This is the code I was able to come up with. It does everything except at the end I need to delete all rows that are not empty in Col A. from "this" row down. The trick is "this" row is hard to describe. It is the last used cell in I then ctrl + up arrow.
    So, from that row down any cells in A that are not empty I need deleted. Then macro will work perfect. Except I had to use alot of the recorder and it is very slow, but it works.


    [hr]*[/hr] Auto Merged Post;[dl]*[/dl]

    Quote from Fencliff

    How about if you tell us all the requirements (regarding formatting etc.) you have for this macro, perhaps that'll help us evaluate what would be the best solution for your case?


    If you look at the example, thats all I need. I need all the data from ap & move sheets on the match sheet. AP can be in columns A:D, Column E needs to be =A1=F1, Columns F:I need to be move data. I need all the true's from a1=f1 at top then all falses stacked at bottom. So, we need to vlookup up in the data and delete duplicate data. Before the lookup however spaces need to be removed from each of the A columns in the sheets. If I were to total columns D:I in Match they would need to equal totals in AP & Move. Also clear out #N/A's from the vlookup. And format all columns that need to be dates as dates and dollars as dollars.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]another example

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!