Merging duplicate cells into a new column with VBA

0 votes
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.
Mar 23, 2023 in Others by Kithuzzz
• 38,010 points

1 answer to this question.

0 votes

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
        If rs.EOF Then booEnd = True
        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
End Sub
answered Mar 23, 2023 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

How to find a value in an excel column by vba code Cells.Find

Just use: Dim Cell As Range Columns("B:B").Select Set cell = ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 63,720 points
0 votes
1 answer

How do you populate a google sheets/excel column with cells from a column in another sheet in the same document?

You have two options on chronology: sheet-by-sheet =QUERY({Sheet1!X:Z; Sheet2!X:Z; ...READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 63,720 points
0 votes
1 answer

Excel VBA- Creation of a New datablock with criteria

To insert the dropdown, you can go ...READ MORE

answered Jan 12, 2023 in Others by narikkadan
• 63,720 points
0 votes
1 answer

Looping through a folder with Excel files and scraping date from selected cells usin VBA

You record the outcome of your comparison ...READ MORE

answered Feb 16, 2023 in Others by narikkadan
• 63,720 points
0 votes
1 answer

Ms-access vba - read from excel and also update that excel

Here is the code that works without ...READ MORE

answered Dec 28, 2022 in Others by narikkadan
• 63,720 points
0 votes
1 answer

VBA Excel get data from Access database Error

Try this: Sub getdata() Const ...READ MORE

answered Mar 19, 2023 in Others by Kithuzzz
• 38,010 points
0 votes
1 answer

Retrieve Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
0 votes
1 answer

How to unmerge multiple cells and transpose each value into a new column in Pandas dataframe from excel file

Try this: df = pd.read_excel("Sample_File.xlsx", header=[0,1,2,3,4,5], index_col = ...READ MORE

answered Jan 8, 2023 in Others by narikkadan
• 63,720 points
0 votes
1 answer

How to insert a picture into Excel at a specified cell position with VBA

Try this: With xlApp.ActiveSheet.Pictures.Insert(PicPath) With ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,720 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP