Any Easy VBA to do the transpose?

I might suggest that this is not the best spreadsheet to learn VBA on. The data are poorly organized. What is eye-appealing for humans is often very complicated for the machine.

In this case the dates are strung out over the columns. The way the computer would like the data is:
Items | Product | Date | Costs
and in fact, that's what I'd do if I were programming this: read what you have and reformat it to look like this and then a pivot table could take care of just about any way you want to present the data.

To program this, you would have to know how to address pages, loop through ranges and populate a table. If you feel comfortable with this, we can give it a try.

Originally Posted by dflak

I might suggest that this is not the best spreadsheet to learn VBA on. The data are poorly organized. What is eye-appealing for humans is often very complicated for the machine.

Isnt this what theyre trying to sort out though? or have I picked the OP up wrong?

The code below will transpose the data into what you want, Ive commented the code where possible to explain what it does.

Sub Transposer()
Dim xRow, xCol, LastRow, LastCol, outputrow As Double

Dim sht, outsht As Worksheet
Dim strItem, strProd, strDate As String

'setup the range to be analysed
Set sht = Worksheets("Data")
Set outsht = Worksheets("Transposed_Data")
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row 'works out how many rows there are in the Data sheet
LastCol = sht.Cells(3, sht.Columns.Count).End(xlToLeft).Column 'and the same for the number of columns

outputrow = 1  'set up where the output data in Transposed_Data starts from.
For xRow = 4 To LastRow  'run through a loop of all the rows in the data sheet.
strItem = Cells(xRow, 1).Value 
strProd = Cells(xRow, 2).Value

    For xCol = 3 To LastCol  'loop through all the columns on that row
    If (Val(Cells(xRow, xCol).Value) > 0) Then  'if theres a value in the cell then we want to output the row to the Transposed_Data sheet.
        strDate = Cells(3, xCol).Value
        
        'output the values into the first 4 columns       
        outsht.Cells(outputrow, 1).Value = strItem
        outsht.Cells(outputrow, 2).Value = strProd
        outsht.Cells(outputrow, 3).Value = strDate
        outsht.Cells(outputrow, 4).Value = sht.Cells(xRow, xCol).Value
        outputrow = outputrow + 1   'move to the next row of the output sheet
    End If
    Next xCol   'go to the next column
Next xRow     'go to the next row
End Sub