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 - Слава Україні
  • 21,399
  • 46
  • 64
  • 121
Brian
  • 171
  • 1
  • 3
  • 4

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
  • 403
  • 4
  • 11
  • thanks for your answer. I'm trying it and getting all 0's. – Brian Jun 05 '13 at 19:24
  • Did you enter it as an array with CTRL+SHIFT+ENTER? I tried it again and it works for me. – Scheballs Jun 05 '13 at 20:58
  • 1
    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](http://i.stack.imgur.com/3Yny5.png).  (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 06 '13 at 00:36
  • @Scott (1) Thanks, (2) I was defining where the data is and where I created the new table since the formula uses cell references that can be different. (3) Read more here [How to extract a unique distinct list from a column in excel](http://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/). (4) Wow, nevermind, you're right it does work. I may have been thinking of a different formula combo. - Thanks, – Scheballs Jun 06 '13 at 01:52