公式从列表中删除一些但不是全部的重复项

I have a list containing different words and numbers, a sample of which is as follows:

我有一个包含不同单词和数字的列表,其示例如下:

Green
Blue
14
Green
Yellow
11
Yellow
Green
Green
14

I would like to create a parallel list that removes every unique word/number as well as every second instance of a word/number - so removes some duplicates but not all. To continue with the above example, the final list would look as follows:

我想创建一个并行列表,删除每个唯一的单词/数字以及单词/数字的每个第二个实例 - 因此删除一些重复但不是全部。要继续上面的示例,最终列表将如下所示:

Green
14
Yellow
Green

As there are 4 'Green' in the original list, there are only two in the final list. There are 2 'Yellow' in the original and so just one in the final, and there are only single instances of Blue and 11, so these are removed from the final list.

由于原始列表中有4个“绿色”,因此最终列表中只有两个。原始版本中有2个“黄色”,因此在最终版本中只有一个,并且只有蓝色和11的单个实例,因此这些将从最终列表中删除。

Is there a formula or methodology to do so? Unfortunately I haven't been able to think of something clever myself.

有这样的公式或方法吗?不幸的是,我自己也无法想到一些聪明的事情。

Many thanks in advance!

提前谢谢了!

1 个解决方案

#1

=OR(COUNTIFS($A:$A,$A2)=1,INT(COUNTIFS($A$2:$A2,$A2)/2)*2=COUNTIFS($A$2:$A2,$A2))

If it's either an even duplicate or a unique, then it is marked as TRUE. Make sure you delete all TRUE values at once/disable automatic calculation, otherwise it'll mark the odd duplicates too after deleting prior occurrences.

如果它是偶数重复或唯一,则标记为TRUE。确保一次删除所有TRUE值/禁用自动计算,否则在删除之前的事件后它也会标记奇数重复项。