Find duplicates in 2 columns and copy/paste to new worksheet

Hi

Please find attached a workbook of sample data and the results im trying to achieve.

What I would like is a macro to run rather than currently sorting and deleting manually.

On the "data" worksheet is the raw data once pasted form another application and I have applied “text to columns” next what I normally do is remove duplicates based on column E then sort based on column J and F then manually remove all non duplicates.

There must be a duplicate in both J and F, all other data can be deleted.

If this could be put into a simple macro it would be fantastic as my data is many 1000’s of rows.

Thanks
David

Hi

Thanks for the swift reply ,but the answer is no.

I would like to remove everything apart from the data on the worksheet labeled "report".

Ideally I would like to run a macro on the raw data in the "worksheet" giving my the data on the "report" worksheet

Regards
David

Regards
David

I'm a bit confused you say

what I normally do is remove duplicates based on column Ethen sort based on column J and F then manually remove all non duplicates.

There must be a duplicate in both J and F, all other data can be deleted

Does this removal include non-duplicates in Column E?

Got to go for a while, I'll look back later tonight.

Hi

Once the first remove duplicates has been applied to column E, we can inore what happens in that column from then on in.

The data I will be pasting into the worksheet once working will vary in length of rows also.

Thanks
David

I'm struggling to match your results.

This sheet was created using 2007 remove duplicates from your sample data.
Could you confirm that it agrees with your manual method of clearing?

It would be a great help in tidying up the next step, if you were to then copy this to another sheet and remove the duplicates in F & J for comparism.

When the Data is confirmed I will build a VBa solution as you have requested.

Cheers.

Hi Marcol

I think I might be going at this the wrong way round.

If you look at the new sheet I have attached, with a start of a macro and a full list of data that I use ( this does vary in length from 100 to 50,000 rows )

If some how the macro could first delete any blank rows until it came across data the inputed

Range("E1").Select   ActiveCell.FormulaR1C1 = "=MID(RC[-1],1,7)"

and was able to paste this down column E first until then end of the data regardless of length.

then if it inputed

Range("I1").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(--(RC[-4]&RC[-3]&RC[-1]=R1C5:R50000C5&R1C6:R50000C6&R1C8:R50000C8))"

If the value of that cell was 1, delete row if any other value continue pasting into next cell below in column I etc..untill then end of the data.

If you copy and paste this formula into all cells in column I at once the spreedsheet hangs as there is so much data in the sumproduct range

What do you think ?

Okay, bad timing again!!!

Got to go soon, at least I'll get back to it with a clear head..........

Hi All

Just had a second thought about this data im trying to sort, rather than deleting lots of data that I don’t want, leaving behind some data that is still not needed and some that is, would it be best to just search for the data I want, using some formula in a macro.

It going to be quite complicated because the search criteria are different based on a mixture of letter and numbers within the workbook.

I.e. on a cell containing P-4-D18C71 a match could be any of the following P-4-D18D71 ,P-4-D18E71, P-4-D18C70 or P-4-D18C72.

But on a cell containing P-1-C18B231 a match could be any of the following P-1-C18A231
, P-1-C18C231, P-1-C18B221 or P-1-C18B211

There are a few other scenarios based of the 5th character in a particular cell.
Is this to tricky ??

Hi Marcol

Ref your post ; Quick check

Your formula inserted in E2

Code:
=MID(D2,1,7)This returns P-3-D30 from P-3-D30D12, is this correct?

Perhaps it should be

Code:
=MID(D2,5,255)Returning D30D12

I think the first formula will work best, but I can try the second easy enough and compare the data that is left.

Thanks
David

I'll give it a go. Look back later tonight.

Hi Marcol

The first formula is correct, the second returns a value of 1 ( which we are going to delete rows based on this value ) for some of the data I want to keep.

Thanks
David

Try this for starters

Option Explicit

