Forum Discussion
Unpivot Columns - Cleaning Up Data
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.
Anonymous ,
Check the attached file.
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 ,
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.
4 Replies
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 ,
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.
Anonymous ,
Check the attached file.
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.