Combine table with dates and values in a new way (special re-sorting)

[Stephan Ellmann]

Dear Excel-Experts,

can someone help me with my problem?
I have an Excel file of the following format:

The first column lists dates (German Format, DD.MM.YYYY), the second one lists values:

1.1.2007 5,35
2.1.2007 6,78
3.1.2007 7,56
... ..
... ..
31.2.2012 4,53


This is a list of many years, but only weekdays are listed, no saturdays or sundays.

Now I'd like to have an automatic output on a second Sheet, like this:


2007 2008 2009 .... ...
1.1. 5,35 5,0 ...
2.1. 6,78 4,3 ...
3.1. 7,56 2.5 ...
4.1. ....

If there is no value for a certain date (because it was a saturday or sunday) the cell should just remain empty.

It would be perfect if everything could be sorted with just one mouseclick (i.e. with a Macro), as I have some more datasets which change from time to time.
If I had an automated procedure I could just paste the new dataset in Column A, click on "Sort" again, and get the output for the new dataset.

Is it possible to program this?
And if yes, how?


I'd be extremely grateful for some help.
Everything I found in this and other forums could not really help me.


Thanks to everyone who is willing to help me!

Kindest regards,

Stephan


An example-Excel-file can be downloaded from my dropbox:
https://dl.dropbox.com/u/27009278/Beispiel-Datei.xlsx

[Excel Fox]

Try this


Sub Consolidator()

Dim varInput, varOutput As Variant
Dim lngMinYear As Long, lngMaxYear As Long, lngLoop As Long, lngRow As Long, lngCol As Long
With Application
varInput = .Transpose(Worksheets(1).Cells(1).CurrentRegion.Of fset(1).Columns(1).Resize(, 2).Cells.Value2)
lngMinYear = Year(.Min(.Index(varInput, 1)))
lngMaxYear = Year(.Max(.Index(varInput, 1)))
End With
ReDim varOutput(1 To (lngMaxYear - lngMinYear) + 2, 1 To 367)

For lngLoop = 2 To UBound(varOutput)
varOutput(lngLoop, 1) = lngMinYear + (lngLoop - 2)
Next lngLoop
For lngLoop = 2 To 367
varOutput(1, lngLoop) = Format(lngLoop + 365 * 4, "D.M.")
Next lngLoop
For lngLoop = LBound(varInput, 2) To UBound(varInput, 2)
If Not IsEmpty(varInput(1, lngLoop)) Then
If IsNumeric(varInput(1, lngLoop)) Then
With Application
lngRow = .Match(Year(varInput(1, lngLoop)), .Index(varOutput, , 1), 0)
lngCol = .Match(Format(varInput(1, lngLoop), "D.M."), .Index(varOutput, 1), 0)
varOutput(lngRow, lngCol) = varInput(2, lngLoop)
End With
End If
End If
Next lngLoop
With Worksheets(2)
.UsedRange.Clear
.Cells(1).Resize(UBound(varOutput, 1), UBound(varOutput, 2)).Value = varOutput
End With

End Sub

[Stephan Ellmann]

GREAT!!

This works perfectly!
I have been searching for a solution for such a long time and you were able to post an answer within one single hour :-D

Thank you so much!!!