Forum Discussion
Filtering Logic Assistance
Why do you have column 3 at all? Of the choice of A/B/C depends on the group, you don't need to store it in every row. Just create a separate table:
If you wish, you can use VLOOKUP formulas in the third column to return the choice corresponding to the group number. That way, it will be consistent.
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, ""))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
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.
Thank you for the feeback! PeterBartholomew1
The data is not being created in excel, it is being imported into excel and there will inevitably be examples of entries with the same group number but different choice values due to human error. The goal is to find and identify those inconsistencies in excel. For example, in the below chart, I would want to be able to identify entries 15-3 and 15-8 that have incorrect choice values that do not match the choice associated with their fellow group members.
Identifier Group Choice 15-1 1 A 15-2 1 A 15-3 1 B 15-4 2 C 15-5 2 C 15-6 2 C 15-7 2 C 15-8 2 B OK. Let's say your data are in columns A, B and C.
Enter the following array formula in D2, confirmed with Ctrl+Shift+Enter:
=INDEX($C$2:$C$100,MODE(IF($B$2:$B$100=B2,MATCH($C$2:$C$100,$C$2:$C$100,0))))<>C2
Adjust the ranges if your data have more than 100 rows. Then fill down.
Column D will contain TRUE if the value in column C is different from the most frequently occurring choice for the same group. This assumes, of course, that the most occurring choice is the "correct" one, and that there is a most frequently occurring choice.
 
Thank you Hans! Much appreciated, I will test this out first thing tomorrow morning 🙂
