find partial text contained in another cell

[Jim G]

I have a data set that contains Date Worked (A), Transaction Text (B), Hours
(C), Value (D)

Unfortunatley, the Transaction Text contains the names of the employees and
are not always the same.

However, some part of the Transaction Text always contains the surname but
may be in a different location in the string. An example is "a cole - to
stores" and "a cole - fabricate basket".

I had thought to create a control list (surnames), then use sumproduct to
find the the control text within the free text descriptions and then
calculate the sum of the values and hours for a range of dates (pay periods)
for each match.

We have since given each person a unique code to search on. However, this is
historical data that I can't change without updating thousands of records.

Any solution would be gratefully appreciated.

Jim

 

[Jim G]

I came across a solution that witha little manipulation gives me the disired
result.

Here it is for anyone interested.

=SUMPRODUCT(--(ISNUMBER(FIND($A10,'Nebo Nov06'!$O$8:$O$1000)))*('Nebo
Nov06'!$S$8:$S$1000)*('Nebo Nov06'!$A$8:$A$1000>=B$3)*('Nebo
Nov06'!$A$8:$A$1000<=C$3))

B3 & C3 are date ranges.

By making the list all upper case it finds the partial match okay. The only
issue is that Sumproduct is very slow to recalculate large amounts of data.
Even though I've ranged this over 1000 rows the actual data is only over 75
or so per list.

A better solution may be to use Dsum or the like. I might research this
later.