Hi Ozgrid,

So I have been given the joyous task of sorting a large set of bad contact data for companies. The companies can be identified by various fields but my focus is on the three different phone number fields (mobile, other, office). The problem is the data is all over the place. Some contacts work for multiple companies, some work for multiple locations of one company (each with separate accounts), some companies share phone numbers exactly or with extensions tacked on. The goal is to find out what numbers go to what company and or contact by using either the exact value or the value with an extension (extra digits or labled "ext").

What I am trying to do is to search through the three phone number columns for a number from a call sheet, and then every time I find that number exactly or with an extension, I want to copy the whole row its found in and put it on another sheet with the searched for number. Everything I have looked up so far for formulas either doesn't return rows, or doesn't search multiple columns. I considered maybe three different formulas to search each column separately but since the phone numbers sometimes are repeated in a row it would double count it or it might jumble the data. I'm wondering if there is a combination of formulas that might help, or if I need an entire macro (something I currently know nothing about).

Any help or pointers on how to automate this would be greatly appreciated, as I am currently at a loss for what to look into as a solution next. Attached is a test sample of data.

forum.ozgrid.com/index.php?attachment/73120/