Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I've got a spreadsheet with data like this:

Product   | Attribute
----------+----------
Product A | Cyan
Product B | Cyan
Product C | Cyan
Product A | Magenta
Product C | Magenta
Product B | Yellow
Product C | Yellow
Product A | Black
Product B | Black

What I'd like to do group everything by Column A and have Column B be a comma-delimited list of values that share Column A in common, like so:

Product   | Attribute
----------+--------------------------
Product A | Cyan,Magenta,Black
Product B | Cyan,Yellow,Black
Product C | Cyan,Magenta,Yellow,Black

Unfortunately, Pivot Tables only know how to work with number values, and the furthest it goes towards this is counting the number of times Column A occurs.

I was able to pull this off ultimately by importing the data into a MySQL database and using MySQL's GROUP_CONCAT(Attribute) function in a query with a GROUP BY Product clause, but after banging my head on my desk repeatedly while attempting to figure out an Excel solution.

For future reference, is this possible in Excel without macros? Whether it is or not, how would one pull this off?

share|improve this question

2 Answers 2

up vote 10 down vote accepted
   |     A     |     B
---+-----------+-----------
 1 |  PRODUCT  | ATTRIBUTE
 2 | Product A | Cyan
 3 | Product B | Cyan
 4 | Product C | Cyan
 5 | Product A | Magenta
 6 | Product C | Magenta
 7 | Product B | Yellow
 8 | Product C | Yellow
 9 | Product A | Black
10 | Product B | Black

Assuming row 1:1 is header row.

  1. Sort by column A to group by product

  2. Prepare data in comma-separated format in column C by entering into C2 the following formula and copy down to C3:C10.

    =IF(A2<>A1, B2, C1 & "," & B2)
    
  3. Identify useful rows by entering into D2 =A2<>A3 and copy down to D3:D10.

  4. Copy column C:D, then paste special as value (AltE-S-V-Enter). You will now get:

    Product A    Cyan       Cyan                   FALSE
    Product A    Magenta    Cyan,Magenta           FALSE
    Product A    Black      Cyan,Magenta,Black     TRUE
    Product B    Cyan       Cyan                   FALSE
    Product B    Yellow     Cyan,Yellow            FALSE
    Product B    Black      Cyan,Yellow,Black      TRUE
    Product C    Cyan       Cyan                   FALSE
    Product C    Magenta    Cyan,Magenta           FALSE
    Product C    Yellow     Cyan,Magenta,Yellow    TRUE
    
  5. Remove useless rows by filtering FALSE in column D with AutoFilter, then delete those rows.

  6. Finish. Column A & C is what you need.

share|improve this answer
    
That is using your imagination! Congrats! –  jorgebg Oct 16 '12 at 12:25

Here are a couple of approaches, both "non-macro"...

  1. With a small data set, after first sorting it by product (similar to GROUP BY Product), you could first copy the "Product" column, paste it elsewhere, then remove duplicates. Next, copy the "Attributes" for each product and "paste special, TRANSPOSE" next to each Product. Then concatenate a comma with each of your transposed attributes in a final results column. Admittedly all this "copy/paste special/transpose" would get old quickly if you have a long list of Products.

  2. If you have lots of data, using a few formulas you can work your way to the final result, as shown below. The formulas in F2, G2, H2, I2 and N2 are indicated by the blue arrows. Copy those to the rows below as needed. Note that J2:L2 use the same formula as I2. Also, the F2 formula refers to a named range "Products" that spans the range A:A .

enter image description here

share|improve this answer
    
These are good solutions, though only if the data set is small, like you say. Thanks! –  p0lar_bear Jul 24 '12 at 20:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.