excelexcel-formulapivottranspose

transpose a table with multiple values in excel


I have a table that has ID and Description. One ID can have multiple rows of Description. I would like to change the format to a table where there will be 1 unique row for each ID and multiple columns for description. Is there a way to do this for a large data set using a formula in excel? Below is a screenshot of current state and desired output.

Current State and Desired Output


Solution

  • There is another way of solving this problem with the help of Pivot table.

    Create a pivot of your table and add 'ID' to Rows field and 'Description' to both Columns and Values field.

    Your pivot table should look something like this: this is the pivot table image

    After that, add this formula =IF(B3=1,B$2,"") in cell 'G3' and drag the formula.

    The whole solution will look something like this: this image shows the entire solution