Excel group transposed columns by date












0















I'm having trouble transposing data to get the format that I'd like. I have pay period end dates in one column and employee names in the other column, and I'd like format it so that each column header is a pay period date and the rows are the employees on the payroll for that period.



Here's the data as it is formatted now:



pay period   name
3/6/2015 John Smith
3/6/2015 Jane Smith
4/5/2015 John Smith
4/5/2015 Jane Smith
4/5/2015 Joe Smith
4/19/2015 John Smith
4/19/2015 Jane Smith
4/19/2015 Joe Smith
5/3/2015 John Smith
5/3/2015 Jane Smith
5/3/2015 Joe Smith
5/3/2015 Julie Smith


And here is how I would like it to be formatted:



3/6/2015    4/5/2015    4/19/2015   5/3/2015
John Smith John Smith John Smith John Smith
Jane Smith Joe Smith Joe Smith Joe Smith
Jane Smith Jane Smith Jane Smith
Julie Smith


I've tried using Get & Transform/Power Query to transpose the data using Pivot Columns, using the names in the pay period column to create new columns, with "name" as the values column, and aggregation turned off. Instead of getting the format I want, I get the pay period dates as column headers and then a single cell reading "error" in row 2. I have checked the column types to verify that "pay period" has date as its type and "name" has text as its type.



I'm not sure if I'm simply doing the query wring or if I should go about this in another way. Any and all help is greatly appreciated.










