• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help with a formula please

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.
 

Attachments

p45cal

Well-Known Member
Not sure if I've got the logic right, but test the following in cell D2 copied down:
Code:
=IF(LEN([@Player])>1,IF(SUMPRODUCT((B$2:B2=B2)*(F$2:F2<>F2))=0,"Debut","-"),"")
 

Grumpy88

Member
Hi. Thank you for the response!

No, unfortunately this still isn't working correctly - it indicates "Debut" for every player in every match.
 

Attachments

Peter Bartholomew

Well-Known Member
I believe the test should be 'Has the player appeared before in a different match?', otherwise 'Debut'
= IF( [@Player]<>"-",
IF( COUNTIFS( [Player],[@Player], [Match '#],"<>"&[@[Match '#]], [Appear code],"<"&[@[Appear code]]), "-", "Debut"),
"-")
 

Attachments

p45cal

Well-Known Member
Hi. Thank you for the response!

No, unfortunately this still isn't working correctly - it indicates "Debut" for every player in every match.
Not here.
You've moved columns but you haven't changed the formula to follow.
Try changing to:
Code:
=IF(LEN([@Player])>1,IF(SUMPRODUCT((B$2:B2=B2)*(G$2:G2<>G2))=0,"Debut","-"),"")
where F has changed to G.

Edit post posting: …which seems to give the same results as Peter's.
 

Grumpy88

Member
My bad, p45cal. Apologies, and thanks so much to you too for being willing to help me out!

Very much obliged to you both!
 
Top