LR_Formula = "=IFERROR(IF(ROWS($A$5:A5)>SUM(IF(FREQUENCY(IF(INDIRECT($B$1)<>"",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW($M$17)+1),1)),"",INDEX(INDIRECT($B$1),SMALL(IF(FREQUENCY(IF(INDIRECT($B$1)<>"",MATCH(INDIRECT($B$1),INDIRECT($B$1),0)),ROW(INDIRECT($B$1))-ROW($M$17)+1),ROW(INDIRECT($B$1))-ROW($M$17)+1),ROWS($A$5:A5)))),"")"

With Worksheets("Sheet1").Range("A5")

.FormulaArray = LR_Formula

.Value = .Value

End With

this is just one of a few scenarios I have tried and am open to others

the Error is invalid property range. I am aware it is > 255 characters, however I have tried other array formulas under 255 and still not working