I have two criteria (name and month) and I am trying to calculate top-3 most frequent text occurrences in a large dataset. I already crafted the formulas for top-1 and top-2 and they are working well, but struggling to create one for the top-3.
Top-1:
=INDEX(data!$H:$H,MODE(IF((data!$C:$C=$D40) * (data!$W:$W=E$39),MATCH(data!$H:$H,data!$H:$H,0))))
Top-2:
=INDEX(data!$H:$H,MODE(IF(IF((data!$C:$C=$D41)*(data!$W:$W=E$39),MATCH(data!$H:$H,data!$H:$H,0))<>MODE(IF((data!$C:$C=$D41)*(data!$W:$W=E$39),MATCH(data!$H:$H,data!$H:$H,0))),IF((data!$C:$C=$D41)*(data!$W:$W=E$39),MATCH(data!$H:$H,data!$H:$H,0)),"")))
Top-3:
???
Will appreciate any help, if any additional detail is needed, happy to provide it.
Top-1:
=INDEX(data!$H:$H,MODE(IF((data!$C:$C=$D40) * (data!$W:$W=E$39),MATCH(data!$H:$H,data!$H:$H,0))))
Top-2:
=INDEX(data!$H:$H,MODE(IF(IF((data!$C:$C=$D41)*(data!$W:$W=E$39),MATCH(data!$H:$H,data!$H:$H,0))<>MODE(IF((data!$C:$C=$D41)*(data!$W:$W=E$39),MATCH(data!$H:$H,data!$H:$H,0))),IF((data!$C:$C=$D41)*(data!$W:$W=E$39),MATCH(data!$H:$H,data!$H:$H,0)),"")))
Top-3:
???
Will appreciate any help, if any additional detail is needed, happy to provide it.