In Excel, I want to convert the table on the top (Initial data) into the one at the bottom (Desired output). I want to group the items in the second row by the first row, and then generate one column per unique value of the first row and list the items of the corresponding group in that column.
Is there a way to do that without manually copying cells?
Table: Initial data
| Fruit | Banana |
| Fruit | Apple |
| Fruit | Grape |
| Vegetable | Spinach |
| Vegetable | Eggplant |
Table: Desired output
| Fruit | Vegetable |
|---|---|
| Banane | Spinach |
| Apple | Eggplant |
| Grape |
CodePudding user response：
If you have access to the UNIQUE and FILTER functions (Excel 365), you could accomplish this as follows.
Assuming your data is in A1:B5. In D1 enter:
=TRANSPOSE(UNIQUE($A$1:$A$5))
This will get you the unique values from the cells in A in D1:E1. Then in D2 enter:
=FILTER($B$1:$B$5,$A$1:$A$5=D1)
And drag to the right.
I.e. you do this:
Result:
