Finding % Commonality

Question

Hello, I'm trying to figure out how to find % commonality between cells within the same column.

For example: Take the list below, I want to find what % do each of the services share:

Tree Service received 4 requests (A,B,C,D) and it has a 50% commonality with House (both serviced Request A, B) , 75% commonality with Lawn (A, C,D) and a 50% commonality with Carpet (B,D).

Then I want to filter these commonalities to answer this question: Which service worked on 75% of the same requests as the Tree service- the answer being "Lawn"

Thank you!

DataNath · Accepted Answer

Hey @jfoley020, I've put something together to prove the concept here - think it seems to be along the lines of what you're after. The idea here is that we:

1) Isolate the [Service] of interest i.e. Tree here

2) Create concatenated lists of the [Request] for each of the other [Service]

3) Append these to Tree

4) Check if each Tree [Request] is contained within the concatenated list or not, assigning a 1 to the match count if so

5) For each Tree <> Other [Service] combination, sum up the match count and divide it by the number of requests for tree to get the percentage