Two Column Dates to arrange in a single column

ameen6

New member
Joined
Dec 3, 2014
Messages
13
Reaction score
0
Points
0
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
 

Attachments

  • two column dates.xlsx
    10.8 KB · Views: 16
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
 
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)
 

Attachments

  • two column dates2.xls
    39.5 KB · Views: 6
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.
RowValue
1/1/20141
2
3
100
200
1/1/2014 Total
1/2/20144
5
300
1/2/2014 Total
1/3/2014400
500
600
1/3/2014 Total
1/4/20146
7
1/4/2014 Total
1/5/20148
9
1/5/2014 Total
1/6/201410
1/6/2014 Total
4/6/2014700
800
900
1000
4/6/2014 Total
Grand Total
 

Attachments

  • two column dates.xlsx
    16.4 KB · Views: 11
Hello Bob Philips
This doesnt work
could you plz send me an attachment
Thank u
 
Hello HERBDS7
wow your pic shoes good exact result,
but i did not see any TABLE QUERY in EXCEL :(
 
Hello Lochan79
Thank U for your effort, but i need automatic whenever i update those entries in sheet1 and 2
Any otherway?
 
I have attached a sample workbook using Bob's macro (he might be away).

You will need to enable macros. Then click the Merge button in Sheet3.
 

Attachments

  • Sample.xlsm
    24.8 KB · Views: 10
Wow... it works great...
Thank u NBVC.
But there is a problem as follows:
If i click "merge" button more than once, the results repeated. Can you please rectify the same
Also request you to provide a macro for "Clear" to clear the resut
Thank u again
(Also i thank Bob Philips :) )
 
See attached.

I replaced the code with this (it essentially clears and overwrites the data each time you press Merge button).

Code:
Public Sub MergeData()
Dim lastrow As Long

Application.ScreenUpdating = False

    With Worksheets("Sheet3")
        .Range("A:B").Clear
    
        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
    
    Application.ScreenUpdating = True

End Sub

and added this code to clear (click the Clear button).

Code:
Public Sub ClearData()

    With Worksheets("Sheet3")
        .Range("A:B").Clear
    End With
End Sub
 

Attachments

  • Sample.xlsm
    26 KB · Views: 10
Back
Top