Convert AGGREGATE formula to VBA 3

[josephteh]

Hi All

Continuing from my previous 2 threads:

http://https://www.excelforum.com/ex...ml#post5843551
https://www.excelforum.com/excel-pro...ml#post5842951

Now, I am required to display the First Sales Date and Last Sales Date in respective sheets.

Need your help again, thank you!

Sincerely
Joseph

[jindon]

Again, flexibility first.
Assuming Sales Data is sorted in ascending order by PERIOD column.

Please Login or Register  to view this content.

[josephteh]

Thank you jindon for once again coming to my rescue.

Can I request to modify the code to allow for other columns (not fixed) in the "Sales Data" table?

Thank you.

[jindon]

change to

Please Login or Register  to view this content.

[josephteh]

Thank you jindon, works perfectly!

2 mistakes made by me: (1) there is no "Company Code" in Sales Data sheet, and (2) it's called "Company Name" (not "Company Code") in the Customer Master sheet.

Can you help me to modify the code please? Thank you so much!

[jindon]

try change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[josephteh]

Thank you for your prompt response, jindon! The "Company First & Last Sales Dates" are not showing, possibly because "Company Name" has been removed from Sales Data sheet.

[jindon]

And what do you expect for Company First/Last Sales?

[bebo021999]

I'm not sure about your exact requirement, but with the Company name in the Customer Master sheet, how can we retrieve the start date and end date when there are two types of dates in the sheet? However, I assume that the Company will retrieve data from CUS (the start date and end date of CUS). Since a company can have multiple CUS, it will take the smallest start date and the largest end date of its CUS.
My approach is as follows:
I will use three dictionaries to store the lists of Cus, Stock, and Com, with their keys being a concatenation of the start date and end date in a string, linked by '|'. I will iterate through the lists, comparing the start date and end date with the dates in the list to filter out the smallest start date and the largest end date.

Sub FirstLastDate()
Dim lr&, i&, rng, res()
Dim dCust As Object, dStock As Object, dCom As Object
Dim sp, sp1, Mn As Double, Mx As Double
Set dCust = CreateObject("Scripting.Dictionary")
Set dStock = CreateObject("Scripting.Dictionary")
Set dCom = CreateObject("Scripting.Dictionary")
With Sheets("Sales Data")
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    rng = .Range("A2:J" & lr).Value2
    For i = 1 To UBound(rng)
    
        ' generate Customers data
        If Not dCust.exists(rng(i, 4)) Then
            dCust.Add rng(i, 4), rng(i, 1) & "|" & rng(i, 1) ' get the first pair of date
        Else ' from 2nd occurence, to get smaller date (Mn) and larger date (Mx)
            sp = Split(dCust(rng(i, 4)), "|")
            With WorksheetFunction
                Mn = .Min(rng(i, 1), sp(0))
                Mx = .Max(rng(i, 1), sp(1))
            End With
            dCust(rng(i, 4)) = Mn & "|" & Mx
        End If
        
        ' generate Stock data
        If Not dStock.exists(rng(i, 10)) Then
            dStock.Add rng(i, 10), rng(i, 1) & "|" & rng(i, 1) ' get the first pair of date
        Else ' from 2nd occurence, to get smaller date (Mn) and larger date (Mx)
            sp = Split(dStock(rng(i, 10)), "|")
            With WorksheetFunction
                Mn = .Min(rng(i, 1), sp(0))
                Mx = .Max(rng(i, 1), sp(1))
            End With
            dStock(rng(i, 10)) = Mn & "|" & Mx
        End If
    Next
End With
With Sheets("Customer Master")
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    rng = .Range("A2:D" & lr).Value2
    ReDim res(1 To UBound(rng), 1 To 4)
    For i = 1 To UBound(rng)
        sp = Split(dCust(rng(i, 1)), "|")
        
        ' Get first & last date for CUS
        res(i, 1) = sp(0): res(i, 2) = sp(1)
        'working with Company
        If Not dCom.exists(rng(i, 4)) Then
            dCom.Add rng(i, 4), dCust(rng(i, 1)) ' get the first pair of date
        Else ' from 2nd occurence, to get smaller date (Mn) and larger date (Mx)
            sp1 = Split(dCom(rng(i, 4)), "|")
            With WorksheetFunction
                Mn = .Min(sp1(0), sp(0))
                Mx = .Max(sp1(1), sp(1))
            End With
            dCom(rng(i, 4)) = Mn & "|" & Mx
        End If
    Next
    For i = 1 To UBound(rng)
        sp = Split(dCom(rng(i, 4)), "|")
        res(i, 3) = sp(0): res(i, 4) = sp(1)
    Next
.Range("G2:J100000").ClearContents
.Range("G2").Resize(UBound(res), 4).Value = res
End With
With Sheets("Stock Master")
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    rng = .Range("A2:A" & lr).Value
    ReDim res(1 To UBound(rng), 1 To 2)
    For i = 1 To UBound(rng)
        sp = Split(dStock(rng(i, 1)), "|")
        res(i, 1) = sp(0): res(i, 2) = sp(1)
    Next
.Range("E2:F100000").ClearContents
.Range("E2").Resize(UBound(res), 2).Value = res
End With
End Sub 

[josephteh]

And what do you expect for Company First/Last Sales?

Thank you jindon for checking back. As Quang has correctly pointed out in post #9, the sales dates for the "Company Name" are derived from the sales dates of "Cust Code" in the Customer Master sheet.

"Cust Codes" are actually the branch codes of the Company.

[bebo021999]

Thank you jindon for checking back. As Quang has correctly pointed out in post #9, the sales dates for the "Company Name" are derived from the sales dates of "Cust Code" in the Customer Master sheet.

So, does the code in #9 work?

[josephteh]

Thank you Quang PT, your codes seemed to be working well.

[bebo021999]

Thank you Quang PT, your codes seemed to be working well.

Although the code is functional, I am not very confident about its speed with large datasets. I hope that someone will join in and provide the most optimized code for your situation.
Have you tested it with large data yet?

[jindon]

If other code is working, go for it, don't bother.

[josephteh]

Hi jindon, your code is more flexible.

[jindon]

Then you need to tell me that if that is the only one irregular case or if you have more, need to know all of them.

[josephteh]

Hi jindon. Confirmed. No more further cases. Thank you.

[jindon]

See if this is how you wanted.

Please Login or Register  to view this content.

[josephteh]

Working perfectly! Thank you jindon & Quang. Rep given!