I have a need to move/copy rows from one sheet to another IF they dont already exist..

[cubangt]

So i currently have a report that i process some raw data from a external system.
Using AdvancedFilter i copy what is needed from the raw data into the report into a "Clean" tab ( This is where i plan on doing all the manipulations needed) before moving it to the final sheet that the reports and graphs are pulling from.

My ask is i need to ONLY move records from the "Clean" tab to the "Final" tab if they don't already exist in that tab. (there is 1 key column that contains a alphanumeric value that is unique)

What I'm looking for is some direction on what is best or most efficient to look in the "Final" tab at those values and then ONLY copy rows from "Clean" that do not exist.

im currently using this in another report and wondering if this is the way to go, if this would work, what are the areas i would need to focus on changing in order to loop thru all existing values in the "Final" and copy only ones that dont exist from the clean.

Please Login or Register  to view this content.

Tabs:
"Final" = historical/current records (Unique Column Value is alphanumeric, ex. PHH290483)
"Clean" = Todays/Weekly report records (Also has the same unique value)

Once i have run any clean up on the Clean tab, then i would like to copy any records from the "Clean" tab into the "Final" tab that DO NOT exist in the "Final"


Im working on cleaning up the current file, but may take a bit to get that ready..
So the ask is more on how can i modify the above array copy paste to have that condition so that the array that is built is only the one that do not exist.

[maniacb]

One way to do what you ask is to put all the unique items from the final tab into a dictionary and then loop through the other tab only copying over what is not in the dictionary. We can assist if you provide a sample file as per the yellow banner above.

[cubangt]

Wouldnt a Array work just as well?

I was able to put that list into an array with just these 2 lines and it contains all unique records from the "Final" tab

Please Login or Register  to view this content.

As stated not sure how long it would take to clean up the report to allow me to upload it, thats why this was more of a question on what methods to consider and use versus how to actually write the code..
Once i get to that part of testing the logic, that will be in a test file, so that one i can upload if i end up getting stuck..

[maniacb]

You could use an array, you’ll just need to write code to check each item in the code through a loop. The dictionary method has a built in method (.exists) that can be used without running through a loop.

[cubangt]

You could use an array, you’ll just need to write code to check each item in the code through a loop. The dictionary method has a built in method (.exists) that can be used without running through a loop.

Ah i see, so with that method, then basically instead of a array of 100 records, it could literally be just a dictionary of say 5 records that dont exist?

[maniacb]

It’ll still check the 100 records, but the list is already in memory, so your code would be : if it doesn’t exists then add the row.

[cubangt]

ok, cool i switched over to this code to get my dictionary and will write the copy logic and see if i can get it to work. But i get a Application-defined or object-defined error

Please Login or Register  to view this content.

So just to be clear, this is what i would need to do:

#1 = create dictionary of unique values from "Final"
#2 = Loop throw all rows in "Clean" checking if the unique value does not exist in the dictionary, if it does not then copy to new sheet.

[cubangt]

I fixed the error and using this now:

Please Login or Register  to view this content.

Weird thing is that my MsgBox never gets triggered. in my records, there are plenty of records that exist and not exists.. and the "chg" variable is being populated correctly with the unique values..

[cubangt]

I changed the above to this and trying to see if the record counts are accurate and they are not..

Please Login or Register  to view this content.

Here are the record counts and what should be found as NOT exists:
Final 62 (This is the dictionary total)--- I BELIEVE
Clean 72 (This is how many rows are on my clean tab that i need to check against the Final tab)
Not Exists 39 (Manually checking for duplicates between the 2 lists, this is how many records are on the "Clean" tab that DO NOT exists on the "Final" tab

Even though im not getting an error, i dont think that my dictionary has the 62 records..

[maniacb]

Does it make a difference if you change this line:

Please Login or Register  to view this content.

You can also get the number of items in the dictionary with

Please Login or Register  to view this content.

[maniacb]

After reviewing the code above, I don't think you can assign a range directly to a dictionary, at least, that's what I've read. to build the dictionary, I would first assign the range to an array and loop through the array to add each item to the dictionary. I updated the code with this approach.

Please Login or Register  to view this content.

[cubangt]

I just tried and got a subscript out of range on this line:
dict.Add a(i, 1), k

And when i check the range assignment to "a" it only has 2 records, the column header and the first value.

[cubangt]

Fix the subscript error

before
dict.Add a(i, 1), k

after
dict.Add a(j, 1), k

But now that its working and populating the array and dictionary, im getting a "the key is already associated with an element of this collection"

Since im only pulling in 1 column of data to create my dictionary, is there anyway around this?

Im waiting to hear back from management on if this is truely suppose to be a unique column value, if it is then someone added a duplicate not knowing.. If its not unique, then ill have to create a 2d dictionary so that each value has a unique key

[cubangt]

So there should NOT be any duplicates, and since these were manually entered by someone, they could have just missed the first record and inserted the 2nd.. (the whole reason for trying to automate this)
I updated the code to the following and now have an error that wasnt there before..

Please Login or Register  to view this content.

I change this portion to exclude any values from the dictionary that include CTASK and in the debug i see all the values i want in the dict, but when it gets to the NumRows line it throws an error

New Change to exclude:

Please Login or Register  to view this content.

On this line i receive "Application-defined or object-defined error

Please Login or Register  to view this content.

If i remove the If InStr logic and just write everything to the dict is when i get the key already exists in collection..

Seems i need to clean up the range values before creating the array? Maybe?

[Marc L]

Hi,

again the classic Range trap beginner error : you just forgot to qualify the worksheet for the second range …

[cubangt]

I made a change so that my array that is being added to the dict only contains the valid list of values like so:

Please Login or Register  to view this content.

But im not getting the correct number of values expected..
So before i start copying data from sheet to sheet, im trying to get the counts to match the expected..

Here is what im ending up with:
Plan 49 So this is the Main list of records that im checking against
Clean 72 This is the number of records pulled in the report at a given interval(todays run)
Clean NOT in Plan 39 This is the number of records out of the 72 that DO NOT exist in the lift of 49
When i run the above that last set of debug.print count is returning 23 and 25

Im almost there, if i can get those numbers to match up, i think it will do for what i need which is to only copy the records over that do not already exist in the main list.

[cubangt]

Found the issue in the rowcounter variable, i was setting it only if it didnt exist and that was preventing the loop to go thru all 72 records

Moved it outside the IF condition

Please Login or Register  to view this content.

Now my counts match up..
on to the copying over to the main sheet..