INDEX or MATCH?

[Justinian]

I have a list of about 1,000 house address numbers in column A (beginning in A2). In column B, I have exported house address numbers (from an external database) for completed home inspections. What I need to do is match the address numbers in column B against column A to find out which addresses have not been inspected yet. What I would like to do is have the formula return a true / false option so that I can quickly sort out the addresses that do not have a match so we can know what homes need to be inspected.

What is a quick way to do this? I believe either INDEX or MATCH (or both) is requisite but I am not sure how to compose the formula.

 

[Justinian]

Ok, here is the code:

Sub Sort_and_Remove_Duplicates()

' Sorts data in column A and B with yellow cell color on top then goes to first row without yellow cell color and deleted contents in row A and B below it

Range("A2:B2").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Tower Addresses").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Tower Addresses").Sort.SortFields.Add(Range( _
"A2:A2749"), xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color _
= RGB(255, 255, 0)
With ActiveWorkbook.Worksheets("Tower Addresses").Sort
.SetRange Range("A2:B2749")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.ScrollRow = 1672
Range("A1701:B1701").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A1").Select
End Sub

 

[Fluff]

You need to Autofilter, not sort, like

Sub Justinian()
   With ActiveSheet
      .Range("A1").AutoFilter 1, xlFilterNoFill
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub

 

[Justinian]

I tried that code and the screen shook back and forth but nothing happened after that.

 

[Fluff]

Was the sheet with your data the active sheet when you ran the code?

 

[Justinian]

Yes.

 

[Fluff]

In that case step through the code using F8 & when this line .AutoFilter.Range.Offset(1).EntireRow.Delete is highlighted have a look at the sheet, has it been filtered to show only non-yellow cells?

 

[Justinian]

When that line is highlighted, all rows are hidden.

I ran the macro recorded and performed an auto filter on color and this is the code I got:

Sub Macro1()
' Macro1 Macro
Selection.AutoFilter
ActiveSheet.Range("$A$1:$B$2749").AutoFilter Field:=1, Criteria1:=RGB(255, _
255, 0), Operator:=xlFilterCellColor
End Sub

 

[Fluff]

When that line is highlighted, all rows are hidden.

Do all your cells in col A have a fill colour?

 

[Justinian]

So prior to running your code, I have data is rows A2:B2749. In that range, 1697 rows have color in column A. When I step into the code with F8 and land on .AutoFilter.Range.Offset(1).EntireRow.Delete, all 2749 rows become hidden (row 2750 is the first visible row now).

 

[Fluff]

But are all the cells in col A coloured in form?