xystential
New Member
 Joined
 Dec 22, 2020
 Messages
 4
 Office Version

 2016
 Platform

 Windows
I'm not really even sure how to query this so I'll provide an example and then explain further:
These values are pulled in from an agent database. The structure is always a string of 5 characters the first 2 are alphabetic and correspond to the person's initials and the third is a numerical differentiator for people with the same initials. So 2 people both having the initials JJ, one would be JJ0, the other JJ1. The last 2 digits correspond to commission percentages (05% or 10%). What I'm trying to do is create a formula that identifies each unique 3 letter/number identifier and then adds the last two digits for each and outputs a sum value in the same format. So, for instance, in the above scenario, the formula would see that JT0, BB0, and BD0 are unique. It would then output the sum of the last two digits of each in the same format so JT010, BB010, and BD010. This formula would need to be robust to handle additions. By that, I mean as new 3 letter/number identifier are pulled in, the formula recognizes them and performs the operation on them.
Thanks to Anyone That Has a Solution
JT005  JT005  BB005  BB005  BD010 
These values are pulled in from an agent database. The structure is always a string of 5 characters the first 2 are alphabetic and correspond to the person's initials and the third is a numerical differentiator for people with the same initials. So 2 people both having the initials JJ, one would be JJ0, the other JJ1. The last 2 digits correspond to commission percentages (05% or 10%). What I'm trying to do is create a formula that identifies each unique 3 letter/number identifier and then adds the last two digits for each and outputs a sum value in the same format. So, for instance, in the above scenario, the formula would see that JT0, BB0, and BD0 are unique. It would then output the sum of the last two digits of each in the same format so JT010, BB010, and BD010. This formula would need to be robust to handle additions. By that, I mean as new 3 letter/number identifier are pulled in, the formula recognizes them and performs the operation on them.
Thanks to Anyone That Has a Solution