Special transpose

[frapi060]

Hi everyone. I have been searching the solution but couldnt find it. Do you know how can I pass from here:

[TABLE="width: 960"]
<colgroup><col span="12"></colgroup><tbody>[TR]
[TD]31/01/11[/TD]
[TD]28/02/11[/TD]
[TD]31/03/11[/TD]
[TD]30/04/11[/TD]
[TD]31/05/11[/TD]
[TD]30/06/11[/TD]
[TD]31/07/11[/TD]
[TD]31/08/11[/TD]
[TD]30/09/11[/TD]
[TD]31/10/11[/TD]
[TD]30/11/11[/TD]
[TD]31/12/11[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1.344[/TD]
[TD]1.060[/TD]
[TD]710[/TD]
[TD]845[/TD]
[TD]85[/TD]
[TD]315[/TD]
[TD]715[/TD]
[TD]247[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]906[/TD]
[TD]495[/TD]
[TD]720[/TD]
[TD]345[/TD]
[TD]155[/TD]
[TD]150[/TD]
[TD]505[/TD]
[TD]275[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1.180[/TD]
[TD]962[/TD]
[TD]930[/TD]
[TD]180[/TD]
[TD]558[/TD]
[TD]1.290[/TD]
[TD]785[/TD]
[TD]1.036[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]630[/TD]
[TD]1.115[/TD]
[TD]600[/TD]
[TD]430[/TD]
[TD]270[/TD]
[TD]160[/TD]
[TD]930[/TD]
[TD]340[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1.538[/TD]
[TD]565[/TD]
[TD]475[/TD]
[TD]510[/TD]
[TD]115[/TD]
[TD]430[/TD]
[TD]250[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1.813[/TD]
[TD]582[/TD]
[TD]605[/TD]
[TD]545[/TD]
[TD]0[/TD]
[TD]435[/TD]
[TD]825[/TD]
[TD]405[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1.304[/TD]
[TD]545[/TD]
[TD]397[/TD]
[TD]270[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]525[/TD]
[TD]580[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]95[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]110[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]110[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]311[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]117[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]110[/TD]
[TD]207[/TD]
[TD]207[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]450[/TD]
[TD]7.577[/TD]
[TD]10.773[/TD]
[TD]4.971[/TD]
[TD]9.931[/TD]
[TD]7.995[/TD]
[TD]9.680[/TD]
[TD]9.404[/TD]
[/TR]
</tbody>[/TABLE]


to here:

[TABLE="width: 141"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]31/01/11[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]31/01/11[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]31/01/11[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]31/01/11[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]31/01/11[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]31/01/11[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]31/01/11[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]31/01/11[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]31/01/11[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]28/02/00[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]28/02/00[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]28/02/00[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]28/02/00[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]28/02/00[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]28/02/00[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]28/02/00[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]28/02/00[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]28/02/00[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]28/02/00[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD]xxx[/TD]
[/TR]
</tbody>[/TABLE]


Thank you

 

[Oeldere]

This macro does the job.

Sub CONVERTROWSTOCOL_Oeldere_revisted_new()

Dim rsht1 As Long, rsht2 As Long, i As Long, col As Long, wsTest As Worksheet, mr As Worksheet, ms As Worksheet

'check if sheet "ouput" already exist

Const strSheetName As String = "Output"

Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0
 
If wsTest Is Nothing Then
    Worksheets.Add.Name = strSheetName
End If

'set the data
                 

Set mr = Sheets("sheet1")                                  'this is the name of the source sheet
 
Set ms = Sheets("Output")                                       'this is the name of the destiny sheet

col = 1
'End set the data

    With ms
     .UsedRange.ClearContents
     .Range("A1:B1").Value = Array("Date", "value")
    End With
    
    rsht2 = ms.Range("A" & Rows.Count).End(xlUp).Row
    
    
    With mr
          rsht1 = .Range("A" & .Rows.Count).End(xlUp).Row
          For i = 2 To rsht1
                Do While .Cells(1, col).Value <> "" 'And .Cells(I, col).Value <> ""
                rsht2 = rsht2 + 1
               
    '            ms.Range("A" & rsht2).Value = .Range("A" & i).Value
                
                ms.Range("A" & rsht2).Value = .Cells(1, col).Value
                
                ms.Range("B" & rsht2).Value = .Cells(i, col).Value
         
                col = col + 1
            Loop
            col = 1
        Next
    End With
    
  With ms
  
  
  '  .Range("C2:C" & .Rows.Count).SpecialCells(4).EntireRow.Delete

  
    .Columns("A:Z").EntireColumn.AutoFit
    
    End With
    
End Sub

 

[frapi060]

Thank you Oeldere! I am gonna try. Is there any option to get it without a Macro?

 

[Oeldere]

Probably it could be, but I would use this macro to solve this problem..