Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

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

share|improve this answer
    
thanks for your answer. I'm trying it and getting all 0's. –  Brian Jun 5 '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 5 '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. (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
    
@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. (4) Wow, nevermind, you're right it does work. I may have been thinking of a different formula combo. - Thanks, –  Scheballs Jun 6 '13 at 1:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.