Geek Answers Handbook

Display all matching values ​​in a single cell, separated by a comma

I have two columns of data in an Excel 2010 spreadsheet. In column A is the category, and in column B is the value. In column B, for each unique category in column A there will be several values.

What I want to achieve on a separate sheet is to display all the values ​​for each each unique category in a separate cell, separated by a comma (or semicolon, etc.).

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 need to display the following on 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 done using a formula? Of course, Vlookup will only find the first matching value. I searched for it, but the individual search terms involved in the query are so general that I came across unsatisfactory results.

+4
1

( ):

=IF(B2<>B3,"","x") A2 ( Category B1). D2 =IF(B1=B2,D1&", "&C2,C2) , . . A x . Unfilter ColumnA.

+4

More articles:


All Articles