2

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.

Scott - Слава Україні
  • 22,532
Brian
  • 171

1 Answers1

2

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.

EDIT* Added a Photo

Read more about this formula combo here ► How to extract a unique distinct list from a column in excel

EDIT* Added a Photo

Community
  • 1
Scheballs
  • 413