INDEX or MATCH?

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,551
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Thanks for that, I forgot a , in the filter. It should be
VBA Code:
Sub Justinian()
   With ActiveSheet
      .Range("A1").AutoFilter 1, , xlFilterNoFill
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub
But this will delete the entire row, do you just want it to delete cols A & B?
 
Upvote 0
Deleting the rows is not bad because I am running a COUNTIF on the remaining units and the standard auto filter will return all units, not just the ones highlighted in yellow. Is it easier to just clear the contents of column A and B after the filter runs or is deleting the whole row easier?
 
Upvote 0
You can just delete the cols A:B & shift up like
VBA Code:
Sub Justinian()
   With ActiveSheet
      .Range("A1").AutoFilter 1, , xlFilterNoFill
      .AutoFilter.Range.Offset(1).Delete xlShiftUp
      .AutoFilterMode = False
   End With
End Sub
 
Upvote 0
Ok, that works. Actually, the code deleting the rows works perfectly!

Thank you again for your time with this! I appreciate your help.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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
Back
Top