So I have a formula that I am trying to convert as the number of columns could change, but I have them represented as "lFirstHTeamCol" and "lLastHTeamCol"
Formula 1: =IFERROR(INDEX(Table2[@[MVP15]:[FLEX19]],MODE(IF((Table2[@[MVP15]:[FLEX19]]<>"")*(Table2[@[MVP15]:[FLEX19]]<>INDEX(Table2[@[MVP15]:[FLEX19]],MODE(IF(Table2[@[MVP15]:[FLEX19]]<>"",MATCH(Table2[@[MVP15]:[FLEX19]],Table2[@[MVP15]:[FLEX19]],0))))),MATCH(Table2[@[MVP15]:[FLEX19]],Table2[@[MVP15]:[FLEX19]],0)))),"")
The above formulas intention is to show the second most common repeated text. Turning the formula into VBA gives me:
.Formula2R1C1 = "=IFERROR(INDEX(RC[-13]:RC[-5],MODE(IF((RC[-13]:RC[-5]<>"""")*(RC[-13]:RC[-5]<>INDEX(RC[-13]:RC[-5],MODE(IF(RC[-13]:RC[-5]<>"""",MATCH(RC[-13]:RC[-5],RC[-13]:RC[-5],0))))),MATCH(RC[-13]:RC[-5],RC[-13]:RC[-5],0)))),"""")"
Now I just need [-13] to be replaced with lFirstHTeamCol and [-5] to be replaced with lLastHTeamCol.
I really struggle with the quotation marks and what not. Thanks for any assistance.
This is the formula I use for the most common occurrence to give an idea of what I am trying to do:
.FormulaR1C1 = "=INDEX(RC" & lFirstHTeamCol & ":RC" & lLastHTeamCol & ",MODE(MATCH(RC" & lFirstHTeamCol & ":RC" & lLastHTeamCol & ",RC" & lFirstHTeamCol & ":RC" & lLastHTeamCol & ",0)))"
Formula 1: =IFERROR(INDEX(Table2[@[MVP15]:[FLEX19]],MODE(IF((Table2[@[MVP15]:[FLEX19]]<>"")*(Table2[@[MVP15]:[FLEX19]]<>INDEX(Table2[@[MVP15]:[FLEX19]],MODE(IF(Table2[@[MVP15]:[FLEX19]]<>"",MATCH(Table2[@[MVP15]:[FLEX19]],Table2[@[MVP15]:[FLEX19]],0))))),MATCH(Table2[@[MVP15]:[FLEX19]],Table2[@[MVP15]:[FLEX19]],0)))),"")
The above formulas intention is to show the second most common repeated text. Turning the formula into VBA gives me:
.Formula2R1C1 = "=IFERROR(INDEX(RC[-13]:RC[-5],MODE(IF((RC[-13]:RC[-5]<>"""")*(RC[-13]:RC[-5]<>INDEX(RC[-13]:RC[-5],MODE(IF(RC[-13]:RC[-5]<>"""",MATCH(RC[-13]:RC[-5],RC[-13]:RC[-5],0))))),MATCH(RC[-13]:RC[-5],RC[-13]:RC[-5],0)))),"""")"
Now I just need [-13] to be replaced with lFirstHTeamCol and [-5] to be replaced with lLastHTeamCol.
I really struggle with the quotation marks and what not. Thanks for any assistance.
This is the formula I use for the most common occurrence to give an idea of what I am trying to do:
.FormulaR1C1 = "=INDEX(RC" & lFirstHTeamCol & ":RC" & lLastHTeamCol & ",MODE(MATCH(RC" & lFirstHTeamCol & ":RC" & lLastHTeamCol & ",RC" & lFirstHTeamCol & ":RC" & lLastHTeamCol & ",0)))"