Forum Discussion

MR11's avatar
MR11
Copper Contributor
Feb 05, 2025

Formula to repeat dynamic patterns

Thank you in advance.

Is there a formula I can use to generate the patter in column D based on the duplicate data in columns A-C? For example, I need to enter "Y" 12 times after "N" if there are 12 rows with duplicate data.

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor

    Can you please attach a sample file showing your sample input data and then show your desired output manually. We will try to make that output using formula. Also please mention your Excel edition/version info so that we can suggest appropriate formula.

  • m_tarler's avatar
    m_tarler
    Bronze Contributor

    Yes but a better more complete explanation and sample sheet would be helpful.  Based on what you said it could be starting in D2:

    =IF(CONCAT(A1:C1)=CONCAT(A2:C2), "Y", "N")

    and then fill down.

    if instead any repeat should be marked "Y" then maybe:

    =IF(ISNUMBER(XMATCH(A2 & B2 & C2, $A$1:$A1 & $B$1:$B1 & $C$1:$C1)), "Y", "N")

    or 

    =IF( SUM((A2=A$1:A1) * (B2=B$1:B1) * (C2=C$1:C1)), "Y", "N" )

    and then fill down

    you could also put the above into a BYROW() or SCAN() dynamic array formula

Resources