Csdrhrt

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.

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