VBA need to delete duplicates with multiple conditions

[DTest]

Hello everyone, I really need help with this VBA code. What I need first from the spread sheet below is to first detect duplicates from a table and only delete based on the following condition.

--If they have a 1st signature OR a 2nd signature then keep the line.
--If they do not have either a 1st signature OR a 2nd signature then it is deleted.
--If they have both a 1st signature AND a 2nd signature, check for duplicates 1 more time and if there is still a duplicate delete the one with the earlier created

Keep in mind this has to work in a table column and below is a sample of what I am trying to achieve

START with this


FI-Last Name Created Date 1st Signature Date 2nd Signature Date
JO-FA Sep 2, 2014 Sep 2, 2014 Sep 2, 2014
JO-FA Aug 26, 2014 Aug 26, 2014 Aug 27, 2014
JO-FA Aug 27, 2014
PA-RI Aug 25, 2014 Aug 25, 2014
PA-RI Aug 26, 2014 Aug 26, 2014
PA-RI Sep 2, 2014 Sep 2, 2014 Sep 2, 2014
TE-LE Aug 26, 2014
TE-LE Aug 25, 2014
TE-LE Sep 2, 2014 Sep 2, 2014

END with this

FI-Last Name Created Date 1st Signature Date 2nd Signature Date
JO-FA Sep 2, 2014 Sep 2, 2014 Sep 2, 2014
PA-RI Sep 2, 2014 Sep 2, 2014 Sep 2, 2014
TE-LE Sep 2, 2014 Sep 2, 2014

Any help would be really appreciated

 

[Fazza]

Is without VBA OK? Untested. Save the file and run a query (ALT-D-D-N and follow the wizard). Criteria that not both signature date fields are null, minimum created date. This would return the wanted records. SQL maybe like this:

SELECT [FI-Last Name], MIN([Created Date]) AS [Created Date], [1st Signature Date], [2nd Signature Date]
FROM YourTable
WHERE NOT ([1st Signature Date] Is Null AND [2nd Signature Date] Is Null)
GROUP BY [FI-Last Name], [1st Signature Date], [2nd Signature Date]

 

[DTest]

Is without VBA OK? Untested. Save the file and run a query (ALT-D-D-N and follow the wizard). Criteria that not both signature date fields are null, minimum created date. This would return the wanted records. SQL maybe like this:

SELECT [FI-Last Name], MIN([Created Date]) AS [Created Date], [1st Signature Date], [2nd Signature Date]
FROM YourTable
WHERE NOT ([1st Signature Date] Is Null AND [2nd Signature Date] Is Null)
GROUP BY [FI-Last Name], [1st Signature Date], [2nd Signature Date]

Hi thank you for the response. Yes it needs to be in VBA, as this would be part of a much larger body of code.

 

[Fazza]

OK. I see I kept (in my earlier post) the MIN 'created date' and this is wrong : it should have been MAX

Well the same idea could be implemented via VBA.

A query table would be one way. Or a pivot table. ADO would be another way - you could erase the source data and copyfromrecordset to replace the original data by the results. Or specific manipulation in VBA using various approaches. Not easy to know what best suits the particular situation. Maybe even the way the current data is generated can be changed to instead give the result (such as if it is a database query, change its definition): to me this might be best.

I wonder how the source data is generated, what version of Excel this needs to work for & how much data there is?

 

[DTest]

OK. I see I kept (in my earlier post) the MIN 'created date' and this is wrong : it should have been MAX

Well the same idea could be implemented via VBA.

A query table would be one way. Or a pivot table. ADO would be another way - you could erase the source data and copyfromrecordset to replace the original data by the results. Or specific manipulation in VBA using various approaches. Not easy to know what best suits the particular situation. Maybe even the way the current data is generated can be changed to instead give the result (such as if it is a database query, change its definition): to me this might be best.

I wonder how the source data is generated, what version of Excel this needs to work for & how much data there is?

I am currently working of Office Mac 2011 and there are 1280 rows

 

[Fazza]

I was wrong with the earlier SQL: I had missed a step. Code below shows correct SQL. I have no familiarity with Mac versions of Excel, sorry. For other than Mac, below works for me.

regards

Sub OK_in_Excel_2003()

    Dim strConn As String
    Dim strSQL As String
    Dim objRS As Object
    Dim wksData As Excel.Worksheet

    'have assumed data sheet is active sheet - modify to suit if not
    Set wksData = ActiveSheet

    'this connection string is for Excel 2003 - modify to suit if not
    strConn = Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", ActiveWorkbook.FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)

    strSQL = Join$(Array( _
            "SELECT A.[FI-Last Name], A.[Created Date], A.[1st Signature Date], A.[2nd Signature Date]", _
            "FROM [" & wksData.Name & "$] A, (", _
            "SELECT B.[FI-Last Name], MAX(B.[Created Date]) AS [Created Date]", _
            "FROM [" & wksData.Name & "$] B", _
            "WHERE B.[1st Signature Date] Is Not Null OR B.[2nd Signature Date] Is Not Null", _
            "GROUP BY B.[FI-Last Name]) C", _
            "WHERE A.[FI-Last Name] = C.[FI-Last Name] AND A.[Created Date] = C.[Created Date]"), vbCr)

    Set objRS = CreateObject("ADODB.Recordset")
    objRS.Open strSQL, strConn
    With wksData.Range("A1").CurrentRegion
        .Offset(.Rows.Count).CopyFromRecordset objRS
        .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
    End With
    Set objRS = Nothing
    Set wksData = Nothing

End Sub

 

[Fazza]

PS

Please note for future to give info like Mac version in initial post.

regards

 

[DTest]

I was wrong with the earlier SQL: I had missed a step. Code below shows correct SQL. I have no familiarity with Mac versions of Excel, sorry. For other than Mac, below works for me.

regards

Sub OK_in_Excel_2003()

    Dim strConn As String
    Dim strSQL As String
    Dim objRS As Object
    Dim wksData As Excel.Worksheet

    'have assumed data sheet is active sheet - modify to suit if not
    Set wksData = ActiveSheet

    'this connection string is for Excel 2003 - modify to suit if not
    strConn = Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", ActiveWorkbook.FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)

    strSQL = Join$(Array( _
            "SELECT A.[FI-Last Name], A.[Created Date], A.[1st Signature Date], A.[2nd Signature Date]", _
            "FROM [" & wksData.Name & "$] A, (", _
            "SELECT B.[FI-Last Name], MAX(B.[Created Date]) AS [Created Date]", _
            "FROM [" & wksData.Name & "$] B", _
            "WHERE B.[1st Signature Date] Is Not Null OR B.[2nd Signature Date] Is Not Null", _
            "GROUP BY B.[FI-Last Name]) C", _
            "WHERE A.[FI-Last Name] = C.[FI-Last Name] AND A.[Created Date] = C.[Created Date]"), vbCr)

    Set objRS = CreateObject("ADODB.Recordset")
    objRS.Open strSQL, strConn
    With wksData.Range("A1").CurrentRegion
        .Offset(.Rows.Count).CopyFromRecordset objRS
        .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
    End With
    Set objRS = Nothing
    Set wksData = Nothing

End Sub

It does not seem to work I keep getting errors, [h=1]Run-time error '429': ActiveX component can't create object VBA[/h]Is there anyway to do this with regular vba and if statements?

I appreciate the help

 

[Fazza]

I have no familiarity with Mac versions of Excel, sorry. For other than Mac, below works for me.

Being for a Mac I'll leave it to others.

If there is no reply, suggest you ask again in a new thread next week and state up front it is for a Mac, and any other specific requirements (such as including If statements)

regards