Counting similar text

[PurpleMonkeyDishwasher]

I have two sets of data. The data is different information about varies companies. Unfortunately in someones wisdom, the two sets of data (on seperate worksheets) only have one common link - the company name. The stupid part of this, is that in one sheet they have the full company name and in the other they have it abbreviated.

I want to be able to create a column on one sheet that shows a count of how many times the company name appears on the other sheet, based on a criteria. Is there a way I can tell it to count similar company names rather than the exact one (the company names change so it has to be automatic - that is I cannot put in the name or partial name in the formula it has to reference a cell).

Please help. I am sure this is simple, but I have no idea what to do.

Thanx

 

[Yogi Anand]

Hi Purple:

There has to be something unique about the company name that is the same in all incarnations of the given company name. If you can establish that, then one can use WildCard(s) to do the lookup or filtering.

 

[PurpleMonkeyDishwasher]

The only thing that I can use is company name. The similarity is the first word of the company name.

Ie: Marty's Repairs would show as Marty's in the other sheet.

Thanx

 

[Nimrod]

=COUNTIF(A:A,"*Marty's*")

 

[PurpleMonkeyDishwasher]

Thanx Nimrod, but I can't do that as it has to reference a cell not text.

 

[Nimrod]

Thanx Nimrod, but I can't do that as it has to reference a cell not text.

Could you please give an example , using Marty's , of what you need ?

 

[PurpleMonkeyDishwasher]

Worksheet 1.

Column A
Marty's Repairs
Franks Store
Bill's Shop

Worksheet 2.
Column A
Marty's
Bill's
Franks
Marty's
Bill's


I would need a formula that looks at worksheet 1 column A row 1 and counts all the "Marty's" in worksheet 2.

Sorry for the confusion.

Thanks

 

[Nimrod]

If you are alway's searching by the first word , like in your example's, then...

=COUNTIF(Sheet2!A:A,LEFT(A1,FIND(" ",A1,1)-1))

OR..

=COUNTIF(Sheet2!A:A,LEFT(A1,FIND(" ",A1,1)-1) & "*")

This assumes that Marty's Repair is located in A1 of Sheet1... put formula in B1 and drag it down column ...

 

[PurpleMonkeyDishwasher]

Thanks Nimrod. Perfect.