Mark earliest date in series of sequential dates by criteria

[mrpavlos]

Hello all. Thank you again for this great forum and the opportunity to learn from the many questions and answers. I have a question, which I hope will make sense. I have been given a reporting task on a daily transaction log where, for example, in Column A of the spreadsheet I will have many SKUs and in Column B I will have a list of dates which will always be in sequence (i.e there will never be any days missing, but the SKUs themselves wont appear on the log every day). My task is to identify in Column C the earliest date in a series of sequential dates for a given SKU. I have given an example below of what I am seeking to do. Once I identify the 'NEW' I then need to work out the most recent date of the occurence of this 'NEW' by SKU (I am thing of a Max/Index formula which I will try out). The formula I have come up with quickly in Column C is also given below but I am wondering if there is a much straightforward way of finding the 'NEW's as there will be roughly in the order of 50K to 100K records over a whole year. Each day another day's worth of transactions is added to the current data. I would love to hear any suggestions using any approach whether formula, VBA, pivottable etc. Thanks again for taking the time to read this and help, Paul

=IF(COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,B2-1)=0,"NEW","")

SKU
A	NEW
B	NEW
B
A
B
A	NEW
B
A
B	NEW

<tbody>
[TD="width: 75"]Date[/TD]
[TD="width: 64"][/TD]

[TD="class: xl63, align: right"]01/01/2019 [/TD]

[TD="class: xl63, align: right"]01/01/2019 [/TD]

[TD="class: xl63, align: right"]02/01/2019[/TD]

[TD="class: xl63, align: right"]02/01/2019[/TD]

[TD="class: xl63, align: right"]03/01/2019[/TD]

[TD="class: xl63, align: right"]04/01/2019 [/TD]

[TD="class: xl63, align: right"]04/01/2019[/TD]

[TD="class: xl63, align: right"]05/01/2019[/TD]

[TD="class: xl63, align: right"]06/01/2019 [/TD]

</tbody>

 

[Marcelo Branco]

Maybe a Pivot Table

SKU --> Rows Labels
Date --> Values

Right-click Date field
in Summarize values by pick Max

Hope this helps

M.

 

[mrpavlos]

Hello M. Thanks so much for taking the time to read and help. Your suggestion of a pivot table would definitely be better than the formula approach given the amount of potential records. However, I need to identify first the 'NEW's so that I can use that in the pivot table filter to work out the most recent occurence of the 'NEW' for each SKU.

 

[Marcelo Branco]

Reading more carefully your post I realized that my suggestion does not do what you need. Sorry ...

M.

 

[mrpavlos]

Thank you again M. I will definitely though use your suggestion re: the pivot table.

 

[Marcelo Branco]

Thank you again M. I will definitely though use your suggestion re: the pivot table.

I was trying to avoid an auxiliary column (for the sake of performance), but if it seems feasible maybe something like this


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

SKU​

[/TD]
[TD]

Date​

[/TD]
[TD]

Is New​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

A​

[/TD]
[TD]

01/01/2019​

[/TD]
[TD]

NEW​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

B​

[/TD]
[TD]

01/01/2019​

[/TD]
[TD]

NEW​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

B​

[/TD]
[TD]

02/01/2019​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

A​

[/TD]
[TD]

02/01/2019​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

B​

[/TD]
[TD]

03/01/2019​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

A​

[/TD]
[TD]

04/01/2019​

[/TD]
[TD]

NEW​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

B​

[/TD]
[TD]

04/01/2019​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

A​

[/TD]
[TD]

05/01/2019​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

B​

[/TD]
[TD]

06/01/2019​

[/TD]
[TD]

NEW​

[/TD]
[/TR]
</tbody>[/TABLE]


Your formula in column C

Pivot Table (filtered by NEW)

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

SKU​

[/TD]
[TD]

Is New​

[/TD]
[TD]

Max of Date​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

A​

[/TD]
[TD]

NEW​

[/TD]
[TD]

04/01/2019​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

B​

[/TD]
[TD]

NEW​

[/TD]
[TD]

06/01/2019​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


M.

 

[Marcelo Branco]

Paul,

As you have 50K to 100K rows of data, for the sake of performance i suggest a macro to build column C rather than a formula using COUNTIFS.
Something like this

Sub aTest()
    Dim dic As Object, vData As Variant, vResult As Variant, i As Long
        
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = vbTextCompare
    
    vData = Range("A2:B" & Cells(Rows.Count, "A").End(xlUp).Row)
    vResult = Range("C2:C" & Cells(Rows.Count, "A").End(xlUp).Row)
    For i = 1 To UBound(vData, 1)
        dic(vData(i, 1) & "-" & vData(i, 2)) = ""
       If Not dic.exists(vData(i, 1) & "-" & vData(i, 2) - 1) Then vResult(i, 1) = "NEW"
    Next i
    Range("C2:C" & Cells(Rows.Count, "A").End(xlUp).Row) = vResult
End Sub

M.

 

[mrpavlos]

Thanks so much M for this. I really do appreciate this. It works great and I have learned something new from reading your code. Thank you again

 

[Marcelo Branco]

You are very welcome. Glad to help

M.