Sub ClearBlankRows_TextToColumns_Duplicates()
    Dim LastRow As Long, LastCol As Long, FirstRow As Long, RowNo As Long

    If Range("A1") = "" Then
        FirstRow = Range("A1").End(xlDown).Row
        If Range("A" & FirstRow) = "Tool starting." Then
            Rows(1 & ":" & FirstRow).EntireRow.Delete
        Else
            Rows(1 & ":" & FirstRow - 1).EntireRow.Delete
        End If
    End If
    If Not IsDate(Left(Range("A2"), 10)) Then
        Range("A2").EntireRow.Delete
    End If

    Do
        LastRow = Range("A" & Rows.Count).End(xlUp).Row
        If Not IsDate(Left(Range("A" & LastRow), 10)) Then
            Range("A" & LastRow).EntireRow.Delete
        Else
            Exit Do
        End If
    Loop

    Range("A1:A" & LastRow).TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
                                          FieldInfo:=Array(Array(0, 1), Array(11, 1), Array(22, 9), _
                                                           Array(25, 9), Array(28, 1), Array(42, 1), _
                                                           Array(56, 2), Array(69, 9), Array(91, 9), _
                                                           Array(115, 1), Array(120, 1), Array(129, 9)), _
                                                           TrailingMinusNumbers:=True

    Columns("E:E").Insert Shift:=xlToRight

    LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Range(Cells(1, 1), Cells(1, LastCol)).EntireColumn.AutoFit
    Range("B1") = "TIME"

    Application.ScreenUpdating = False
    On Error GoTo ResetApplication

    Range("E2").Resize(LastRow - 1, 1).Formula = "=LEFT(D2,7)"
    Range("J2").Resize(LastRow - 1, 1).Formula = "=E2&F2&H2"
    Range("K2").Resize(LastRow - 1, 1).Formula = "=COUNTIF($J2:$J" & LastRow - 1 & ",J2)"

    Range("K2:K" & LastRow - 1).Copy
    Range("K2:K" & LastRow - 1).PasteSpecial xlPasteValues


    Range("$A$1:$K$" & LastRow).AutoFilter Field:=11, Criteria1:="1"
    Rows("6:" & LastRow).Delete Shift:=xlUp
    Range("$A$1:$K$11613").AutoFilter
    
    Columns("J:K").Clear
    
ResetApplication:
    Err.Clear
    On Error GoTo 0
    Application.ScreenUpdating = True
    Range("A1").Select

End Sub

This will take a couple of minutes to run with a file having about 28k rows.

Check it out for accuracy based on your first set of rules, we can look at more or different rules later

Have fun!!!........
Alistair

Hi Alistair

Is this macro running, to the first way of thinking deleting rows based on the value of 1 returned from the sumproduct or running to the rules I thought of based on value in a cell.

I think its deleting some data I would like to keep at the mo.

Thanks
David

Hi Alistair

Could we test using your first thought along with a couple of other idea's.

Would it be possible to create a macro to do what you first thought about as a start in sorting the data, and actually have it delete the rows, thenI can check to see if the data i want is still there.

I think this would be a perfect start in reducing the data in size.

i.e. Sort your data on Column E
In L1

=IF(AND(E1=E2,J1&F1=J2&F2),"Remove","")

If this works maybe create a second macro to do the sumproduct thing and then see what is left after that.

I dont mind having to run 3 or 4 macros on the data to get results.

Thanks for all your time.... where do I send the christmas card ?

Regards
David

Hi Marcol

The macro runs fine, but it deletes some information I wish to keep.

If you see workbook attached, on sheet1 is the same information as sheet2 but after I have run the macro.

All the information in bold and red should be kept, also could we keep for now the columns with the letter m in and the two columns with inventory in, this will make it easier to test the data after I run your test macro.

I think a good start is to have 1 macro to remove exact duplicates as per

=IF(AND(E1=E2,J1&F1=J2&F2),"1","")

this will remove a lot of data on the real data, if you can some how make the formula and the below run together, this worked when I tied it seperate.

Sub delrow()
Range("A1").Select
Do While ActiveCell <> Empty
 If ActiveCell.Offset(0, 11) = 1 Then
    ActiveCell.EntireRow.Delete
 Else
    ActiveCell.Offset(1, 0).Select
 End If
Loop
End Sub

Then we can test the tricky soration on the data thats left

What do you think.

forgot to attach workbook

I'm on a different tack here.

Try this workbook for an initial sift of your main data.
The macro removes what appear to be obvious duplicates, to me at least.

If you do a text to columns on your 28k sample, then paste the result to sheet "TextToColumns" A2 you can test this. Just make sure the columns match this sample,and you retain the header row.

Then copy sheet "TextToColumns" and run the macro on the copy. This will take a couple of minutes to run
but it reduces the size from 28k rows to 19k.

The formulae used are in sheet "TextToColumns" M2 & N2

The code assumes header rows, so if you try it on a cut-down sheet make sure the headers are in row 1.

If you could check this out, and if it is okay, then mark-up the next rule that will make a significant difference to the file size.
Don't try to be over-complicated with this rule if it can be avoided, just be sure it doesn't remove anything you need to retain.

If we take it step at a time then we will solve this.

Cheers
Alistair

Hi Marcol

Sorry its been so long, ive been away in another country.

Many thanks for the work on the forumula.

Could you try and add into the macro inserting the column I have made yellow on the example attached and add into that column for each row the formula that is there now

=MID(E2,1,7)

.

Then change the formula in which will be N2 (once new column is inserted )

=F2&G2&K2

Is the macro removing rows with a count lower than 2 in what was N2.

This I hope this will remove more data.

Many thanks
David

Hi

I would be very gratefull if anybody can update the macro attached in the pervious post as the coments in the post.

Thanks
David