+ Reply to Thread
Results 1 to 5 of 5

Removing unmatched data from two time series data sets!

  1. #1
    Registered User
    Join Date
    07-25-2013
    Location
    Dhaka, Bangladesh
    MS-Off Ver
    Excel 2007
    Posts
    9

    Removing unmatched data from two time series data sets!

    I have two time series data with dates. For an academic purpose (beta calculation) I need same matching dates for both time series data sets in the same row side by side. Please note that the dates are recorded as dd/mm/yyyy.

    I have copied a portion of two data series in the following. As you can see, I have 1st, 2nd, 5th, 6th of January data for ABBANK but I don't have data for those dates under the Index data, so I need to have these ones removed from ABBANK Data Series. Then again, I have 7th January data for Index data series but data for that date is missing in ABBANK data, so I need that one removed from Index data series.

    Index Data Series ABBANK Data Series

    Ticker Date Close Ticker Date Close
    00DSEGEN 03/01/2007 1583.09 ABBANK 01/01/2007 90.07
    00DSEGEN 04/01/2007 1589.41 ABBANK 02/01/2007 88.3
    00DSEGEN 07/01/2007 1574.97 ABBANK 03/01/2007 87.75
    00DSEGEN 08/01/2007 1579.21 ABBANK 04/01/2007 87.15
    00DSEGEN 09/01/2007 1588.08 ABBANK 05/01/2007 89.55
    00DSEGEN 10/01/2007 1587.61 ABBANK 06/01/2007 89.9
    00DSEGEN 11/01/2007 1581.99 ABBANK 08/01/2007 89.03
    00DSEGEN 14/01/2007 1656.93 ABBANK 09/01/2007 91.97

    As such, following is the desired modification in both data sets.

    Index Data Series ABBANK Data Series

    Ticker Date Close Ticker Date Close
    00DSEGEN 03/01/2007 1583.09 ABBANK 03/01/2007 87.75
    00DSEGEN 04/01/2007 1589.41 ABBANK 04/01/2007 87.15
    00DSEGEN 08/01/2007 1579.21 ABBANK 08/01/2007 89.03
    00DSEGEN 09/01/2007 1588.08 ABBANK 09/01/2007 91.97
    00DSEGEN 10/01/2007 1587.61 ABBANK 10/01/2007 87.93
    00DSEGEN 11/01/2007 1581.99 ABBANK 11/01/2007 91.1
    00DSEGEN 14/01/2007 1656.93 ABBANK 14/01/2007 89.6


    Can anyone please show me how to do it automatically using excel?

    Thanks a lot in advance.

    Shahzada Imran

    P.S.: I searched for a similar problem with solution in this forum but could not find one. Please refer to such posts if exist.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Removing unmatched data from two time series data sets!

    is this what your after?
    Attached Files Attached Files
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    07-25-2013
    Location
    Dhaka, Bangladesh
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Removing unmatched data from two time series data sets!

    Thanks a lot. I can live with that. Only thing I would still want is a mechanism to remove the rows with "none" in them. May be I will be able to use filter to do that.

  4. #4
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Removing unmatched data from two time series data sets!

    you could be to get into vba to do it automatically but really for what your doing its probably not worth it

    easiest way is like your pointed out...filter and remove

    be careful how you do this though because you have the data set in the same area you may inadvertently remove data which it reads off
    best move ABBANK into another sheet imo

  5. #5
    Registered User
    Join Date
    07-25-2013
    Location
    Dhaka, Bangladesh
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Removing unmatched data from two time series data sets!

    Quote Originally Posted by humdingaling View Post
    be careful how you do this though because you have the data set in the same area you may inadvertently remove data which it reads off
    best move ABBANK into another sheet imo
    I know what you mean. I did copy the data and pasted "values" into another worksheet before removing those rows using filter/sorting.

    Thanks again. Really appreciate your concern.

    Shahzada Imran

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 11-27-2012, 04:42 PM
  2. line graph with multiple data series vs. time/date leaves out most data
    By Lee Collins in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-18-2012, 10:54 AM
  3. Replies: 1
    Last Post: 08-02-2012, 03:58 AM
  4. Two Sets of Time Data - One Chart
    By lmeg in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 08-07-2006, 07:10 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1