Unpivot Columns - Cleaning Up Data

[Anonymous]

I have an excel table that needs a lot of clean up. I am trying to use unpivot and I am at a total loss of how to even start. My data looks like this:

---------------------------------------DATE1----------------------------------DATE2-----------------------

Role--------Project-----Person------AVL---------BDG---------NET---------AVL---------BDG---------NET

ENG--------P1----------Sam--------10-----------8-------------2------------8------------5-------------3

ENG--------P2----------Sam--------8------------7-------------1------------3------------2-------------1

ENG--------P1----------Tom--------11-----------7-------------4------------2------------1-------------1

Please note that the dates are only in the cell directly above AVL but I they apply to BDG and NET as well.

I need the data to look like this:

Role--------Project----Person-----Type-------Date-------Value-------

ENG--------P1---------Sam--------AVL--------DATE1-----10----------

ENG--------P1---------Sam--------BDG-------DATE1------8-----------

ENG--------P1---------Sam--------NET-------DATE1-------2----------

ENG--------P1---------Sam--------AVL--------DATE2------8----------

ENG--------P1---------Sam--------BDG-------DATE2------5-----------

ENG--------P1---------Sam--------NET-------DATE2-------3----------

ENG--------P2---------Sam--------AVL--------DATE1------8----------

ENG--------P2---------Sam--------BDG-------DATE1------7-----------

ENG--------P2---------Sam--------NET-------DATE1-------1----------

Etc...

Can anyone point me in the right direction? If I have to, I would be fine creating seperate tables for AVL, BDG, and NET and then linking them together by the date, but I would prefer to have just one table.

[camargos88 · Accepted solution]

@Anonymous ,

Check the attached file.

[CNENFRNL · Accepted solution]

Hi, @Anonymous , the tricky part is to split dates and to assign them to each group of types. You might want to try the steps applied in the attach file.

[CNENFRNL · Accepted solution]

Hi, @Anonymous , the tricky part is to split dates and to assign them to each group of types. You might want to try the steps applied in the attach file.

[Anonymous · Accepted solution]

@CNENFRNL ,

Thank you. This solution also works and in fewer steps than the post I accepted as the solution. I selected the other post because they responded first.

[camargos88 · Accepted solution]

@Anonymous ,

Check the attached file.

[Anonymous]

@camargos88 ,

Thank you! This is exactly what I needed and I learned a lot by stepping throught he file in the query editor! It was sucha a breaktrhough for me that I was up until 4:15 AM last night working. You got me unstuck.