Filter formula question

Hi, I want to ask how I can write this formula. I want to filter data in a table. I want the formula to find duplicate words in “columnA” which can be up to 10 duplicate words and I want to just leave one word out of those 10 words in the table but the word I want to leave is the one with the highest number in “column2” and lowest number in “column3”. Any suggestions how put a formula together for this please. Thank you

Hi @Sheryl_Dulnuan

Welcome to coda community !
Can you be more accurate about this quote above, is that the same word which may have the highest number in col2 and lowest in Col3 ? Or 2 differents numbers ? I would like a screenshot to better understand your data.

If you want to display only the word with highest value in Col2, you should write this formula, that will rank word according to col2 value, for all word that has the same name as current value. Then, your are interested with all words with RankCol2 = 1, as they correspond to the word with highest value in Col2. Please be careful, you may have several Rank#1 if the col2 value are the same

Then, just filter your RankCol2 column for value = 1

But to fully answer your issue I need :

  • to better understand your quote above
  • to know if two words can have the same value in Col2 and/or Col3
  • if a word has highest value in col2 but not lowest in col3, do I have to keep it or not ? If not, which word do you want to keep
  • etc…

Please let me know.