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 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 🙂