Compare 2 columns of data and find the address of cells that are not in both columns

[kgardner]

Hello,

I am trying to compare 2 lists of data, column a (current data) and column b (new data), and find the address of the cells that are in column b and not column a. The data in column a is constant and the data in column b is updated regularly.

Example: What formula would I use to find the address? My data has over 500 rows of entries and I need to know if new entries are added.


[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Current[/TD]
[TD]New[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]



I am currently using a count(b:b)-count(a:a) to tell me how many new entries are present but am unsure how to find the address of the new data.

 

[pbornemeier]

I don't know how to do it with a formula, but this code will analyze the differences and paste the result a table in columns D:E with the extra items in column B and their addresses.

Function InBNotInA() As Variant

    Dim lRowIndex As Long
    Dim rngA As Variant
    Dim rngB As Variant
    Dim lIndex As Long
    Dim varTemp As Variant
    Dim varK  As Variant
    Dim varI As Variant
    
    rngA = Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value
    rngB = Range("B2:B" & Cells(Rows.Count, 2).End(xlUp).Row).Value
    
    With CreateObject("Scripting.Dictionary")
        'Inventory column B, saving addresses
        For lRowIndex = 1 To UBound(rngB, 1)
             .Item(rngB(lRowIndex, 1)) = "B" & lRowIndex + 1
        Next
        'Remove items from inventory that are also in column A
        For lRowIndex = 1 To UBound(rngA, 1)
            If .exists(rngA(lRowIndex, 1)) Then .Remove (rngA(lRowIndex, 1))
        Next
        'Get Scripting.Dictionary data to array
        If .Count > 0 Then
            ReDim varTemp(1 To 2, 1 To .Count)
            varK = .Keys: varI = .Items
            For lIndex = 1 To .Count
                varTemp(1, lIndex) = varK(lIndex - 1): varTemp(2, lIndex) = varI(lIndex - 1)
            Next
        End If
    End With
    'Paste array to worksheet at D2
    Range("D2").Resize(UBound(varTemp, 2), 2).Value = Application.Transpose(varTemp)

End Function

 

[kgardner]

Phil - I don't understand half of the code but it works! Thank you!

I was able to figure out a solution using the formulas below, many found from other posts online and pieced together.

​

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]Formulas[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD] C2 =IF(A2<>"",IF(ISERROR(MATCH(A2,B:B,0)), "ERROR",""),"")[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD] D2 =IF(C2="ERROR",B2,"")[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD] E2 =SUM(AND(C2="ERROR",D2<>""),E1)

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD] F2 =IFERROR(INDEX(D:D, MATCH(ROWS($F$2:F2), E:E,0)),"")[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5[/TD]
[TD] G2 =IFERROR(ADDRESS(MATCH(F2, B:B,0),1),"")[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[pbornemeier]

You're welcome.
Thanks for posting the formulas. I always like to learn alternate ways of doing things.