To Find Unmatched data in a Column
 

Presenting a shortened version of the problem. I have two columns:

Column A Column B

114203 114203
114204 114205
114205 114206
114206 114207
114207 114208
114208 114209
114209 114210
114210 114211
114211 114212
114212 114218
114213
114214
114216
114217
114218
There are approximately 10,0000 lines and I need to find the unmatched
data in Column A. Can anybody please help.

To Find Unmatched data in a Column
 

Place this in C2 ( or whatever is starting row) and copy down

=IF(ISNA(MATCH(A2,B:B,0)),"No match","")

OR

try this macro which highlights (colurs RED) non-matched cells in A :


Sub NoMatch()
Dim r As Long, lastrow As Long
Dim res As Variant
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lastrow
res = Application.Match(Cells(r, "A"), Range("B:B"), 0)
If IsError(res) Then
Cells(r, "A").Interior.ColorIndex = 3
End If
Next r
End Sub

HTH

"Himani" wrote:

Presenting a shortened version of the problem. I have two columns:

Column A Column B

114203 114203
114204 114205
114205 114206
114206 114207
114207 114208
114208 114209
114209 114210
114210 114211
114211 114212
114212 114218
114213
114214
114216
114217
114218
There are approximately 10,0000 lines and I need to find the unmatched
data in Column A. Can anybody please help.

To Find Unmatched data in a Column
 

On 10 Feb, 10:57, Toppers wrote:
Place this in C2 ( or whatever is starting row) and copy down

=IF(ISNA(MATCH(A2,B:B,0)),"No match","")

OR

try this macro which highlights (colurs RED) non-matched cells in A :

Sub NoMatch()
Dim r As Long, lastrow As Long
Dim res As Variant
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lastrow
res = Application.Match(Cells(r, "A"), Range("B:B"), 0)
If IsError(res) Then
Cells(r, "A").Interior.ColorIndex = 3
End If
Next r
End Sub

HTH



"Himani" wrote:
Presenting a shortened version of the problem. I have two columns:

Column A Column B

114203 114203
114204 114205
114205 114206
114206 114207
114207 114208
114208 114209
114209 114210
114210 114211
114211 114212
114212 114218
114213
114214
114216
114217
114218
There are approximately 10,0000 lines and I need to find the unmatched
data in Column A. Can anybody please help.- Hide quoted text -

- Show quoted text -

Thanks very much. The Macros were perfectly. Just wondering how I can
group together the ones that are highlighted. Also could you provide
me with a brief explanation of the code. Thanks a ton

To Find Unmatched data in a Column
 

I have added comments to the code and listed (grouped) the non-matches in
column C


HTH


Sub NoMatch()
Dim r As Long, lastrow As Long, rr As Long
Dim res As Variant
' Find last row for column A entries
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
rr = 1
' Loop through rows 2 to "lastrow" of column A
For r = 2 To lastrow
' Use MATCH to find if Column A entry is in Column B
res = Application.Match(Cells(r, "A"), Range("B:B"), 0)
' If there is an error i.e. no match, then highlight column A cell in red
' and place copy of non-matched cell in Column C
If IsError(res) Then
Cells(r, "A").Interior.ColorIndex = 3
rr = rr + 1
Cells(rr, "C") = Cells(r, "A")
End If
Next r
End Sub

"Himani" wrote:

On 10 Feb, 10:57, Toppers wrote:
Place this in C2 ( or whatever is starting row) and copy down

=IF(ISNA(MATCH(A2,B:B,0)),"No match","")

OR

try this macro which highlights (colurs RED) non-matched cells in A :

Sub NoMatch()
Dim r As Long, lastrow As Long
Dim res As Variant
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lastrow
res = Application.Match(Cells(r, "A"), Range("B:B"), 0)
If IsError(res) Then
Cells(r, "A").Interior.ColorIndex = 3
End If
Next r
End Sub

HTH



"Himani" wrote:
Presenting a shortened version of the problem. I have two columns:

Column A Column B

114203 114203
114204 114205
114205 114206
114206 114207
114207 114208
114208 114209
114209 114210
114210 114211
114211 114212
114212 114218
114213
114214
114216
114217
114218
There are approximately 10,0000 lines and I need to find the unmatched
data in Column A. Can anybody please help.- Hide quoted text -

- Show quoted text -

Thanks very much. The Macros were perfectly. Just wondering how I can
group together the ones that are highlighted. Also could you provide
me with a brief explanation of the code. Thanks a ton

To Find Unmatched data in a Column
 

On 10 Feb, 11:41, Toppers wrote:
I have added comments to the code and listed (grouped) the non-matches in
column C

HTH

Sub NoMatch()
Dim r As Long, lastrow As Long, rr As Long
Dim res As Variant
' Find last row for column A entries
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
rr = 1
' Loop through rows 2 to "lastrow" of column A
For r = 2 To lastrow
' Use MATCH to find if Column A entry is in Column B
res = Application.Match(Cells(r, "A"), Range("B:B"), 0)
' If there is an error i.e. no match, then highlight column A cell in red
' and place copy of non-matched cell in Column C
If IsError(res) Then
Cells(r, "A").Interior.ColorIndex = 3
rr = rr + 1
Cells(rr, "C") = Cells(r, "A")
End If
Next r
End Sub



"Himani" wrote:
On 10 Feb, 10:57, Toppers wrote:
Place this in C2 ( or whatever is starting row) and copy down

=IF(ISNA(MATCH(A2,B:B,0)),"No match","")

OR

try this macro which highlights (colurs RED) non-matched cells in A :

Sub NoMatch()
Dim r As Long, lastrow As Long
Dim res As Variant
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lastrow
res = Application.Match(Cells(r, "A"), Range("B:B"), 0)
If IsError(res) Then
Cells(r, "A").Interior.ColorIndex = 3
End If
Next r
End Sub

HTH

"Himani" wrote:
Presenting a shortened version of the problem. I have two columns:

Column A Column B

114203 114203
114204 114205
114205 114206
114206 114207
114207 114208
114208 114209
114209 114210
114210 114211
114211 114212
114212 114218
114213
114214
114216
114217
114218
There are approximately 10,0000 lines and I need to find the unmatched
data in Column A. Can anybody please help.- Hide quoted text -

- Show quoted text -

Thanks very much. The Macros were perfectly. Just wondering how I can
group together the ones that are highlighted. Also could you provide
me with a brief explanation of the code. Thanks a ton- Hide quoted text -

- Show quoted text -

Thank you very much for replying to my query. Much appreciated.