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

**Stephan Ellmann** · 02-23-2013, 06:17 PM

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** · 02-23-2013, 08:28 PM

Try this

Code:

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.Offset(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** · 02-23-2013, 09:18 PM

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!!!