Create Pivot Table from downloaded data

[excel?]

I have data downloaded into an Excel worksheet and need to pull into a pivot table. I cannot figure out how to pull into a pivot table due to the layout of the data.

Any Ideas on how to get this into a Pivot Table?

Here is an example of what the data looks like:

____A_____B___C_____D______E______F_______G____
1__________________[ 09/19/2016 ]___[ 09/20/2016 ]__
2__ID #_Name__Unit__Hours__Dollars__Hours__Dollars
3__100__John___107__9.83__$196.60__8.80___$176.00
4__110__Nancy__234__9.93__$198.60__8.15___$163.00
5__120__Steve__748__8.95__$179.00__8.23___$164.60
6__130__Lisa____448__8.82__$176.40__7.23___$144.60

** The Date in Row 2 is merged across 2 columns and Brackets added to demonstrate this.
** Underline added to maintain spacing between columns

I am wanting to have a Pivot Table that allows me to move the Fields around and get Totals/Sub-totals depending on what I am trying to look at, but I cannot figure out how to have the Table recognize the Dates as a usable field and the Hours and Dollar to be a Single Field. It tries to label them as Hours, Hours1, Hours2 etc..

I appreciate any help you can give.
Thank you

 

[ask2tsp]

You cannot do that directly. The basis for a pivot table is a list. A number of columns, preferably with a column header.

To turn this

Excel Workbook
	A	B	C	D	E	F	G
1	*	*	*	9/20/16		9/21/16
2	ID#	Name	Unit	Hours	Dollars	Hours	Dollars
3	100	John	107	9,83	$ 196,60	8,80	$ 176,00
4	110	Nancy	234	9,93	$ 198,60	8,15	$ 163,00
5	120	Steve	748	8,95	$ 179,00	8,23	$ 164,60
6	130	Lisa	448	8,82	$ 176,40	7,23	$ 144,60
import

into this

Excel Workbook
	A	B	C	D	E	F
1	ID #	Name	Unit	Date	Hours	Dollars
2	100	John	107	9/20/16	9,83	$ 196,60
3	100	John	107	9/21/16	8,80	$ 176,00
4	110	Nancy	234	9/20/16	9,93	$ 198,60
5	110	Nancy	234	9/21/16	8,15	$ 163,00
6	120	Steve	748	9/20/16	8,95	$ 179,00
7	120	Steve	748	9/21/16	8,23	$ 164,60
8	130	Lisa	448	9/20/16	8,82	$ 176,40
9	130	Lisa	448	9/21/16	7,23	$ 144,60
list

can be done with

Option Explicit

Sub importToList()
    Dim shtImport   As Worksheet
    Dim shtList     As Worksheet
    Dim importRange As Range
    Dim importRow   As Long
    Dim listRow     As Long
    Dim importColumn   As Long
    Dim listColumn     As Long
    
    Set shtImport = ThisWorkbook.Worksheets("import") '<--- sheettab name
    Set shtList = ThisWorkbook.Worksheets("list")     '<--- sheettab name
    
    Set importRange = shtImport.UsedRange
    
    With shtList
        .UsedRange.ClearContents
        .Range("A1") = "ID #"
        .Range("B1") = "Name"
        .Range("C1") = "Unit"
        .Range("D1") = "Date"
        .Range("E1") = "Hours"
        .Range("F1") = "Dollars"
    End With
    
    listRow = 1
    For importRow = 3 To importRange.Rows.Count
      For importColumn = 4 To importRange.Columns.Count Step 2
        listRow = listRow + 1
        shtList.Cells(listRow, 1) = shtImport.Cells(importRow, 1)
        shtList.Cells(listRow, 2) = shtImport.Cells(importRow, 2)
        shtList.Cells(listRow, 3) = shtImport.Cells(importRow, 3)
        shtList.Cells(listRow, 4) = shtImport.Cells(1, importColumn)
        shtList.Cells(listRow, 5) = shtImport.Cells(importRow, importColumn)
        shtList.Cells(listRow, 6) = shtImport.Cells(importRow, importColumn + 1)
      Next importColumn
    Next importRow
End Sub

 

[ask2tsp]

Sorry, clicked 'post reply'too fast.

At the beginning of my post should be:
What you want cannot be done directly. The source data for a pivot is a list of rows and a fixed number of columns, where in a certain column the type of data is the same for all rows.

and at the end:
After which you can build your pivot from list.