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 that group (ex. Group 1, group 2, group 3), and column 3 has one of the following three choices (A, B, C).

How do I create a script or formula that identifies entries that have the same group number (column 2) but not the same value in column 3? 

 

Theoretically, all entries with the same group number should also have the same value in column 3.  I want to quality control and check to make sure that this is the case.  My data can potentially be very voluminous with thousands of different group IDs.  What would be the best way to check for these inconsistencies?  I will likely have too many different values in column 2 to make graphs and charts useful.   I need a solution to help me easily identify all group values with inconsistent values in column 3.

 

I’m not very advanced with excel and need some guidance.  Studying the documentation and Youtube has not provided me with any answers thus far.  Any recommendations and advice would be greatly appreciated. 

Formulas and Functions

9 Replies

Sort By
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Apr 18, 2021

    LAVic15 

    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.

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

      • LAVic15's avatar
        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

    • LAVic15's avatar
      LAVic15
      Copper Contributor
      Apr 18, 2021

      HansVogelaar 

       

      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 GroupChoice
      15-11A
      15-21A
      15-31B
      15-42C
      15-52C
      15-62C
      15-72C
      15-82B
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Apr 18, 2021

        LAVic15 

        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.

         

Resources