I've asked a very similar question here before but now I need something slightly different. The document below shows in the first column of the sheet an identifier of each individual next column shows the number of individuals that match the individual in the first column. Then there's a highlighted column which I would like to fill and behind that are the identifiers of the individuals that match to the individual in the first column.
Basically I had two groups of people and for every person in group 1 I've selected which individuals of group 2 most closely match that person. So, hard work done you'd think. Not so much however, because as you can see, there are multiple matches for some individuals (max = 50 matches). There's no hierarchy to these matches, any is as good as the next, but what I can't have is duplicate matches.
So, I sorted on number of matches and start at the individual with the smallest number of matches. That match is already filled out in the shaded column for the first and second row. For every subsequent row I want to check whether the first match has already been selected as a match to another individual, if that is the case I want to skip that match and move on colomn over to the next, check that one and if it has not yet occurred as a match select that one.
To use an example: row 6 which has individual nr 613. He/She has 4 matching individuals (identifiers: 122, 127, 137 and 335). Now the first match for nr. 613 would be the individual with nr. 122, however that individual was already selected to match a previous individual (in row 2). So I want to skip that individual and skip to the next, thereby matching individual 613 to individual 127 and so on, and so forth.
I'd got pretty far using a number of nested IF statements but ran over the allotted amount of arguments doing it that way. So now I'm looking for a VBA solution - in case I want to change my matching criteria and wind up with a whole new sheet of possible matches.
Could someone help me out?