Extract NEW records

[Portucale]

Hi,

Have a report which everyday extracts data from a database, the data extracted is always from Jun 26th, I need to identify any new records from the previous day, example below:

Yesterday

Today

10

10

11

11

12

12

13

13

14

14

15

15


16


17


In my list I need to see 16 & 17 only.

Any help and all help is very much appreciated,

Thanks in advance

 

[Luke M]

With what is visible here (on this forum), how do we know that 16 and 17 are special?

 

[Portucale]

Ops, so sorry, can't even help myself,

Yesterday Today New Records
10 10 16
11 11 17
12 12
13 13
14 14
15 15
16
17
Thanks for the help,

 

[Luke M]

Ah, that helps!
Put this array formula in C2, copy down as far as ever needed.
=IF(COUNT(B:B)-COUNT(A:A)<ROWS(C$2:C2),"",INDEX(B:B,SMALL(IF(ISNA(MATCH(B$2:B$100,A$2:A$100,0)),ROW(A$2:A$100)),ROWS(C$2:C2))))
Array formulas are confirmed with Ctrl+Shift+Enter, not just Enter.

 

[Portucale]

Thanks Luke, formula is great, do you know of any vba process?

 

[Smallman]

Hi @Portucale

Here is a VBA procedure to do what you want.

Option Explicit
 
Sub Compare1()
Dim ar As Variant
Dim var()
Dim i As Long
Dim n As Long
 
ar = Range("a9").CurrentRegion 'Change input to suit
 
ReDim var(1 To UBound(ar, 1), 1 To 1)
 
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For i = 1 To UBound(ar, 1)
            .Item(ar(i, 2)) = Empty
        Next
        For i = 1 To UBound(ar, 1)
            If Not .exists(ar(i, 1)) Then
                n = n + 1
                var(n, 1) = ar(i, 1)
            End If
        Next
    End With
[D9].Resize(n).Value = var 'Change output to suit
 
End Sub

Link to file which shows workings.

Compare

Take care

Smallman

 

[Portucale]

Many Thanks for the solutions, both have their place in my work. Very grateful to Luke and Smallman,