Three Spreadsheets - Need to Compare Data and Make One

**BeeZeRCoX** · 01-27-2011, 10:29 AM

Okay, I'll try to explain this as best I can. Currently we have three spreadsheets:

A company prospect list we've created
A company prospect list we've purchased
A current company customers sheet

We want to create the most thorough prospect sheet we can. The purchased list will definitely have more up-to-date contact information than our created list. Ultimately, I need to do a few things and want to know the best way to go about doing it.

First, we need to compare our current customers list to the prospect list we purchased and remove any of our current customers from that list.

After that, I need to compare the two prospect lists. If there is company that is listed on both prospect lists, I want to use the purchased list's contact information and remove ours. If there is a company that is on OUR prospect list but not on the list we purchased, I want to make sure it is added.

Ultimately, I want to get it to one prospect list that has filtered out all of our current partners and combined the two prospect lists we have using the purchased list's data for any duplicates.

Thoughts?

**Blake 7** · 01-27-2011, 10:48 AM

The are many ways to skin this baby - index match may be best if you upload your sheet i'll give you an example of how to do this.

=INDEX(D:D,MATCH(A2,D:D,0)) if na appears = not in other list

or u can use conditional formatting

=ISERROR(MATCH(A2,$B$2:$B$671,0))

this will highlight all in one list but not in the other

**BeeZeRCoX** · 01-27-2011, 11:22 AM

Thanks Blake. I think you're right in that the index function is probably the best method to use in this scenario. As an additional question, is there any way to get it to match based on a partial result as opposed to the exact name?

Example, Tennessee Bank and Trust is a current customer of ours. In our database it is listed as "Tennessee Bank & Trust." In the prospect database it is listed as "Tennessee Bank and Trust."

So, when I use this indexing function that result shows up as N/A, although it is only a slight variation on the name.

Thanks so much for the quick response.

**Blake 7** · 01-27-2011, 11:27 AM

hiya - yes it is possible to match on the first X amount of letters. I don't knnow how to do this off the top of my head, but I will get back to you shortly as i am sure there is a post here somewhere asking the same question.

**BeeZeRCoX** · 01-27-2011, 11:28 AM

Great. I'll do a search on here to see if I can figure that out!

Thanks so much Blake!

**Blake 7** · 01-27-2011, 11:34 AM

you could always find and replace "and" for "&". but there may be other slight differences so a formula may be better

**BeeZeRCoX** · 01-27-2011, 11:40 AM

There are several with slight variations of this, so figuring out how to implement it within the formula would be ideal.

For example: Action Nissan versus Action Nissan, Inc.

I think I've found some steps on this, I'm testing it out.