I have a large spreadsheet I was asked to edit. Basically wherever the data was pulled from, it created several duplicates of individuals names, country, start dates, and end dates. Would it be possible to get the start and stop dates in adjacent cells and remove the duplicate data?
I have provided a screen shot. Manually copying, pasting, and deleting would take a very long time since this spreadsheet has over 2300 rows with approximately 50% being duplicates that will need edited.
thanks
VBA shouldn't be necessary here, just add a new column with the formula:
=CONCATENATE(C1,D1)
Replace the column letters with your column letters for Start Date and End Date.
You can then use Excel's remove duplicates function on the new column (Data -> Remove Duplicates)
Using SQL is suitable.
Sub myQuery()
Dim strSQL As String
Dim strTable As String
Dim Ws As Worksheet
strTable = "[" & ActiveSheet.Name & "$]"
strSQL = "SELECT NAME, COUNTRY, MIN([Start Date]) as [Start Date] , max([End Date]) as [End Date] "
strSQL = strSQL & " FROM " & strTable & " "
strSQL = strSQL & " Where not isnull(NAME) "
strSQL = strSQL & " Group by NAME, COUNTRY "
Set Ws = Sheets.Add
exeSQL strSQL, Ws
End Sub
Sub exeSQL(strSQL As String, Ws As Worksheet)
Dim Rs As Object
Dim strConn As String
Dim i As Integer
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties=Excel 12.0;"
Set Rs = CreateObject("ADODB.Recordset")
Rs.Open strSQL, strConn
If Not Rs.EOF Then
With Ws
.Range("a1").CurrentRegion.ClearContents
For i = 0 To Rs.Fields.Count - 1
.Cells(1, i + 1).Value = Rs.Fields(i).Name
Next
.Range("a" & 2).CopyFromRecordset Rs
.Columns.AutoFit
End With
End If
Rs.Close
Set Rs = Nothing
End Sub
