将重复条目与Excel中的唯一数据组合 [英] Combining duplicate entries with unique data in Excel

问题描述

我有一个Excel数据库，我正在尝试避免做一些手动组合的重复数据。我有一堆清单与标签栏基本相同。我想要做的是将这5个列表合并到一个列表中，使这些类别在一个单元格中以逗号分隔列表。

转动这个

到这个

有没有办法实现这一点？我的文档有几千个列表，所以我显然是想避免手动编辑路由。我是一个Excel新手，所以任何手持或教程，你可以指出我将不胜感激。

解决方案

这也可以使用公式来完成对于我的例子，数据需要按照第一列进行排序，并且需要一个标题行。

您将需要两列（C & D）。首先，如果列A中的数据与上面的行相同，则添加一个基本上表示为连接列B中的数据的公式，否则重置连接。下一列将包含一个公式，以确定最终连接，以便稍后进行排序。

这是我将如何处理列和A和B（再次，数据需要按照列A排序，需要有一个标题行）：

这是结果。现在我将复制整个范围并将值粘贴到另一个工作表。列D为零的行是我想要使用的。按列D进行排序将浮动到顶部。

I have an Excel database and I'm trying avoid doing some manual combining of duplicate data. I've got a bunch of listings that are essentially the same aside from the tags column. What I'd like to have it do is combine these 5 listings into 1 listing, making the categories a comma separated list in a single cell.

Turn this

into this

Is there any way of achieving this? My document has a couple thousand listings, so I'm obviously trying to avoid the manual edit route. I'm an Excel novice, so any hand holding or tutorials you could point me to would be appreciated.

解决方案

This can also be done using formulas. For my example to work, the data would need to be sorted by the first column and there would need to be a header row.

You would need two more columns (C & D). First, add a formula that essentially says to concatenate the data in column B if data in column A is the same as the row above it, otherwise reset the concatenation. The next column would contain a formula to identify the final concatenations so you can sort later.

This is how I would do it with listings and categories in columns A & B (again, the data would need to be sorted by column A and there would need to be a header row):

Here's the results. Now I would copy the entire range and paste values into another sheet. The rows with zero for column D is what I'd want to use. Sorting by column D would float them to the top.

这篇关于将重复条目与Excel中的唯一数据组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！