Excel VBA for Data Handling

[KSKWin]

I have a excel with Col A is Date, Col B is Amount and Col C is Remark

Date column will have repeated date entry for different amount and different remarks.
Date column will not be Sequential fashion, Dates will be skipped.

I want a VBA code to achieve,

1) Sequential Date should be shown with Amount Zero and Blank Remark shown as comment in Amount Column, if there is no Date entry present in Col A
2) If multiple same date entries are present, then sum all the amount for the specific date and list the Remark for each amount against the same date as Excel Comment in Amount Col B

Attached the sample excel

Test Samples.xlsx
	A	B	C	D	E	F	G
1
2	This is Actual Data Column				This is I need
3
4	Date	Amount	Remark		Date	Amount
5	03-01-2024	9275.00	Apple		01-01-2024	0
6	05-01-2024	8673.00	Mango		02-01-2024	0
7	08-01-2024	123.00	Pine		03-01-2024	9275
8	13-01-2024	500.00	Grape		04-01-2024	0
9	24-01-2024	195.00	Jack		05-01-2024	8673
10	25-01-2024	5664.00	Grape		06-01-2024	0
11	25-01-2024	12.00	Grape		07-01-2024	0
12	26-01-2024	124.00	Grape		08-01-2024	123
13	26-01-2024	1422.00	Mango		09-01-2024	0
14	28-01-2024	147.00	Grape		10-01-2024	0
15	28-01-2024	854.00	Apple		11-01-2024	0
16	29-01-2024	125.00	Grape		12-01-2024	0
17	29-01-2024	86.00	Pine		13-01-2024	500
18	30-01-2024	21	Pine		14-01-2024	0
19	30-01-2024	58	Apple		15-01-2024	0
20					16-01-2024	0
21					17-01-2024	0
22					18-01-2024	0
23					19-01-2024	0
24					20-01-2024	0
25					21-01-2024	0
26					22-01-2024	0
27					23-01-2024	0
28					24-01-2024	195
29					25-01-2024	5676
30					26-01-2024	1564
31					27-01-2024	0
32					28-01-2024	1001
33					29-01-2024	211
34					30-01-2024	79
35
36
Sheet1

 

[MikeVol]

Hello @KSKWin.
Try next code, perhaps I didn't understand the problem correctly.

Option Explicit

Sub FillDatesAndPrices()
    Dim i           As Long
    Dim ws          As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Application.ScreenUpdating = False

    With ws
        .Range("E4:F" & ws.Rows.Count).ClearContents
        .Range("E4").Value = "Date"
        .Range("F4").Value = "Amount"

        Dim priceDict As Object
        Set priceDict = CreateObject("Scripting.Dictionary")

        Dim lastRow As Long
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        For i = 5 To lastRow

            If IsDate(.Cells(i, 1).Value) Then
                priceDict(Int(.Cells(i, 1).Value)) = .Cells(i, 2).Value
            End If

        Next i

        Dim yearFromCell As Integer
        yearFromCell = Year(.Cells(5, 1).Value)

        Dim dateToFind As Date
        dateToFind = DateSerial(yearFromCell, Month(Date), 1)
        i = 5

        Do While dateToFind <= DateSerial(yearFromCell, Month(Date) + 1, 0)
            .Cells(i, 5).Value = dateToFind

            If priceDict.Exists(Int(dateToFind)) Then
                .Cells(i, 6).Value = priceDict(Int(dateToFind))
            End If

            dateToFind = dateToFind + 1
            i = i + 1
        Loop

    End With

    Application.ScreenUpdating = True
End Sub

Good luck.