Removing duplicate rows

Im using the following code to remove duplicate records from a sheet:

Please Login or Register  to view this content.

It successfully removes the duplicates (based on Columns A and B), however it is changing the formatting of the cells and messing with the conditional formatting I have on the sheet. How can I get around this?

Any thoughts?

See attached sample workbooks. Before shows all rows before being filtered, Filtered shows blank rows (indicating which rows should be filtered out), After shows all rows back together without the filtered blank rows.

This is an explanation of the filtering process as it applies to these records:
-Row 3 removed (duplicate of 4, Columns A and B match)
-Rows 10 & 22 match in Column B but not A, so they both stay
-Rows 12 & 25 match in Column B but not A, so they both stay
-Rows 14 & 15 are both blank in Columns A & B, so ignore these rows
-Row 20 removed (duplicate of 4, Columns A and B match)
-Row 25 removed (duplicate of 26, Columns A and B match)

N.B. When a duplicate is found the lower record should be kept, and the upper record should be removed.

Thanks,
James

See if the attached is what you want...

Thanks jindon, but I'm getting runtime error '429' Active X component can't create object.
I think its because Excel 2011 does not support Active X. Is it possible to do it without this?

Thanks Jindon that code worked, but theres one error:
The last filtering note: "-Row 25 removed (duplicate of 26, Columns A and B match)" Did not happen. If Columns A are both blank and B matches then its a duplicate.

Also how is the range or rows to be filtered defined, I can't figure it out looking at the code? I was planning to use some code like this to define the range that it filters:

Please Login or Register  to view this content.

