+ Reply to Thread
Results 1 to 8 of 8

Facing issues with data filtering || please advise

  1. #1
    Registered User
    Join Date
    09-14-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    3

    Facing issues with data filtering || please advise

    Hi all

    Currently I am having some challenges with an excel issue and would appreciate on any feedback on how to tackle the same.

    I have a list of tickers, security,maturity and other row elements. One ticker may have more than 1 security. I am supposed to create a 5yr bucket that contains 1 security from each ticker that is closet to the 5yr bucket i.e. the maturity of that security from a ticker should be the closest to the 5 year bucket (today+5yr) compared to any other security within the same ticker. I need to do it for a 10yr bucket too but the logic would be the same one I figure out to do it for 5yr.

    One constraint is that I cannot use VBA / R to perform that and will need to try to use excel formulas and functions. I have a master data sheet that contains thousands of rows of data as mentioned above and ideally I should have the filtered rows based on the requirement stated above for each 5yr and 10yr bucket in separate worksheets.

    I have enclosed a sample snapshot of the input and output data. Feedback will be appreciated.

    ThanksPage.jpg

  2. #2
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Facing issues with data filtering || please advise

    Hi,

    Welcome to the forum.

    Can you upload the example file in excel format instead of jpeg please?

  3. #3
    Registered User
    Join Date
    09-14-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    3

    Re: Facing issues with data filtering || please advise

    Hi cbatrody

    Thanks for the response. I am not being able to attach the file for some reason. I have pasted the values below.

    Ticker Security Maturity Description
    AB X1 7/15/2019 Desc1
    AB X2 5/16/2021 Desc2
    AB X3 11/6/2025 Desc3
    AB X4 3/25/2020 Desc4
    AC X5 8/19/2021 Desc5
    AC X6 7/20/2022 Desc6
    AC X7 2/8/2027 Desc7
    AC X8 12/14/2018 Desc8

    Thanks

  4. #4
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Facing issues with data filtering || please advise

    Hi,

    Try the following formula:

    For Output 5Y Bucket:

    In A18:
    Please Login or Register  to view this content.
    B18:
    Please Login or Register  to view this content.
    C18:
    Please Login or Register  to view this content.

    In D18:
    Please Login or Register  to view this content.

    All array entered (CTRL+SHIFT+ENTER) to confirm

    drag this down the cells.



    For Output 10Y Bucket:

    In A25:
    Please Login or Register  to view this content.
    B25:
    Please Login or Register  to view this content.
    C25:
    Please Login or Register  to view this content.
    D25:
    Please Login or Register  to view this content.
    All array entered as well (CTRL+SHIFT+ENTER) to confirm.

    drag this down the cells.

    See the attached file.
    Attached Files Attached Files
    Last edited by cbatrody; 09-15-2017 at 04:36 PM.

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Facing issues with data filtering || please advise

    With duplicate 'Tickers' removed in column F array enter these two formulas.

    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

    In G2 and filled down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    in H2 and filled down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.




    A
    B
    C
    D
    E
    F
    G
    H
    1
    Ticker
    Security
    Maturity
    Description
    Ticker
    Security 5
    Secutiry 10
    2
    AB
    X1
    7/15/2019
    Desc1
    AB
    X2
    X3
    3
    AB
    X2
    5/16/2021
    Desc2
    AC
    X6
    X7
    4
    AB
    X3
    11/6/2025
    Desc3
    5
    AB
    X4
    3/25/2020
    Desc4
    6
    AC
    X5
    8/19/2021
    Desc5
    7
    AC
    X6
    7/20/2022
    Desc6
    8
    AC
    X7
    2/8/2027
    Desc7
    9
    AC
    X8
    12/14/2018
    Desc8
    Dave

  6. #6
    Registered User
    Join Date
    09-14-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    3

    Re: Facing issues with data filtering || please advise

    Thanks both. I have the sheet working now.

    Thanks

  7. #7
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Facing issues with data filtering || please advise

    You are welcome

    If that takes care of your question, please mark this thread as Solved by selecting Thread Tools --> Mark thread as solved.

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Facing issues with data filtering || please advise

    CaptainSpark

    You are welcome. Thanks for the feedback.

+ 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. [SOLVED] Filtering Macro Issues
    By Chrisbrough88 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-26-2014, 06:06 AM
  2. Copy-Filtering Data (Adv. Filter issues)
    By TechCredit in forum Excel General
    Replies: 5
    Last Post: 09-14-2014, 11:28 PM
  3. Formula Issues - Filtering data to new sheet.
    By payne90 in forum Excel General
    Replies: 1
    Last Post: 03-17-2014, 05:48 AM
  4. Facing some issues with ListBox
    By kumapra in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-05-2014, 10:32 PM
  5. [SOLVED] advanced filtering issues
    By derrickh13 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-15-2013, 03:09 PM
  6. filtering issues - help!
    By jw01 in forum Excel General
    Replies: 1
    Last Post: 04-03-2012, 09:57 AM
  7. Intense Filtering Issues...
    By jsarsfi2 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-15-2010, 12:50 PM

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