Forum Discussion

LAVic15's avatar
LAVic15
Copper Contributor
Apr 18, 2021

Filtering Logic Assistance

Hi, I need help creating the following sorting logic.  I have 3 columns, column 1 has a unique identifier value (ex. 15-1, 15-2,15-3), column 2 has a group value that is shared amongst members of tha...
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Apr 18, 2021

HansVogelaar 

The first thing I thought of was the reciprocal count trick to calculate the distinct count for each group.

= LET(
  m,(Group=@Group)/COUNTIFS(Group,Group,Choice,Choice),
  SUM(m) - 1 )

Then I decided I liked your solution but took it a step further to propose the modal solution as a correction

= LET(
  indices, IF(Group=@Group, XMATCH(Choice,Choice)),
  mode, INDEX(Choice,MODE(indices)),
  IF(@Choice<>mode, mode, ""))

Resources