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

#### kgardner

##### New Member
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.

 Current New 1 1 2 2 3 3 4 4 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
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")
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
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)),"")
6

<tbody>
</tbody>

#### pbornemeier

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

Replies
2
Views
180
Replies
3
Views
125
Replies
8
Views
396
Replies
3
Views
101
Replies
6
Views
280

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.

### Which adblocker are you using?

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

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