Excel，我有一个大列表(44000条记录)，我想将它们分成2个列表，其中1个具有唯一值，而1个具有重复值 [英] Excel, i have a big list (44000 records), i would like to sepperate them into 2 lists, 1 with unique values and 1 with duplicate value's

问题描述

我的列表看起来像这样:

My list looks somewhat like this:

1. Unique1
2. double1
3. double2
4. unique2
5. double1
6. unique3
7. double2
8. unique4
9. double3
10. double4

以此类推.

我想要的输出:

列表1

 1.unique1
 2.unique2
 3.unique3
 4.unique4

列表2

 1.double1
 2.double2
 3.double3
 4.double4

我只发现了诸如remove/hilight重复项或count/hilight唯一值之类的东西.但是我需要在另一个列表中显示这些值.

I've only found things like remove/hilight duplicates or count/hilight unique values. But I need to display the values in another list.

推荐答案

从数据中取出数据透视表.将感兴趣的字段作为行字段"和值"字段放入其中，并确保要应用的聚合为计数".(如果您输入的是文本，则默认为默认值.)

Make a PivotTable out of the data. Put the field of interest in it as both a Row Field and as a Values field, and make sure that the aggregation being applied is a Count. (It will be by default if your things are text).

任何计数为1的东西都是唯一的.计数> 1的都不是.

Anything with a Count of 1 is unique. Anything with a count > 1 is not.

然后，您可以选择复制数据透视表，并设置值"过滤器以显示count等于1的位置，count大于1的位置，例如:

You can then optionally copy the PivotTable, and set a Values filter to show where the count = 1 in one, and count > 1 in the other, like so:

这篇关于Excel，我有一个大列表(44000条记录)，我想将它们分成2个列表，其中1个具有唯一值，而1个具有重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！