This falls into the "Any help is greatly appreciated" category as opposed to being a solution. My first thought was that if I could get a table of KC ID#'s vs. TM#'s I could then get excel to find matches in more than one column or row. Making a list of unique TM#'s quickly used up my laptops resources causing the process to take several minutes. I came up with a table that would count how many times a TM served on a KC, with the logic being that if a TM only served on one KC there would be no opportunity to do another KC with members of that team. The formula used in the blue shaded portion of Sheet1 is: Next thought was that I could further reduce by selecting only KC's that had three or more team members meeting with 'Counts' of 2+. The green shaded column that accomplishes this uses the formula: Finally I got the actual TM#'s, yellow shaded portion, using the formula: This reduced the number of candidate TM#'s by 2/3 giving the laptop a chance to complete the unique list (Sheet2 column A) in a reasonable amount of time, using the formula: Also reduced are the KC candidates (Sheet2 row 1) based on the formula: The table is then populated using the formula: This provides a visual aid for finding 'Three Or More matches'. Note: the portion of sheet one shaded pink was an unsuccessful attempt to have excel identify the KC's in which the 'Three Or More matches' criteria had been met. I am leaving it in the attached file so that others can see if there is a way to complete the process or get some inspiration for a successful solution.
Let me know if you have any questions.
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks