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!
Solved! Go to Solution.
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
