| 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.
-
Sort by column A to group by product
-
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)
-
Identify useful rows by entering into D2 =A2<>A3 and copy down to D3😃10.
-
Copy column C😃, 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
-
Remove useless rows by filtering FALSE in column D with AutoFilter, then delete those rows.
-
Finish. Column A & C is what you need.
