Forum Discussion

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

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, ""))

LAVic15

Copper Contributor

Apr 18, 2021

PeterBartholomew1 Excel won't run the formula you suggested as written, it updates it to

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

I am unable to get any results, and receive a #name? error. Is there any customization I have to do with the formula in order for it to run properly? I am using Excel version 2103

PeterBartholomew1
Silver Contributor
Apr 18, 2021
LAVic15
Sorry, but the versions of Excel are not compatible
= SUM((Group=@Group)/COUNTIFS(Group,Group,Choice,Choice)) - 1 = IF(INDEX(Choice, MODE(IF(Group=@Group, MATCH(Choice,Choice,0))))<>@Choice, INDEX(Choice, MODE(IF(Group=@Group, MATCH(Choice,Choice,0)))), "")
Whilst Excel 365 can run legacy spreadsheets, Office 2013 will accept neither the XMATCH function nor, more seriously, the LET function that gives modern Excel its code-like appearance. The formulas as shown should be compatible.
Book1(PB).xlsx16 KB
- LAVic15
  Copper Contributor
  Apr 18, 2021
  Thank you god sir. You are truly an excel wizard!