The reason being is this sheet will become very large, and I will probably be adding more code to this to keep the sheet organized. This way it will only run on the new records. (When the code is complete it should add "YES" to the G column of all filtered rows, that way the code won't run again until new records are added).
Although the range that gets filtered is variable, it should always refer to the entire sheet (rows 3 on) to find duplicates.

Happy 4th,
James

Remove filter and try again.

For safety, copy the code again and try.

I tried copying the same code again, but it still gave me the same outcome. Rows 25 and 26 are both still remaining.

OK, now I see what you mean.

The code ignores the rows that has no data in col.A...

Try this one

Please Login or Register  to view this content.

That worked by removing row 25 (since with rows 25 and 26, both Column A are blank and B match). However it removed row 14 which should stay. Basically when both rows A and B are blank thats when the row should get ignored (that will prevent row 14 from getting removed).

Thanks,
James

Hummm

Please Login or Register  to view this content.

That acted the same way as the other code. Lines 14 and 15 stayed as they should (since both columns A and B are blank). However it should remove row 25.
Basically whenever Columns B is blank then the row should be ignored.

See this
Sample Before with code.xlsm

Thanks Jindon that appeared to work correctly. Im just going to test it out with a larger sample, to make sure it works in other scenarios.
Any thoughts on post #10, about how I can incorporate that code to change the range of rows that the code filters?

Cheers,
James

Try this.

Please Login or Register  to view this content.

This code seems to be working just the same as the last. If redefining the range is too complicated, a workaround might be to start off the code with: IF Column G contains data then ignore that row.
It should still reference all records (including where G contains data) when searching for a matching duplicate to match to, but this way it will only run the code on the newly added unfiltered records to save time (since only newly added records will have no data in G).

Thanks for sticking with me,
James

Originally Posted by Nitefox

Basically whenever Columns B is blank then the row should be ignored.

Then

Please Login or Register  to view this content.

Is this what you wanted?

Anyway, 2 is the col. reference from col.A in this case....

I think that would do it, although its: IF G column contains data then ignore the row, so it should be:

Please Login or Register  to view this content.

Im guessing this line would go on line 6 after "For Each r In rng", but what comes next to tell the code to ignore these lines when data is found in the 7th column?
So Basically the code should ignore rows where column 2 is blank (which it is currently doing) and where column 7 contains data.

Originally Posted by Nitefox

I think that would do it, although its: IF G column contains data then ignore the row, so it should be:

Please Login or Register  to view this content.

Other way around... should be

Please Login or Register  to view this content.

Originally Posted by Nitefox

Im guessing this line would go on line 6 after "For Each r In rng", but what comes next to tell the code to ignore these lines when data is found in the 7th column?

Please Login or Register  to view this content.

Is this correct:

Please Login or Register  to view this content.

Im assuming the code in the lines below it need updating also, as its not effecting it by itself.

Is this correct:

Please Login or Register  to view this content.

Im assuming the code in the lines below it need updating also, as its not effecting it by itself.

What is the criteria?

If you only concern the data in Col.G then simply

Please Login or Register  to view this content.

At the moment the code ignores rows from the duplicate filtering process if there is no data present in Column B. Basically I would like it to also ignore rows where if data is present in Column G.

Then

Please Login or Register  to view this content.

means Col.B is not blank and Col.G is blank

Thanks jindon, that works to ignore rows where G contains data.
However after testing it with a larger amount of data I just discovered a bug. In the case of ...(.Cells(i,2).Value <>"")... it should correctly always be ignored (as it is doing).
But in the case of ...(.Cells(i,7).Value = "")... it should only be ignored when looking for other duplicates that match it, but for other records that dont meet this criteria (because they have no data in G) they should still reference all records including these records in search of a duplicate.
I have attached a screen dump of my full sheet to help explain this. Basically at the moment it has over 30,000 records, and this will continue growing. The code has already ran on these records, so i know that all 30,000 records are unique. When new records are added to the bottom of the sheet, there is no need for the code to check all 30,000+ records again for duplicates (as it has already done it before), I only want to it check the new records that have been added, which should save a considerable amount of runtime for this code. In the example picture the new records just added are from row 30289 on (since the G Column is still blank). The way it is currently it is only looking for duplicates within these 40 newly added records, but it should still compare these 40 new records to the other 30,000. And when a match is found again the higher record should be removed (so the newly added record is always favored).

Sorry if I wasn't explaining it very well,
thanks,
James

Need to see a small sample workbook with before/after with all possible combination of data/results that shows what you are trying to do.

I have attached a sample before and after workbook. On sample before you will notice there are 7 records at the bottom of the list which contain blank cells in the G column. This means they are newly added records that have yet to be checked for duplicates with the other rows. The other rows already contain the work 'Yes' in the G column, meaning they have already been filtered and do no contain any duplicates so there is no need for these records to be checked. The exact sample criteria we have already established for finding duplicates applies.
When the code runs on these 7 newly added records it will find the following:
-Row 24 matches row 20, and so row 20 (being the higher, and older record) should be removed.
-Row 28 matches row 3, and so row 3 should be removed.

When the code is complete just these two rows should be removed. I will then have 'Yes' added to the G Column for these 7 rows.
For testing purposes I have created a duplicated within the rows that have already been filtered (Row 22 and 23). While this will never occur in real life, I have put it in for testing purposes as with this new criteria they should not be removed.

Thanks,
James

See if this is what you wanted.

Used array to speed up the process.

Please Login or Register  to view this content.

It correctly removed the two duplicates, and the test duplicates on row 22 and 23 were also correctly left along. However after the code has finished its processes it displays the excel error 'Out Of Memory', which keeps refreshing when I press OK. I have to escape out of it to stop it. I am just running the code on the 25 or so records in the sample workbook at the moment.

I don't have any problem, so no idea....

After restarting my computer it went away. Hopefully it won't come back.
Thanks for all your help Jindon,
James

Jindon, I have just discovered for some reason when using the code with a larger amount of records it is still filtering the rows that contain "Yes" in column G. This wasn't a problem with the small sample sheet I made as it didn't filter out that test duplicate I made.
On the sample worksheet I attached this time there are over 4000 rows, each of which contain "Yes" in column G, so when the code runs it should have no effect, but its not the case.

1) You have Change event code, so it needs to set it On/Off.

Please Login or Register  to view this content.

2) What do you want when duplicate have both with Col.G "Yes" ?

