# Counting similar text

#### PurpleMonkeyDishwasher

##### New Member
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).

Thanx

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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.

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

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

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

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 ?

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

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

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

OR..

Code:
``=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 ...

Thanks Nimrod. Perfect.

Replies
4
Views
216
Replies
2
Views
204
Replies
1
Views
216
Replies
5
Views
149
Replies
5
Views
183

1,196,485
Messages
6,015,472
Members
441,898
Latest member
kofafa

### 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.

### Which adblocker are you using?

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

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