# Excel - Finding all values in one column for unique values in another column

I have an Excel table that looks like this:

``````Code | Description
-------------------------
A    | Desc1
A    | Desc2
B    | Desc3
C    | Desc4
C    | Desc5
C    | Desc6
...
``````

I need to find all the `Descriptions` for each unique `Code`. For example I want a table that looks like this:

``````Code | Description1 | Description2 | Description3
-----------------------------------------------------
A    | Desc1        | Desc2        |
B    | Desc3        |              |
C    | Desc4        | Desc5        | Desc6
``````

Is there a way to do this in Excel? I tried Pivot Tables, but had no luck.

-

If your data is in `A1:B7` including headers as you show, enter whatever headers you want in `C1:F1`, but make sure the `Code` column values you are looking for is in `C2` and going down. Like `C3 = B` and `C4 = D`. If you don't know all the `Code` column values there is a simple unique extract formula that can get them from the `Code` column for you.

In `C2` enter this formula with Ctrl+Shift+Enter keys

``````=INDEX(\$A\$2:\$A\$7, MATCH(0, COUNTIF(\$C\$1:\$C1, \$A\$2:\$A\$7), 0))
``````

In `D2` enter this formula with Ctrl+Shift+Enter keys and then you can drag it to the right and down to populate your new table.

``````=IFERROR(INDEX(\$B\$2:\$B\$7, MATCH(0, COUNTIF(\$C2:C2,\$B\$2:\$B\$7)+IF(\$A\$2:\$A\$7<>\$C2, 1, 0), 0)), 0)
``````

Currently this will display a `0` in cells that do not have any new description to display. This also will only work if your original data is sorted by the `Code` column.Edit- Nevermind, it does work with unsorted values.

I hope this helps.

Several comments: (1) Good job! (2) I don’t understand what you’re saying in the first paragraph. (3) It would be nice if you would explain the formulas. (4) What do you mean, “This will work only if your original data is sorted by the `Code` column.”? I tried your formulas with unsorted codes and it worked just fine. (Note that I changed your formula to `=IFERROR(INDEX(\$B\$2:\$B\$7, MATCH(0, COUNTIF(\$C2:C2,\$B\$2:\$B\$7)+IF(\$A\$2:\$A\$7<>\$C2, 1, 0), 0)), "-")` to display dashes instead of zeroes; you could just as well say `""` and display blanks.) – Scott Jun 6 '13 at 0:36