Results 1 to 10 of 12

Thread: Two Column Dates to arrange in a single column

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Two Column Dates to arrange in a single column

    Sheet-1: I have dates in B5:B14 and some values in c5:c14 (and entries goes on for 1 year)
    Likewise,
    Sheet2: I have dates in E5:E14 and some values in F5:F14 (and entries goes on for 1 year)
    Sheet-3: I need a result table, where all dates should align in a single column H and its equivalent values in I column
    I tried, Offset, lookup, index etc, but i failed.
    Thus i seek your help.
    Sheet-Sample is attached
    Thank U
    Attached Files Attached Files

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,854
    Articles
    0
    Excel Version
    O365
    Code:
    Public Sub MergeData()
    Dim lastrow As Long
    
    
        With Worksheets("Sheet3")
        
            Worksheets("Sheet1").UsedRange.Copy .Range("A1")
            
            lastrow = .Range("A1").End(xlDown).Row
             Worksheets("Sheet2").UsedRange.Copy .Cells(lastrow + 1, "A")
             .Rows(lastrow + 1).Delete
             
            lastrow = .Range("A1").End(xlDown).Row
            .Range("A1:B1").Resize(lastrow).Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlYes
        End With
    End Sub

  3. #3
    Thank you for your help.
    But it doesnt work.
    Sheet-1: A:B
    Sheet-2: A:B
    Sheet-3: Copied your formula in VBA
    Could you please, send me as an example sheet
    Thank U
    (attached Sheet)
    Attached Files Attached Files

  4. #4
    Excel 2010 with free Power Query Add-In
    No code or formulas need to be written.
    http://www.mediafire.com/view/epu9q1.../12_05_14.xlsx

  5. #5

    Multiple Consolidation Pivot Table

    Did you try the Pivot Table Multiple Consolidation Range ... put both the date & value in the rows & you will get them as you want.
    HTH.
    Row Value
    1/1/2014 1
    2
    3
    100
    200
    1/1/2014 Total
    1/2/2014 4
    5
    300
    1/2/2014 Total
    1/3/2014 400
    500
    600
    1/3/2014 Total
    1/4/2014 6
    7
    1/4/2014 Total
    1/5/2014 8
    9
    1/5/2014 Total
    1/6/2014 10
    1/6/2014 Total
    4/6/2014 700
    800
    900
    1000
    4/6/2014 Total
    Grand Total
    Attached Files Attached Files

  6. #6
    Hello Lochan79
    Thank U for your effort, but i need automatic whenever i update those entries in sheet1 and 2
    Any otherway?

  7. #7
    Hello HERBDS7
    wow your pic shoes good exact result,
    but i did not see any TABLE QUERY in EXCEL

  8. #8
    Hello Bob Philips
    This doesnt work
    could you plz send me an attachment
    Thank u

Posting Permissions

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