The code should only look the rows where Col.G is blank (which will always be towards the bottom, as these will be newly added rows). It still compares these rows to all the others, but since all the others where Col.G is 'Yes' will no longer contain duplicates within them (since the code has already ran on them) there is no need for all these rows to be checked among each other for duplicates again.

There will never be a case for duplicates to exists with both having Yes in G, as all rows with Yes in G would have already been checked (Im just deliberately testing it when i know there are duplicates that have Yes in G as a means to test, as these duplicates should not be removed with this criteria. This is just for test purposes to make sure the code is working as desired).
I tried using that code on the larger sample, and it is still filtering out rows, even though all rows have 'Yes' in G.Col (so nothing should happen). Basically it should not even be looking to find duplicates where Col.G both equal Yes.
Does that make sense?
See attached.

Still not clear to me.

All the rows with "Yes" in Col.G should be isolated?

Or

If duplicate found with "yes" & blank, what should be done?

Basically this sheet contains a huge amount of records. New records will always be added to the bottom. When they are newly added the G Col will be blank. Lets say I add 10 new records to the bottom of the sheet. The code when ran should compare these 10 newly added rows (which can be identified by the fact they are the only rows with blank in Col G) with all rows on the sheet (Including those with Yes or Blank on Col G).
The higher duplicate should be removed every time, so most of the time the record with "Yes" will be getting removed.
When the code is complete, I will have these newly added rows = 'Yes' in Col. G so they will never be compared to the other 'Yes' labeled rows again to save considerable time, since there will never be any duplicates within the Yes labeled rows.

Does that mean regardless of what's in col.G, upper data of dups removed?

Correct, regardless of Col.G upper record always goes.

So, go back to the original with a little change

Please Login or Register  to view this content.

Its still filtering records when both contain "yes" in G.Col.
See attached copy of the same Larger Sample workbook with that code. All 4000+ records contain "Yes" in Col.G so even though there are duplicates amongst them (purely for test purposes this would not happen in real life), no records should be removed.

Again, what is the correct criteria???

I asked
"Does that mean regardless of what's in col.G, upper data of dups removed?"
and you said
"Corect"

I don't think I am able to understand what you are trying to do.

Only in the case that a row with blank col.G and a "Yes" col.G match. In that case the upper (and in every case that a match is found) a "Yes" col.G will likely be getting removed (as these ones will always be above the blank col.Gs).
There should never be a case where two "Yes" col.G rows are considered a match, and the code should not even be checking Col.G rows for matches (as the "Yes" in Col.G symbolizes that this row has already been searched among the other for a duplicate, so there is no need to search for it again).

Heres another way of explaining what I'm trying to do:
In my mind there are two ranges of data or rows when looking for duplicates. It looks something like this: If Range1 = Range2 then remove the higher duplicate. Range1 is the data that has blank in col.G and Range2 is the data that has "Yes" in col.G.
Once Range1 has been searched against Range2 it to then becomes part of Range2 (as "yes" gets added to col.G). Range2 should never be compared against Range2 (itself) as they will never contain any duplicates.
On the Large Sample workbook all rows contain "yes" in col.G and therefore there is only one big Range2, there is no Range1 present. So when the code runs nothing should happen.
Does that make sense?

At one point it seemed as though this was working just as I wanted with the below code. When I ran this code on the small 20 record sample workbook it correctly ignored the test duplicates I made that both contained Yes in col.G, but it didn't work when i applied it to the larger 4000 sample workbook. You mentioned it could have been due to the sheet change event, but disabling this while the code ran made no difference.

Please Login or Register  to view this content.

Jindon, does it make sense what I'm trying to do?
Please let me know if not so I can try and re post.

Sorry but no idea about what you are really trying to do.

Jindon, theres another criteria I was wondering if you could help add for me:
If A matches (either matching in text, or by both containing blank cells), and B is blank (it must be blank) then matches in C are also duplicates (lowest record always remains).

Current code:

Please Login or Register  to view this content.

Is there a quick way to adapt this code so that it only goes off duplicates in comlumn A but still removes the entire row if there is a duplicate there?

I need this because it needs to delete entire row otherwise my formatting gets messed up