I need to compare a spreadsheet of 5000 records. I'm comapring 2 columns of street names (b and c), B=Street address C=Mailing address, the purpose is to see which home owners have a different mailing address than their street address. I'm running into a problem demonstrated below:
b street address c mailing address
6090 Burlington pike 6090 Burlington PK

As you can see if I use a simple countif on these columns, it will tell me that the homeowner for 6090 Burlington pike has a different mailing address from the street address, but in reality both have the same address, only the data is typed differently. What would be the best way to compare cells similar to the demonstration above? Here are some other examples I see.

b c
1188 Spring Dr 1188 Spring Rd
2312 Ethan trail 2312 Ethan trl

What you are asking excel to do is fairly complex. Here is a potential solution that may be of interest to you:

Soundex - this is based on a pattern of assigning alphanumeric values to characters in a string that meet particular rules. Visiting Excel Developer Tip: Searching Using Soundex Codes explains the solution with a downloadable sample file courtesy of Richard J. Yanco.

There is also potential for a percentage match, see Percentage Match - microsoft.public.excel.worksheet.functions | Google Groups although this is an even trickier solution.

There are various other comparisons that you could carry out on your two columns including character length, you could split multi-word strings into single word columns and compare each column in term or compare the first n characters from each string.

Hope this helps,
Dave