Forum Discussion
MR11
Feb 05, 2025Copper Contributor
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.
- Harun24HRBronze 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_tarlerBronze 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