Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Two Column Dates to arrange in a single column

  1. #1

    Two Column Dates to arrange in a single column

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Sheet-1: I have dates in B5:B14 and some values in c5:c14 (and entries goes on for 1 year)
    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
    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.

  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.
    Row Value
    1/1/2014 1
    1/1/2014 Total
    1/2/2014 4
    1/2/2014 Total
    1/3/2014 400
    1/3/2014 Total
    1/4/2014 6
    1/4/2014 Total
    1/5/2014 8
    1/5/2014 Total
    1/6/2014 10
    1/6/2014 Total
    4/6/2014 700
    4/6/2014 Total
    Grand Total
    Attached Files Attached Files

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

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

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

  9. #9
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Mississauga, Canada
    Excel Version
    Excel 2016
    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.
    Attached Files Attached Files

  10. #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 )

Page 1 of 2 1 2 LastLast

Posting Permissions

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