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

Question

I need to show each description as combined separate column which point to a unique code. Each code has multiple descriptions and they may be blank and I need to show the blanks

I need some kind of macro or pivot table because i recieve multiple files with the wrong format.

Thanks I have an Excel table that looks like this:

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

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

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

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

Answer 1

You have got your data in the cross tab format, and you want it to be unpivoted. You can make use of this link which explains how to go about it:

https://www.excel-university.com/unpivot-excel-data/

Tips:

1. This will result in an extra column equivalent to the "Attribute" in the above example, you can simply delete it to get your desired two column format.

2. Just using the unpivot will, in all probability, not show create rows for the blank descriptions. To counter that, Select the three columns (Description1, Description2, Description3) in your original data sheet, and substitute the blank values with a string pattern, say "blank". Ensure that this string pattern doesn't already exist your existing description values. At the end of the unpivot, replace back the same string pattern, "blank" in this case, with no values.

Hope this helps.