share|improve this question



























    0















    I'm having trouble transposing data to get the format that I'd like. I have pay period end dates in one column and employee names in the other column, and I'd like format it so that each column header is a pay period date and the rows are the employees on the payroll for that period.



    Here's the data as it is formatted now:



    pay period   name
    3/6/2015 John Smith
    3/6/2015 Jane Smith
    4/5/2015 John Smith
    4/5/2015 Jane Smith
    4/5/2015 Joe Smith
    4/19/2015 John Smith
    4/19/2015 Jane Smith
    4/19/2015 Joe Smith
    5/3/2015 John Smith
    5/3/2015 Jane Smith
    5/3/2015 Joe Smith
    5/3/2015 Julie Smith


    And here is how I would like it to be formatted:



    3/6/2015    4/5/2015    4/19/2015   5/3/2015
    John Smith John Smith John Smith John Smith
    Jane Smith Joe Smith Joe Smith Joe Smith
    Jane Smith Jane Smith Jane Smith
    Julie Smith


    I've tried using Get & Transform/Power Query to transpose the data using Pivot Columns, using the names in the pay period column to create new columns, with "name" as the values column, and aggregation turned off. Instead of getting the format I want, I get the pay period dates as column headers and then a single cell reading "error" in row 2. I have checked the column types to verify that "pay period" has date as its type and "name" has text as its type.



    I'm not sure if I'm simply doing the query wring or if I should go about this in another way. Any and all help is greatly appreciated.










    share|improve this question

























      0












      0








      0








      I'm having trouble transposing data to get the format that I'd like. I have pay period end dates in one column and employee names in the other column, and I'd like format it so that each column header is a pay period date and the rows are the employees on the payroll for that period.



      Here's the data as it is formatted now:



      pay period   name
      3/6/2015 John Smith
      3/6/2015 Jane Smith
      4/5/2015 John Smith
      4/5/2015 Jane Smith
      4/5/2015 Joe Smith
      4/19/2015 John Smith
      4/19/2015 Jane Smith
      4/19/2015 Joe Smith
      5/3/2015 John Smith
      5/3/2015 Jane Smith
      5/3/2015 Joe Smith
      5/3/2015 Julie Smith


      And here is how I would like it to be formatted:



      3/6/2015    4/5/2015    4/19/2015   5/3/2015
      John Smith John Smith John Smith John Smith
      Jane Smith Joe Smith Joe Smith Joe Smith
      Jane Smith Jane Smith Jane Smith
      Julie Smith


      I've tried using Get & Transform/Power Query to transpose the data using Pivot Columns, using the names in the pay period column to create new columns, with "name" as the values column, and aggregation turned off. Instead of getting the format I want, I get the pay period dates as column headers and then a single cell reading "error" in row 2. I have checked the column types to verify that "pay period" has date as its type and "name" has text as its type.



      I'm not sure if I'm simply doing the query wring or if I should go about this in another way. Any and all help is greatly appreciated.










      share|improve this question














      I'm having trouble transposing data to get the format that I'd like. I have pay period end dates in one column and employee names in the other column, and I'd like format it so that each column header is a pay period date and the rows are the employees on the payroll for that period.



      Here's the data as it is formatted now:



      pay period   name
      3/6/2015 John Smith
      3/6/2015 Jane Smith
      4/5/2015 John Smith
      4/5/2015 Jane Smith
      4/5/2015 Joe Smith
      4/19/2015 John Smith
      4/19/2015 Jane Smith
      4/19/2015 Joe Smith
      5/3/2015 John Smith
      5/3/2015 Jane Smith
      5/3/2015 Joe Smith
      5/3/2015 Julie Smith


      And here is how I would like it to be formatted:



      3/6/2015    4/5/2015    4/19/2015   5/3/2015
      John Smith John Smith John Smith John Smith
      Jane Smith Joe Smith Joe Smith Joe Smith
      Jane Smith Jane Smith Jane Smith
      Julie Smith


      I've tried using Get & Transform/Power Query to transpose the data using Pivot Columns, using the names in the pay period column to create new columns, with "name" as the values column, and aggregation turned off. Instead of getting the format I want, I get the pay period dates as column headers and then a single cell reading "error" in row 2. I have checked the column types to verify that "pay period" has date as its type and "name" has text as its type.



      I'm not sure if I'm simply doing the query wring or if I should go about this in another way. Any and all help is greatly appreciated.







      microsoft-excel worksheet-function pivot-table power-query






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Jan 2 at 21:29









      plshelpmewithexcelplshelpmewithexcel

      132




      132






















          1 Answer
          1






          active

          oldest

          votes


















          0














          I too wish for more options with pivot tables. Here is a vba sub to do this transposition for you. This is just the basics and could be more robust. This does not sort your dates or your names and takes them as is. It adds a sheet and takes data from the current active sheet.



          Sub Transpose()

          Dim dateDict As Object
          Dim numRows As Long
          Dim rowPos As Long
          Dim dateVal As String

          Dim currSheet As Worksheet
          Set currSheet = ActiveSheet

          Set dateDict = CreateObject("Scripting.Dictionary")
          numRows = currSheet.Range("A1").CurrentRegion.Rows.Count
          rowPos = 2
          Do While rowPos <= numRows
          dateVal = currSheet.Cells(rowPos, 1).Value2
          If dateDict.exists(dateVal) Then
          Else
          dateDict.Add dateVal, 0
          End If
          rowPos = rowPos + 1
          Loop

          Dim mySheet As Worksheet
          Set mySheet = ThisWorkbook.Worksheets.Add

          Dim i As Long
          Dim newPos As Long
          Dim Column As Long
          Column = 0
          For Each myKey In dateDict
          newPos = 2
          rowPos = 2
          Column = Column + 1
          mySheet.Cells(1, Column).Value2 = myKey
          Do While rowPos <= numRows
          If currSheet.Cells(rowPos, 1).Value2 = myKey Then
          mySheet.Cells(newPos, Column).Value2 = currSheet.Cells(rowPos, 2).Value2
          newPos = newPos + 1
          End If
          rowPos = rowPos + 1
          Loop
          Next

          End Sub





          share|improve this answer























            Your Answer








            StackExchange.ready(function() {
            var channelOptions = {
            tags: "".split(" "),
            id: "3"
            };
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function() {
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled) {
            StackExchange.using("snippets", function() {
            createEditor();
            });
            }
            else {
            createEditor();
            }
            });

            function createEditor() {
            StackExchange.prepareEditor({
            heartbeatType: 'answer',
            autoActivateHeartbeat: false,
            convertImagesToLinks: true,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: 10,
            bindNavPrevention: true,
            postfix: "",
            imageUploader: {
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            },
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            });


            }
            });














            draft saved

            draft discarded


















            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1389953%2fexcel-group-transposed-columns-by-date%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown

























            1 Answer
            1






            active

            oldest

            votes








            1 Answer
            1






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            0














            I too wish for more options with pivot tables. Here is a vba sub to do this transposition for you. This is just the basics and could be more robust. This does not sort your dates or your names and takes them as is. It adds a sheet and takes data from the current active sheet.



            Sub Transpose()

            Dim dateDict As Object
            Dim numRows As Long
            Dim rowPos As Long
            Dim dateVal As String

            Dim currSheet As Worksheet
            Set currSheet = ActiveSheet

            Set dateDict = CreateObject("Scripting.Dictionary")
            numRows = currSheet.Range("A1").CurrentRegion.Rows.Count
            rowPos = 2
            Do While rowPos <= numRows
            dateVal = currSheet.Cells(rowPos, 1).Value2
            If dateDict.exists(dateVal) Then
            Else
            dateDict.Add dateVal, 0
            End If
            rowPos = rowPos + 1
            Loop

            Dim mySheet As Worksheet
            Set mySheet = ThisWorkbook.Worksheets.Add

            Dim i As Long
            Dim newPos As Long
            Dim Column As Long
            Column = 0
            For Each myKey In dateDict
            newPos = 2
            rowPos = 2
            Column = Column + 1
            mySheet.Cells(1, Column).Value2 = myKey
            Do While rowPos <= numRows
            If currSheet.Cells(rowPos, 1).Value2 = myKey Then
            mySheet.Cells(newPos, Column).Value2 = currSheet.Cells(rowPos, 2).Value2
            newPos = newPos + 1
            End If
            rowPos = rowPos + 1
            Loop
            Next

            End Sub





            share|improve this answer




























              0














              I too wish for more options with pivot tables. Here is a vba sub to do this transposition for you. This is just the basics and could be more robust. This does not sort your dates or your names and takes them as is. It adds a sheet and takes data from the current active sheet.



              Sub Transpose()

              Dim dateDict As Object
              Dim numRows As Long
              Dim rowPos As Long
              Dim dateVal As String

              Dim currSheet As Worksheet
              Set currSheet = ActiveSheet

              Set dateDict = CreateObject("Scripting.Dictionary")
              numRows = currSheet.Range("A1").CurrentRegion.Rows.Count
              rowPos = 2
              Do While rowPos <= numRows
              dateVal = currSheet.Cells(rowPos, 1).Value2
              If dateDict.exists(dateVal) Then
              Else
              dateDict.Add dateVal, 0
              End If
              rowPos = rowPos + 1
              Loop

              Dim mySheet As Worksheet
              Set mySheet = ThisWorkbook.Worksheets.Add

              Dim i As Long
              Dim newPos As Long
              Dim Column As Long
              Column = 0
              For Each myKey In dateDict
              newPos = 2
              rowPos = 2
              Column = Column + 1
              mySheet.Cells(1, Column).Value2 = myKey
              Do While rowPos <= numRows
              If currSheet.Cells(rowPos, 1).Value2 = myKey Then
              mySheet.Cells(newPos, Column).Value2 = currSheet.Cells(rowPos, 2).Value2
              newPos = newPos + 1
              End If
              rowPos = rowPos + 1
              Loop
              Next

              End Sub





              share|improve this answer


























                0












                0








                0







                I too wish for more options with pivot tables. Here is a vba sub to do this transposition for you. This is just the basics and could be more robust. This does not sort your dates or your names and takes them as is. It adds a sheet and takes data from the current active sheet.



                Sub Transpose()

                Dim dateDict As Object
                Dim numRows As Long
                Dim rowPos As Long
                Dim dateVal As String

                Dim currSheet As Worksheet
                Set currSheet = ActiveSheet

                Set dateDict = CreateObject("Scripting.Dictionary")
                numRows = currSheet.Range("A1").CurrentRegion.Rows.Count
                rowPos = 2
                Do While rowPos <= numRows
                dateVal = currSheet.Cells(rowPos, 1).Value2
                If dateDict.exists(dateVal) Then
                Else
                dateDict.Add dateVal, 0
                End If
                rowPos = rowPos + 1
                Loop

                Dim mySheet As Worksheet
                Set mySheet = ThisWorkbook.Worksheets.Add

                Dim i As Long
                Dim newPos As Long
                Dim Column As Long
                Column = 0
                For Each myKey In dateDict
                newPos = 2
                rowPos = 2
                Column = Column + 1
                mySheet.Cells(1, Column).Value2 = myKey
                Do While rowPos <= numRows
                If currSheet.Cells(rowPos, 1).Value2 = myKey Then
                mySheet.Cells(newPos, Column).Value2 = currSheet.Cells(rowPos, 2).Value2
                newPos = newPos + 1
                End If
                rowPos = rowPos + 1
                Loop
                Next

                End Sub





                share|improve this answer













                I too wish for more options with pivot tables. Here is a vba sub to do this transposition for you. This is just the basics and could be more robust. This does not sort your dates or your names and takes them as is. It adds a sheet and takes data from the current active sheet.



                Sub Transpose()

                Dim dateDict As Object
                Dim numRows As Long
                Dim rowPos As Long
                Dim dateVal As String

                Dim currSheet As Worksheet
                Set currSheet = ActiveSheet

                Set dateDict = CreateObject("Scripting.Dictionary")
                numRows = currSheet.Range("A1").CurrentRegion.Rows.Count
                rowPos = 2
                Do While rowPos <= numRows
                dateVal = currSheet.Cells(rowPos, 1).Value2
                If dateDict.exists(dateVal) Then
                Else
                dateDict.Add dateVal, 0
                End If
                rowPos = rowPos + 1
                Loop

                Dim mySheet As Worksheet
                Set mySheet = ThisWorkbook.Worksheets.Add

                Dim i As Long
                Dim newPos As Long
                Dim Column As Long
                Column = 0
                For Each myKey In dateDict
                newPos = 2
                rowPos = 2
                Column = Column + 1
                mySheet.Cells(1, Column).Value2 = myKey
                Do While rowPos <= numRows
                If currSheet.Cells(rowPos, 1).Value2 = myKey Then
                mySheet.Cells(newPos, Column).Value2 = currSheet.Cells(rowPos, 2).Value2
                newPos = newPos + 1
                End If
                rowPos = rowPos + 1
                Loop
                Next

                End Sub






                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Jan 4 at 18:14









                BrianBrian

                1445




                1445






























                    draft saved

                    draft discarded




















































                    Thanks for contributing an answer to Super User!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid



                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.


                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function () {
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1389953%2fexcel-group-transposed-columns-by-date%23new-answer', 'question_page');
                    }
                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    Popular posts from this blog

                    Probability when a professor distributes a quiz and homework assignment to a class of n students.

                    Aardman Animations

                    Are they similar matrix