Ive used an index mode match formula to give me the most common value in a set of data if criteria is met, now I'm trying to use the filter function to filter out the most common result and output the new most common result in this list ( 2nd most common overall) below the cell that contains the most common result. I've managed to remove one result to find the second most common result but i cant get the formula right to filter out two or more results from the original list to give me 3rd, 4th ETC most common value
formula for most common - this is in cell CU690
=INDEX(Table1[PART/FAULT],MODE(IF(Table1[TOP TEN]="CRITERIA",MATCH(Table1[PART/FAULT],Table1[PART/FAULT],0))))
formula for second most common
=INDEX(FILTER(Table1[PART/FAULT],Table1[PART/FAULT]<>Sheet2!CU690,""),MODE(IF(Table1[TOP TEN]="CRITERIA",MATCH(FILTER(Table1[PART/FAULT],Table1[PART/FAULT]<>Sheet2!CU690,""),FILTER(Table1[PART/FAULT],Table1[PART/FAULT]<>Sheet2!CU690,""),0))))
