Grumpy88
Member
Hi.
I am struggling to create a formula that will track the first occurrence of a given name, but always in conjunction with a further cell value, and would really appreciate someone devising a formula-based solution that I have been unable to find.
Please see the attached spreadsheet. I have one half of the formula that I need in column D, which looks at the names in column B, and then displays the wording "Debut" next to a name if it's the first time that that particular name has appeared. That bit works fine. However, the "Debut" text should be dependent not only on the name, but also the Match number shown in column F. The problem is that often the same name appears twice with the same Match number, such that not only should the first appearance of the name be indicated "Debut", but if applicable also the second appearance (provided the Match number in column F is the same for both name instances).
For example, the name "Davidson, Richard E" in B2 is correctly indicated as "Debut" in D2, because it is the first time that his name appears in column B. However, it appears again in B23, and as it is still the same Match (Match #1, as displayed in F2 and F23), the formula should also indicate "Debut" at D23. Thereafter, whenever his name reappears, but with a different Match number (e.g. in B48, where F48 shows that it is now Match #2 and no longer #1), the formula should instead display a dash next to it in column D (any given player can only debut once, with one specific Match number).
I was playing around in column C trying to create a formula that would work with the entries in both columns B and F, thinking that if I could tell Excel what the first appearance of a given Match number was, I could then use that cell reference in a formula to compare whether the column F Match number next to a given name was the same - and if so, would then indicate "Debut" next to it as well in column D (even though it wasn't necessarily the first appearance of that name). However, both the Address and Cell functions, while providing the correct cell reference, generate only a text entry, which means I couldn't use it subsequently in a further formula.
Hope I have explained that clearly! Long story short, I am looking for a formula in column D that marks each new appearance of a name as "Debut", but not necessarily just in the row where that name appears for the first time - if there are any other rows in which that same name appears, with the same column F Match number as the row with the first appearance of that name has, then all such rows with an identical column B name / column F Match number combination should all be flagged as "Debut". That can only happen either once or else a maximum of twice per individual Match number.
I am struggling to create a formula that will track the first occurrence of a given name, but always in conjunction with a further cell value, and would really appreciate someone devising a formula-based solution that I have been unable to find.
Please see the attached spreadsheet. I have one half of the formula that I need in column D, which looks at the names in column B, and then displays the wording "Debut" next to a name if it's the first time that that particular name has appeared. That bit works fine. However, the "Debut" text should be dependent not only on the name, but also the Match number shown in column F. The problem is that often the same name appears twice with the same Match number, such that not only should the first appearance of the name be indicated "Debut", but if applicable also the second appearance (provided the Match number in column F is the same for both name instances).
For example, the name "Davidson, Richard E" in B2 is correctly indicated as "Debut" in D2, because it is the first time that his name appears in column B. However, it appears again in B23, and as it is still the same Match (Match #1, as displayed in F2 and F23), the formula should also indicate "Debut" at D23. Thereafter, whenever his name reappears, but with a different Match number (e.g. in B48, where F48 shows that it is now Match #2 and no longer #1), the formula should instead display a dash next to it in column D (any given player can only debut once, with one specific Match number).
I was playing around in column C trying to create a formula that would work with the entries in both columns B and F, thinking that if I could tell Excel what the first appearance of a given Match number was, I could then use that cell reference in a formula to compare whether the column F Match number next to a given name was the same - and if so, would then indicate "Debut" next to it as well in column D (even though it wasn't necessarily the first appearance of that name). However, both the Address and Cell functions, while providing the correct cell reference, generate only a text entry, which means I couldn't use it subsequently in a further formula.
Hope I have explained that clearly! Long story short, I am looking for a formula in column D that marks each new appearance of a name as "Debut", but not necessarily just in the row where that name appears for the first time - if there are any other rows in which that same name appears, with the same column F Match number as the row with the first appearance of that name has, then all such rows with an identical column B name / column F Match number combination should all be flagged as "Debut". That can only happen either once or else a maximum of twice per individual Match number.
