Merging duplicate cells into a new column with VBA

Question

To be entirely honest, I have no idea where to begin with this problem for class. (This is the third day of in-class vba instruction.)

There are 22,771 rows in this excel document, which is arranged according to the tickers in column A. I basically need to aggregate the data from rows with duplicate tickers into a single row for each ticker, similar to the approach they gave us.

I've been looking into "merging cells" on Google, but it doesn't seem like that's the best course of action in this case given that I'm combining rows with the same ticker into a new column on the right rather than merging cells in the same column.'Create a script that loops over all the stocks for one year and outputs the information' was the only directive.

I personally learn better when I go through things on my own, so I don't want the solution, but if someone could at least lead me in the proper direction for where to begin, that would be beneficial.

I haven't attempted anything yet because, as I previously mentioned, I don't know where to even start.

Answer 1

The requirement to pull the first Open and last Close for each ticket, compute change and percentage, as well as total vol at the same time, complicates the process. This is a method that makes advantage of recordset loops. Suppose that the rows are already sorted by ticket and date and that the dataset is just for one year. I choose not to use the annoying angle brackets found in field headers. I suggest you take them out.

Sub AggData()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strT As String, dblOpen As Double, dblClose As Double, lngTot As Long, x As Integer, r As Integer, booEnd As Boolean

cn.Open "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & ThisWorkbook.FullName & ";HDR=Yes';"
rs.Open "SELECT * FROM [Sheet2$]", cn, adOpenStatic, adLockOptimistic
strT = rs!Ticker
x = 1
r = 2
Do While Not rs.EOF
    If strT = rs!Ticker Then
        If x = 1 Then dblOpen = rs!Open Else dblClose = rs!Close
        lngTot = lngTot + rs!Vol
        x = x + 1
        rs.MoveNext
        If rs.EOF Then booEnd = True
    Else
        booEnd = True
    End If
    If booEnd Then
        Debug.Print strT & "," & dblOpen & "," & dblClose & "," & lngTot
        Sheets("Sheet1").Cells(r, 9).Value = strT
        Sheets("Sheet1").Cells(r, 10).Value = dblClose - dblOpen
        Sheets("Sheet1").Cells(r, 11).Value = Round((dblClose - dblOpen) / dblOpen, 2)
        Sheets("Sheet1").Cells(r, 12).Value = lngTot
        r = r + 1
        booEnd = False
        If Not rs.EOF Then strT = rs!Ticker
        lngTot = 0
        x = 1
    End If
Loop
End Sub