Results 1 to 3 of 3

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

  1. #1
    Junior Member
    Join Date
    Feb 2013
    Posts
    2
    Rep Power
    0

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

    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

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    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
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Feb 2013
    Posts
    2
    Rep Power
    0
    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!!!

Similar Threads

  1. Combine Columns From Multiple Sheets To One Sheet
    By Portucale in forum Excel Help
    Replies: 6
    Last Post: 04-24-2013, 09:18 PM
  2. VBA - Sorting by columns
    By Rasm in forum Excel Help
    Replies: 2
    Last Post: 09-24-2012, 04:18 PM
  3. How to Extracting dates and days between 2 dates.
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 9
    Last Post: 08-11-2012, 09:11 PM
  4. Data sorting & Graph making according filters
    By leopaulc in forum Excel Help
    Replies: 2
    Last Post: 04-05-2012, 08:56 AM
  5. Remove Special Characters :
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 3
    Last Post: 03-06-2012, 09:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •