在一个逗号分隔的单元格中显示所有匹配的值 [英] Display all matching values in one comma separated cell

问题描述

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

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.

我想在另一张纸上实现的是在一个逗号(或分号等)分隔的单元格中显示每个唯一类别的所有值.

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 |
---------------------------------------

这可以通过公式来实现吗?当然，Vlookup只会找到第一个匹配值.我已经用Google搜索了它，但是查询中涉及的单个搜索词是如此通用，以至于我被不适当的结果所淹没.

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.

推荐答案

请尝试(在另一张纸上的副本中):

Please try (in a copy on another sheet):

在A2中的左侧插入 = IF(B2< B3，"，"x")的列(假设 Category 在B1中).在D2中，放入 = IF(B1 = B2，D1&，"& C2，C2)并复制两个公式以适合.在顶部复制并粘贴特殊值.在ColumnA上过滤 x 并删除选定的行.取消过滤并删除ColumnA.

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.

这篇关于在一个逗号分隔的单元格中显示所有匹配的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！