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
183 views

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
        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
answered Mar 23, 2023 by narikkadan
• 63,420 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,420 points
2,976 views
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,420 points
1,172 views
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,420 points
231 views
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,420 points
653 views
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,420 points
1,160 views
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
447 views
0 votes
1 answer

Retrieve epay.info 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
899 views
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
3,219 views
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,420 points
1,846 views
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,420 points
1,976 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP