How to find most frequent text with a criteria to not include texts from a range?-CodePudding

I am using the formulas =IFERROR(INDEX( J110:J100000,MODE(IF( J110:J100000<>"",MATCH( J110:J100000, J110:J100000,0)))),"") , =IFERROR(INDEX( J110:J100000,MODE(IF(( J110:J100000<>"")*( J110:J100000<>INDEX( J110:J100000,MODE(IF( J110:J100000<>"",MATCH( J110:J100000, J110:J100000,0))))),MATCH( J110:J100000, J110:J100000,0)))),"") and =IFERROR(INDEX( J110:J100000,MODE(IF((( J110:J100000<>"")*( J110:J100000<>INDEX( J110:J100000,MODE(IF( J110:J100000<>"",MATCH( J110:J100000, J110:J100000,0)))))*( J110:J100000<>INDEX( J110:J100000,MODE(IF(( J110:J100000<>"")*( J110:J100000<>INDEX( J110:J100000,MODE(IF( J110:J100000<>"",MATCH( J110:J100000, J110:J100000,0))))),MATCH( J110:J100000, J110:J100000,0)))))),MATCH( J110:J100000, J110:J100000,0)))),"")

to search for the top most, second most and third most occurring text in the array J110:J100000. But how do I fix these formula's with criteria to not search or to ignore any text that is also occurring in the array AA1:AA10? In other words, the results from these formula's should not be present in any of the cells in the array AA1:AA10, ignoring the blanks of course. TIA. Appreciate any help.

CodePudding user response：

=LET(κ,AA1:AA10,ε,J110:J100000,ζ,UNIQUE(ε),η,SORTBY(ζ,COUNTIF(ε,ζ),-1),INDEX(UNIQUE(FILTER(η,(η<>"")*(1-ISNUMBER(MATCH(η,κ,0))))),SEQUENCE(3)))

The 3 passed to SEQUENCE determines the number of results to return.