+ Reply to Thread
Results 1 to 80 of 80

Compare Two Sheets - Remove and Append (Master and New data)

  1. #1
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Compare Two Sheets - Remove and Append (Master and New data)

    Hey all! I have two sheets. The main sheet is my Master sheet that I work with daily, and the second sheet is a newly exported data which contains new lines and some removed lines.

    I have Sheets:

    Current
    New

    Current is the working sheet. I need to compare Column A in "Current" sheet and "New" sheet.

    Firstly, any data existing in "Current" Column A but NOT existing in "New" Column A - the whole row should be deleted from "Current"
    Second, any data existing in "New" Column A but NOT existing in "Current Column A - that whole row should be appended at the end of "Current"

    I found some code online but it threw errors or crashed my Excel. I have attached spreadsheet for reference. My actual file has 3k + lines..I removed all but 10 lines for the test file. Thanks in advance!
    Attached Files Attached Files
    Last edited by NewYears1978; 07-23-2017 at 01:03 AM.

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    This runs once

    The commented out code has always run before

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Quote Originally Posted by xladept View Post
    This runs once

    The commented out code has always run before
    Were you asking about my commented out code? If so then I yes, the COMBINE button at the top...used to do a bunch of stuff...you can see how poor I am at excel I had to macro a lot of stuff as if I was manually doing it. I am not a programmer lol.
    It worked, but it didn't remove the old non existing values so I ended up with lots of lines that shouldn't be there which is why I decided to rewrite the code. And your script...worked perfect and was way faster lol!

    Or did I misunderstand what you were asking me?

    Thank you! I can't rep you again currently..will try again later ;p

    You can ignore everything below, I figured it out..but I left it because it's funny lol.
    I removed all the old code, updated the code..and now I get a compile error on my previously working scripts..no clue what it means

    Please Login or Register  to view this content.
    "Expected variable or procedure, not module" - Only thing I changed was removing old code and renaming my modules..weird.

    Also what causes my macros to be listed like this (See image)

    Here's my udpated file..my COMBINE (button on top) macro is the builty broken one..and also the CreateBackup macro isn't working anymore:
    https://www.dropbox.com/s/m7kg58ypq3...List.xlsm?dl=0

    Haha - well nevermind - apparently if you name modules the same name as the sub - everything breaks. Renaming them to something unique fixed all the errors above.
    Attached Images Attached Images
    Last edited by NewYears1978; 07-22-2017 at 11:06 PM.

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    I'm thinking that there's still something wrong with my program since it will delete more rows on a second run.

    I can't understand why the U.EntireRow.Delete doesn't run for this spreadsheet only. It's still in progress;

    I was hoping that you could ferret out a reason for this

  5. #5
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Oh darn..I didn't realize it doesn't work lol I just tweaked all my stuff and was about to post my full macro version so you could see why it is so slow and if there was a way to clean up my code.

    It did work but I haven't tried to run the code more than once.

    I only run this code like once a week will it work upon saving, closing and reopening?

    I have two issues (aside from the problem you are stating is happening)
    It's slow (not your code..my code lol) Not sure why my code is so slow ..all I am doing is copy and pasting a sheet to another sheet.. (maybe it's the VLOOKUPS..maybe some way I can solve that not sure)

    Second problem is when it adds new lines to my Current main sheet..for some reason it is adding VLOOKUP formulas to columns R-T (see example) - no clue why...

    https://www.dropbox.com/s/wefqgcrk12...Test.xlsm?dl=0

    (wondering if my copy paste code is trying to copy like 100,000 lines instead of just the 3k lines of data..that might be why it is bogging down - but oddly if I run just the CombineStep1 macro it's instant..but when I run the macro as a whole it takes like several minutes..no clue why)

    Gah- all my VLOOKUPs are broken now too... (it probably just has to do with formatting..I forgot to bring home a sample file of my exported data from work..so I need to skip the import part til I have that file from work - why are vlookups not working unless I have numbers stored as text..that makes no sense!)

    UGH - I hate excel
    Last edited by NewYears1978; 07-23-2017 at 12:00 AM.

  6. #6
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Re: I can't attach file due to file size
    Make it smaller. People that can and are willing to help don't need 3000 lines to test.
    Make it representative of your workbook with a before and after.
    Good luck

  7. #7
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Quote Originally Posted by jolivanes View Post
    Re: I can't attach file due to file size
    Make it smaller. People that can and are willing to help don't need 3000 lines to test.
    Make it representative of your workbook with a before and after.
    Good luck
    Yeah the problem is I need to test the macro with lots of lines because it tends to act wonky, and for some reason when I remove lines and try to just use "dummy" lines it breaks my VLOOKUP code. They only seem to work with the exported numbers as text (I have no clue why). If I manually type in some new lines for testing those VLOOKUP stop working..very odd.

    xladept was helping me with the file I pasted on Dropbox though without complaints. Thanks for your post! I will try to post another with less lines.

    Currently his code is not working properly..it works once but after than then it acts very sporadically for some reason. I've attached a shorter file. (I tested his code in the shorter file and it got stuck in a loop some how)

    (If my posts are not cohesive..I apologize..been working on this all day and also spent 5 hours fixing a fence in 100 degree weather so I am exhausted)
    Attached Files Attached Files
    Last edited by NewYears1978; 07-23-2017 at 12:53 AM.

  8. #8
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    I figured it out - the one run gave the right results - the Union must have retained some fields - since the block deletion didn't work it's been removed - use this version:

    Please Login or Register  to view this content.
    You can run it as many times as you want!
    Last edited by xladept; 07-23-2017 at 11:13 AM. Reason: Details:)

  9. #9
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Those vlookups must be already there because the program only transfers 16 fields since R,S,T are 18-20

    EDIT: It's got something to do with the table?????? - BOTable
    Last edited by xladept; 07-23-2017 at 12:15 PM.

  10. #10
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    [Previous msg deleted, too confusing]

    Thank you so much. It works, I just need minor adjustment - is there a way to make it copy ONLY the value from COLUMN A when adding new rows to "Current"? This way it keeps my VLOOKUP formulas from the lines above it in the table.

    Lastly I will just need to add in my macros to convert numbers stored as text to numbers (because that is fouling up my VLOOKUPs) (Which I have sorted out already)

    Edit:
    Also, I added 4 new items, and I had to run the script 4 times, it only added one line each time??

    Added the 3 dupes lines below the Mammoth line
    Please Login or Register  to view this content.
    Had to run code three times to add the b, c, and d lines
    Last edited by NewYears1978; 07-23-2017 at 01:29 PM.

  11. #11
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Hopefully:

    Please Login or Register  to view this content.
    And, thanks for the rep!

    BTW - The table still fills??
    Last edited by xladept; 07-23-2017 at 01:52 PM.

  12. #12
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Quote Originally Posted by xladept View Post
    Hopefully:

    Please Login or Register  to view this content.
    And, thanks for the rep!

    BTW - The table still fills??

    Yeah no clue what's up with that..I think it's thinking it's supposed to because of the VLOOKUP in other columns..probably Excel causing it...weird. Will test new code

    What's weird is it is putting VLOOKUP in columns R-T but it is not putting it in ColumnB or ColumnM (hidden column) but it is in the other columns... lol what the heck excel. Grrr Also formatting is always a problem it never wants to retain formatting when expanding the column.
    Last edited by NewYears1978; 07-23-2017 at 02:16 PM.

  13. #13
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    I have corrected the above errors by converting my table to a range then back to a table

    I think it is now working 100% Needs testing when I get to work tomorrow with an actual data table - but I think we've got it!

  14. #14
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    If it's too slow we can try the block deletion again - it probably doesn't work because of the table

  15. #15
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    I am using it with 3k lines right now and it seems to be instant.

    My files are usually 3k lines and the changes each time I run the code may only be 5-10 lines so it shouldn't bog down. I will monitor, test, and let you know. Woo thanks again!

  16. #16
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    With the block deletion - Original Version with correction:

    Please Login or Register  to view this content.
    *Well - if you get to about 50k lines it would matter but Machts Nichts with less than 10k lines
    Last edited by xladept; 07-23-2017 at 02:39 PM.

  17. #17
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Nah it should never really exceed around 3k lines.

    This is actually all the accounts I manage at work. But I am a commercial Account Manager and a lot of the new ones I get are residential and thus get dropped off my list I should hover around 3k all the time (Or less, which is part of why I am setting up this spreadsheet so I can start removing accounts that I don't need like 1 time purchase $30 order accounts etc)

    Should cut my list down dramatically. And now I can track things properly and keep it all updated.

  18. #18
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Excellent!

  19. #19
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Can I ask you another question. The code is working perfectly..however something going on with my own code is acting really wonky. When it does a simple delete from one sheet, copy range from another sheet, paste that range..it really bogs down.

    There must be a better way.

    See the first few line
    Please Login or Register  to view this content.

    This really bogs down..no clue why..it runs instant the first few times but then it starts to act wonky (I suspect it is also adding lots of blank lines to the sheets possibly)
    (Maybe this should be a new thread)

    (It may not even be the copy code specifically..everything seems to bog down after I've run some macros a few times..almost like a memory leak or something..not sure what the cause is)
    Last edited by NewYears1978; 07-25-2017 at 12:27 PM.

  20. #20
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Well in the code:

    Please Login or Register  to view this content.
    DeleteNew2 is not an acceptable action - you could replace it with Selection.Delete - but here is what I would write:

    Please Login or Register  to view this content.

  21. #21
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    DeleteNew2 is another code ref somewhere else to delete that tab (Which is probably not the best way to delete a tab either lol)

    Please Login or Register  to view this content.
    But I see you have addressed that already, I will try the code see if it helps Thanks again!

    Edit: It worked instant - only problem is that it did not convert my text to numbers so all my VLOOKUP broke..hrm - will work on that now. Seems like the ConvertAB and ConvertN are no longer working right.

    Please Login or Register  to view this content.
    If I run them manually then all my VLOOKUPs are fixed..so it's just not running them properly in the code. Ah.. I see why..it's running them on New2 not New (I need to select that sheet after copy..or tell it to run on those sheets)

    Yay.
    Last edited by NewYears1978; 07-25-2017 at 01:24 PM.

  22. #22
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Probably the conversion routines need to be adapted

    BTW:

    Please Login or Register  to view this content.
    Last edited by xladept; 07-25-2017 at 01:26 PM.

  23. #23
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    You can see I am not a coder..but I really want to learn because my hackjob way is obviously not working.

    With your changes my macro now runs completely instant..in like 1-2 seconds. No pause, no delay, no wonkiness.

    Thanks!!

    Should I be using "Call SubName" instead of just "SubName" in all instances?

    Please Login or Register  to view this content.
    I am so slow that I put

    Please Login or Register  to view this content.
    when I could just put

    Please Login or Register  to view this content.
    Or maybe not..that throws error when run in the macro..haha. I am so newb.
    Last edited by NewYears1978; 07-25-2017 at 01:41 PM.

  24. #24
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Yeah - technically you don't need the word Call with less than 2 arguments but I've found that it's best to always use it

  25. #25
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Definitely cleaner, helps to see where I am calling subs rather than just a bunch of jumbled text. I need to go over everything else there is probably a lot more I can clean up.

    How come even though Application.ScreenUpdating = False is set, I can still see my sheet changing tabs and clicking things and stuff?
    (Ahh I got it working..I had a =True hidden in there that was turning it back on)
    Last edited by NewYears1978; 07-25-2017 at 02:01 PM.

  26. #26
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    When using select there's always plenty to edit - the recorder cleanup is the most efficient hands on method for learning - at least to the intermediate stage - programming affords many creative opportunities - you're doing now what we all have done - practically everyone of us is self-taught and this forum has taught me so much - and I had already written a book before I ever even knew about this forum

  27. #27
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Aw thanks

    I will get there, so much to learn especially when I don't use it all the time. Just for projects at work.

    The code runs FLAWLESS now. My whole macro is instant...handles 3k lines of combining with no hitch at all, nothing visible is completely instant. LOVE IT.

  28. #28
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Great, glad to hear it - isn't it a grand feeling

  29. #29
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    For some reason when I tried to do this with other people lists (where it has to process 3k new lines and remove 3k lines) it is fouling up. It seems to skip line one (as in it must be removing line one first) then appending on line two..and therefore what happens is it doesn't extend the table for some reason...and breaks and I end up with only column A of data.

    Does that make sense?

    So basically since all items int he list are going to be removed (since it is another reps customer list) it removes all lines including line one, then some how it starts appending in A3 instead of A2, thus not expanding the table thus not dropping down my Vlookups.

  30. #30
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Look at the difference between the other people lists and what you were using. Is it my program not working?

  31. #31
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    The program works it's just because with my list it is not removing all the items. With theirs because its all new list of data, it's removing ALL the data first which removes the VLOOKUPS.

    Basically I need to keep one line in tact so that the formulas are not lost when the table is expanded. Maybe I can make A new row on A2 which is only a the formulas...then redo your code to start lookin at from A3 instead of A2?

    Does what I am saying make sense? Currently in my form I have Vlookups in all columns after column A, when items are appended it expands the table keeping those formulas but when it has to remove ALL data from rows 2 on, I lose those formulas and just end up with a column A of numbers and then the rest are blank.

  32. #32
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Let's see the code you're talking about.

  33. #33
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    It's your code - unless I misunderstood the question.

    I know one way to fix it is to add a common line in so that one line won't be deleted on import..

    The way to duplicate it is in the New tab put all new items in there that are not in the current tab..that should create the result I am referring to. (I can upload a small sample if I need to I will make one real quick)

    This error would only happen ONCE on initial run, just to get each person's accounts in the list. After that it would be fine every time. Am I making any sense? lol :D
    Last edited by NewYears1978; 07-26-2017 at 02:39 PM.

  34. #34
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    I guess that I need to see the new New tab

  35. #35
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    I will make a sample once this import stops..it takes 5-10 mins to do so I am just waiting on it to finish (I put a common line in just to see if it worked as I expected)

    Did you understand my explanation? I know I am being confusing..in my head it makes sense.

  36. #36
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Communication is so difficult I'm often at a loss for words

  37. #37
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Haha yeah it is, in text form. It's still running talk about sloooooooow.

    It seems to spaz out, I may have to make each persons first file manually.

  38. #38
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Okay here is the sample, I have 9 items in Current tab and New tab (that are all different numbers) Run the NewYearsCombine macro and you'll see what is happening.
    Attached Files Attached Files

  39. #39
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    The entire current data is being deleted - perhaps putting a dummy record in that will never be deleted will solve this unexpected result!

    *As has often been noted, I'm out to lunch(!) - Later
    Last edited by xladept; 07-26-2017 at 04:32 PM. Reason: grammar

  40. #40
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Changed my mind - decided to eat right here. What shall we do about the "a" tag on the Customer number?

  41. #41
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    The dummy tag is what I thought also..or the other option is manually inputting all the values from their column one initially. Which works, have tested, the only issue is that I would probably have to do it for everyone. Oh well might be the easier route to go - dunno another way.

    I guess just adding into my macro to add in the dummy line to Current and New tabs before it runs the combine - would solve the issue.
    Last edited by NewYears1978; 07-26-2017 at 05:05 PM.

  42. #42
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    I have a solution that depends upon the Customer Number in The Current Sheet to always be numeric - that's the way the routine is written - is that the way it will always be??

  43. #43
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Yes it will always be numeric..I added the letters in just for the example (appended to existing numbers so I didn't have to make up numbers)

    Hahah I just realized that my table was expanded to XFE column..don't know when I did that but no wonder my file started going really slow. The one I attached also has them..might be causing you problem you might delete them before you run any tests.
    Last edited by NewYears1978; 07-26-2017 at 05:27 PM.

  44. #44
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Don't need to try this:

    Please Login or Register  to view this content.

    Thanks for the rep!

  45. #45
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Gave me an error on
    Please Login or Register  to view this content.
    on the S = Left part

    Says Invalid procedure call or argument
    Last edited by NewYears1978; 07-26-2017 at 05:39 PM.

  46. #46
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    It ran really slick for me

  47. #47
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Maybe if we break it up:

    Please Login or Register  to view this content.

  48. #48
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Is there supposed to be an End If in the original code after the Loop?

    Tried the new way though same error...hmmm.

    Runtime error '5'

    https://support.microsoft.com/en-us/...right-function
    Last edited by NewYears1978; 07-26-2017 at 05:46 PM.

  49. #49
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    If it blows this one off - back to the drawing board

    Please Login or Register  to view this content.

  50. #50
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Same error on the mid..

    Why would it work for you but not here that is weird. The only diff is I am working with a full file of 3k lines but that shouldn't matter.

    Excel 2010 btw.

  51. #51
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    The only thing I can think of is that you're missing a reference. In the VBE click on tools and see if it has anything labeled as missing

  52. #52
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Not that I can tell?
    Attached Images Attached Images

  53. #53
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    I repeat - but check this out.


    The only thing I can think of is that you're missing a reference. In the VBE click on tools and see if it has anything labeled as missing

  54. #54
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    I thought of something else - maybe you only copy pasted the bottom half of the program - if so the dimensions on the top need to have:
    Please Login or Register  to view this content.
    I hope that's it
    Last edited by xladept; 07-26-2017 at 09:54 PM.

  55. #55
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    I used the copy button so it should have copied it all?

    Oddly - at home I don't get an error..but nothing happens when I run it o.O

    (Oh..it's because I used letters..woops)

    So..weird then..no error at home. However, it just removed all the lines but didn't append the new ones. (see attachment)

    Also this error ONLY occurs if ALL lines are being replaced. If I run the macro whereas all the numbers are the same or only some are the same - it doesn't error (I guess the Left code isn't being triggered is why?)

    At a loss. Could it be something to do with passing a negative number with the Left function..I read a lot online about Left, Mid etc not working with 0 or negative values.
    Attached Files Attached Files
    Last edited by NewYears1978; 07-27-2017 at 09:55 AM.

  56. #56
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    You've got 7 digits in the Current but eight digits in the New - they can never match?? What shall we do??

  57. #57
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Oh..yeah they don't match because they are sequential numbers starting from years ago til now. So some have 4, 5, 6, 7 etc.

    So when I made them match in number, I no longer get an error on the Left..however it's doing what I stated earlier, it just deletes all the lines and never appends the new ones. (Won't work anyway because I have to be able to have diff length numbers)

    Ahhhhhhhhh so frustrating

    Might just have to go back to adding in a matching line to Current and New in my macro so that it keeps one line.
    Last edited by NewYears1978; 07-27-2017 at 11:22 AM.

  58. #58
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    There must be a field that matches we could match on all but the last digit - would that work?

  59. #59
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    I didn't understand that lol - I am slow.

    I think if you don't use the whole number there's a chance for some mismatches.

    It might be easier to just write a small procedure that adds a dummy line at the end of Current and New right?

  60. #60
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Well, if your string functions don't work, there's little we can do

  61. #61
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    I think just manually adding a like line in the macro will work..I am testing it now. Too bad it takes 5-10 mins on first run since it is removing 3k lines and adding 3k lines.

    No clue why the strings are not working..everything else has worked fine - no missing items in ref.


    Adding the like item didn't work either it still started on line 3 and didn't copy the formulas down ugh.

    I tried that though and it wasn't working right either. It seems like any time I replace all the lines or most the lines it acts up..no clue what is happening...I might just have to manually make everyone's first file and be done with it!
    Last edited by NewYears1978; 07-27-2017 at 11:55 AM.

  62. #62
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    So I think the solution - the one that seems to work every time is when starting fresh for another of the reps here.

    On Current - I start with just ONE line. I import their file in and then I just add that same ONE line from Current, into New. Then I run the macro - then it seems to work - and is fast because it is not having to remove.

    I think this is the working solution. So I just need to update my macro to add that one line to "New" and all SHOULD work.

  63. #63
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    But, you'll end up with Customer numbers never matching - maybe add another field where the numbers will match?

  64. #64
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    What do you mean they'll never match? I'm using your original formula before we added the Left stuff. Seems to work flawless too (of course, need more testing)
    Last edited by NewYears1978; 07-27-2017 at 12:16 PM.

  65. #65
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    If you don't have the same number of digits you don't have the same number, or am I missing some point?

  66. #66
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Is this not right?
    Please Login or Register  to view this content.
    For an activeX (control) checkbox? Keeps throwing error "Object Required"

    Quote Originally Posted by xladept View Post
    If you don't have the same number of digits you don't have the same number, or am I missing some point?
    Are you telling me the original code you wrote only matches values with same number digits? Doesn't appear that way..seems to be working.

  67. #67
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Try:

    Please Login or Register  to view this content.

  68. #68
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    I thought that was for form controls?

    Tried it anyway and it errored. Waaaaaaaaaaaaaaah

  69. #69
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    I got it to work I had to put
    Please Login or Register  to view this content.
    Everything is working now too. I put a first run checkbox in here. I started with a blank form with only one line in it. If the user clicks the "First Run" box it then imports there data in, and it appends one line matching the one line I have in there.

    Then all it does is append all their data in. Uncheck the box and continue from there. Works perfect now (as far as I can tell!) The next time they import there data in it will get rid of the one dummy line.

    Yay!
    Last edited by NewYears1978; 07-27-2017 at 02:06 PM.

  70. #70
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Excellent! Hooray! I'm really glad to hear that as I was contemplating writing a "Finder" routine

  71. #71
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    I don't know what that is but it sounds scary.

  72. #72
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Hey xladept - so far so good..have tested with 3 other coworkers and seems to be working! Woo!

    I had one other thing I forgot to ask about. Would it be hard to add in code to store in a variable the number of lines removed and number of lines added? Is not necessary..just a little extra fluff if it's not hard to add Using the original code that is

    Please Login or Register  to view this content.

  73. #73
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Where would you want it?

  74. #74
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    I'd add it to the message box at the end of my full code, on a second line. "# records were deleted, # records were added"
    Please Login or Register  to view this content.
    Not sure if variables can continue across macros or not? (or are they dumped after the sub is run?)

  75. #75
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    If you put these dimensions before the code for the routine and after the "Option Explicit"

    Please Login or Register  to view this content.
    Then with your msgbox:

    Please Login or Register  to view this content.

  76. #76
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Thanks Testing!

    Guess I am confused where the two new lines go - it ran but the msg box didn't show the values.

    here's the two modules

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Last edited by NewYears1978; 08-03-2017 at 09:29 AM.

  77. #77
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    I think I got it..had to add the vars to the other module instead of the combine module for some reason.

    Eh, nevermind it always just returns 0

    Andddd I got it working. Had to combine the code from both modules in the same module. Works now. Sweet thanks
    Last edited by NewYears1978; 08-03-2017 at 10:09 AM.

  78. #78
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Excellent!

  79. #79
    Forum Contributor NewYears1978's Avatar
    Join Date
    03-15-2010
    Location
    Forney, TX
    MS-Off Ver
    Excel 2016
    Posts
    962

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Everything is working smoooooth now. You are the best.

  80. #80
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Compare Two Sheets - Remove and Append (Master and New data)

    Wow - glad to hear it

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Append sheets to MASTER sheet retaining chart but with series updated?
    By rickfraser in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-27-2016, 11:47 AM
  2. [SOLVED] Saving MASTER file, automatically remove part of file name and append current date?
    By EnigmaMatter in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-10-2014, 08:13 AM
  3. [SOLVED] Import data from excel files to append master file
    By kdawgpl9 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-19-2013, 04:32 PM
  4. [SOLVED] Compare Col A across 2 Sheets in different Workbooks, Copy Matches to MASTER list, H/L NON
    By ali.whitaker in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-09-2012, 04:49 AM
  5. [SOLVED] Compare 2 cells in 2 sheets, if both match append data in Sheet2 to Sheet1
    By furious0331 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-29-2012, 01:05 AM
  6. How to Append the Data to the Master Table
    By Shiva in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-08-2005, 01:00 AM
  7. how do i append 4 differnt worksheets in a new master work sheets
    By darsg in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-28-2005, 05:06 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1