CODEWITHSUNDEEP
excel
Ohhhnick
Ohhhnick

Reputation: 5

Assign a percentage of match to a text field based on another text field

In using Mirosoft Excel 2010 I am trying to come up a percentage match field.

I have a pipe delimited cell with values such as |37|3P|61|FE|LJ|R7|WL|

I have another area where I can enter values such as |37|LJ|WL|

I want to see how many of the ones I entered |37|LJ|WL|, exist in the dataset (one cell) |37|3P|61|FE|LJ|R7|WL|

What I tried doing was splitting out my values into different cells. One of |37|, one for |LJ| etc, and then doing a find on the dataset cell. The issue I run into this approach is I can't count or add the values from multiple finds.

Any way I can achieve this or any hints would be great!.

Upvotes: 0

Views: 315

Answers (1)

barry houdini
barry houdini

Reputation: 46401

If your dataset is in A1 and the split values are in B1:B10 then try this formula to count how many of the B1:B10 values are in A1

=SUMPRODUCT(COUNTIF(A1,"*"&B1:B10&"*")*(B1:B10<>""))

Upvotes: 1

Related Questions