MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Search two columns for an item count


Posted by Wynn on October 16, 2001 7:51 AM

I need to obtain a count from data that is in 2 seperate columns.
I am trying to count how many of 3 different procedures, each of 4 different doctors did.
The procedure name is in one column and needs to be a partial string search as the names of a particular group of the same procedure differs, so I need to extract just the specific word out of the string. The other column of data has the doctors name as the string. I am trying to get data to export to a report or printable spreadsheet that says Doctor 1 procedure 1, (number), procedure 2 - (number), etc.
Any help would be provided. I have looked various ways around this, including arrays, but can't quite figure out how to use them. Thanks.


Posted by Wynn on October 16, 2001 7:54 AM

PS: This would be in VBA and not in formulas

Posted by Aladin Akyurek on October 16, 2001 9:20 AM

Juan

Here is an example:

txtClO2Royal_SW_U.Text

I was wondering if I can use the ".Name" property. I tried the following but got an "object missing error":

ctrl_name = TextBox.Name

Stan


Posted by Aladin Akyurek on October 16, 2001 9:49 AM

Wynn,

Lets say that column A houses procedures used and B doctors from row 2 on.

Put in D4:D7 the four doctors.
Put in E2:G2 the three procedures.
Put in E3 the partial string that corresponds to procedure 1.
Put in F3 the partial string that corresponds to procedure 2.
Put in G3 the partial string that corresponds to procedure 3.

In E4 enter: =SUMPRODUCT(($A$2:$A$100=$D4)*(ISNUMBER(SEARCH(E$3,$B$2:$B$100))))

Copy this to G4 then down as far as needed.

Aladin

======