在一个逗号分隔的单元格中显示所有匹配的值

I have two columns of data in an Excel 2010 spreadsheet. In Column A is a category, and in Column B is a value. There will be multiple values in Column B for each unique category in Column A.

我在Excel 2010电子表格中有两列数据。A列是一个类别，B列是一个值。对于A列中的每个唯一类别，在B列中将有多个值。

What I want to achieve in a separate sheet is to display all of the values for each each unique category in one comma (or semi-colon etc) separated cell.

我想在单独的表中实现的是在一个逗号(或分号)分隔的单元格中显示每个唯一类别的所有值。

For example, if my first sheet looks like this:

例如，如果我的第一张表是这样的:

----------------------
| Category | Value   |
----------------------
| Cat1     | Val A   |
| Cat1     | Val B   |
| Cat1     | Val C   |
| Cat2     | Val D   |
| Cat3     | Val E   |
| Cat3     | Val F   |
| Cat3     | Val G   |
| Cat3     | Val H   |
----------------------

I'd want to display the following in another sheet:

我想在另一页中显示以下内容:

---------------------------------------
| Category | Value                    |
---------------------------------------
| Cat1     | Val A,Val B,Val C        |
| Cat2     | Val D                    |
| Cat3     | Val E,Val F,Val G, Val H |
---------------------------------------

Can this be achieved with a formula? Vlookup will only find the first matching value, of course. I've Googled it, but the individual search terms involved in the query are so generic I'm getting swamped with inappropriate results.

这能通过公式来实现吗?当然，Vlookup只能找到第一个匹配值。我已经在google上搜索过了，但是查询中涉及到的各个搜索项都是很普通的，我被不恰当的结果淹没了。

1 个解决方案

#1

Please try (in a copy on another sheet):

请尝试(另一页副本):

Insert a column on the left with =IF(B2<>B3,"","x") in A2 (assuming Category is in B1). In D2 put =IF(B1=B2,D1&", "&C2,C2) and copy both formulae down to suit. Copy and Paste Special Values over the top. Filter on ColumnA for x and delete selected rows. Unfilter and delete ColumnA.

在A2(假设类别为B1)中插入左侧的一个列=IF(B2<>B3，“”，“x”)。在D2中代入=IF(B1=B2,D1&"， "&C2,C2 ")并将两个公式复制到适合的位置。复制并粘贴特殊值到顶部。在ColumnA上筛选x并删除选定的行。Unfilter和删除一列圆柱。