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

kgardner

New Member
Joined
Sep 3, 2015
Messages
22
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.


CurrentNew
11
22
33
44
5

<tbody>
</tbody>



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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,890
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.

Code:
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

New Member
Joined
Sep 3, 2015
Messages
22
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.

ABFormulas
11 C2 =IF(A2<>"",IF(ISERROR(MATCH(A2,B:B,0)), "ERROR",""),"")
22 D2 =IF(C2="ERROR",B2,"")
33
E2 =SUM(AND(C2="ERROR",D2<>""),E1)


<tbody>
</tbody>
44 F2 =IFERROR(INDEX(D:D, MATCH(ROWS($F$2:F2), E:E,0)),"")
55 G2 =IFERROR(ADDRESS(MATCH(F2, B:B,0),1),"")
6

<tbody>
</tbody>
 

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,890
You're welcome.
Thanks for posting the formulas. I always like to learn alternate ways of doing things.
 

Forum statistics

Threads
1,136,864
Messages
5,678,213
Members
419,751
Latest member
richkings

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top