Search code examples
excelvbaexcel-formulapowerqueryunpivot

How to transpose varied columns in excel?

enter image description here

I've ton of rows like above.

Below table is ideal result.

I had worked with v-lookup, pivot table with no luck.

Is there any way to correctly match varied columns?

Solution

  • If you want to get from this:

    img1

    ...to this:

    img2

    Then what you want to do is called an Unpivot.

    1. Highlight your data and hit CTRL+T to make it into a table. (Check the "my data has headings" if there are headings, or un-check it if it does not, like your example.)
    2. On the DATA tab click FROM TABLE/RANGE. This will open PowerQuery.
    3. You need to select all columns EXCEPT for the first one, so click the 2nd column and then push and hold CTRL while you click each of the others to the right of it.
    4. on the TRANSFORM tab click UNPIVOT COLUMNS.
    5. Click the X in the top-right of the screen, and click "KEEP CHANGES"

    You're Done! (...so easy that it's fun!)

    Image

    • More example and links to more detailed information at my other answer here.