+ Reply to Thread
Results 1 to 50 of 50

Dynamic Filter Search to pull similar data from the specific value searched

  1. #1
    Registered User
    Join Date
    06-18-2019
    Location
    Dallas, Texas
    MS-Off Ver
    windows 2013
    Posts
    72

    Dynamic Filter Search to pull similar data from the specific value searched

    Hey all,

    I am running into this blockade and unsure where to go from here but have few ideas.

    So basically I have a huge list of Data and in theory I would like to search an ID Number from the data and pull all the data similar to the searched # based off of certain criteria all together.

    More specifically, Is there a way to use my search in the "search" tab to search an ID# and pull all other ID numbers based off of the criteria of matching Maturity years and ratings.
    Ex: Pulling the # in B2 which has the relationship of a 2023 maturity, Baa2 and BBB+ ratings and having a dynamic list display the others matching the same criteria of 2023, Baa2 and BBB+

    I was thinking about using a form of Index Match, but have never used multiple criteria like this. My file should be attached
    Any help would be awesome and really appreciated
    Thank you
    Attached Files Attached Files
    Last edited by Tgbell; 07-17-2020 at 09:39 AM.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    Try the attached.
    Attached Files Attached Files
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    06-18-2019
    Location
    Dallas, Texas
    MS-Off Ver
    windows 2013
    Posts
    72

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    That file is really useful! and awesome, I really appreciate the help - Is there any way to be able to search by just the id number, and pull the relating data as well?

    My goal is to essentially be able to plug in a bonds cusip id # and pull all related bonds from my data to show the User and information based off of those critieria matching to the initial cusip search. The end goal being to organize historical trade data and see bonds related to a specific trades, almost like a trend.

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    You can use conditionals as deep as you want, to search given combinations of entries. This version will either search only for id# or for all four. If you want to do other combos, then the helper formula just needs to be expanded for the specifics.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-18-2019
    Location
    Dallas, Texas
    MS-Off Ver
    windows 2013
    Posts
    72

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    Thank you sir, you are such a help. So there would be a way to reverse search by only one or multiple of the other criteria? Just being able to enter one of the values in row 2, or multiple as well?

    Sorry, this is area of excel is not my strong suit

  6. #6
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    This should work with any combination of search values...
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    06-18-2019
    Location
    Dallas, Texas
    MS-Off Ver
    windows 2013
    Posts
    72

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    Thank you so much for the help Bernie, this really helps alot and solved some problems for me!

  8. #8
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    Great - another option, of course, is to simply apply data filters to your data set....

  9. #9
    Registered User
    Join Date
    06-18-2019
    Location
    Dallas, Texas
    MS-Off Ver
    windows 2013
    Posts
    72

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    Sorry for bothering you but I had a quick follow up, I am trying to copy this to my actual data workbook, what would be the best option in order to not mess up the array formula and others etc.. Should I just rename the values to the matching ones, ex: instead of DataTable put the full data range's name?

    And could you possibly explain why you used the first part of the formula, because I was unsure why you called the S&P rating twice?
    per ref: =IFERROR(SMALL(IFERROR(1/(1/ROW(DataTable[[#All],[S&P Rating]])

  10. #10
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    Hi - I would use the working workbook that I posted and copy the data over the existing Data Table values - you can always rename the table using the table tools, and you can rename any of the data fields at that time as well, and Excel should update the formula properly.

    Note that the formula expects the table headers to be in row 1 - I call S&P Ratings twice, once to compare to the desired value and once to pick up the row - I could have used any of the data fields to get the row for indexing purposes: I just randomly chose S&P as that field.

  11. #11
    Registered User
    Join Date
    06-18-2019
    Location
    Dallas, Texas
    MS-Off Ver
    windows 2013
    Posts
    72

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    WS attached

    Thank you Bernie for all the help! So I was able to think through this and type it out with more explanation and a different goal.

    Main project for reference :
    I have a dataset of thousands of bond orders through our desk. I am trying to make a interactive search to pull all bonds related to certain attributes from the specific order. For example: insert a cusip or user and have it pull bonds they might buy based off of past trades and types of bonds they purchased (through the criteria system)
    The Project end goal would be: Help me put in an order # (Cusip) or User and have it show me what they would most likely like/buy (based off of the historical data)

    Lookup:
    But as of right now, I think I need a type of Index(match function to properly pull all values related only if ALL of these conditions are met (the same as the ones we are looking for): Composite Rating, Maturity Year, Industry, and Sector

    Attached is a ws with an example from the data with more description, any help would be awesome - thanks
    Attached Files Attached Files

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    The following formula produces the same output as listed in the Results from formula section:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that column U will need to be custom formatted the same as the Maturity Year column (yyyy).
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  13. #13
    Registered User
    Join Date
    06-18-2019
    Location
    Dallas, Texas
    MS-Off Ver
    windows 2013
    Posts
    72

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    This is exactly what I needed! It all works besides the formatting issue. On my actual dataset the Maturity Year column is all text and just has the years, so it should fix that issue once updated! Would copying over the data on top of your worksheet be the best way to do that? Or would you recommend another way?

    Thanks so much for the help! This sets me up to do the next step in this project, that I mentioned in a previous reply

  14. #14
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    I believe that either copying the real data into columns A:K or copying the formulas in N4:X4 into the spreadsheet with your real data should work. Of course if you choose the latter you will need to select cells N4:X4 and drag the fill handle down.
    Let us know if you have any questions.

  15. #15
    Registered User
    Join Date
    06-18-2019
    Location
    Dallas, Texas
    MS-Off Ver
    windows 2013
    Posts
    72

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    It wont work due to the formatting issue with the maturity year. The formula should be good to go, but still not referencing with the yyyy format. I have some text values in the same column, in which might be messing it up. I am just playing around with different ideas, thanks for the help

  16. #16
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    Perhaps this will help.
    1. Use Text to Columns to convert the values in the Maturity column from text to dates (MDY)
    2. Use the following to populate the Maturity Year column (format General)
    3. repeat the above steps for cells T2:U2
    4. Modify the formula in N4 so that it reads:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    06-18-2019
    Location
    Dallas, Texas
    MS-Off Ver
    windows 2013
    Posts
    72

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    That worked! Thank you so much

  18. #18
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

  19. #19
    Registered User
    Join Date
    06-18-2019
    Location
    Dallas, Texas
    MS-Off Ver
    windows 2013
    Posts
    72

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    Of course! Quick follow up question: Say I wanted to choose multiple values, say Years or ratings, would that be possible by using a textjoin type function? Or would the creation of multi values with delimiters mess up the formula? EX: I want to look up 2020 and 2023, So I have an insert from a separate table for multiple dates which the text join formula (inside the year selection cell) pulls the multiple dates from.

  20. #20
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    To choose multiple years you could use the + operator i.e. .../((Table4[[Maturity Year]:[Maturity Year]]=$U$2)+(Table4[[Maturity Year]:[Maturity Year]]=$U$3))/...
    Where U2 would contain one year and U3 would contain another.
    I can't visualize the statement "I have an insert from a separate table for multiple dates which the text join formula (inside the year selection cell) pulls the multiple dates from".
    Please upload a file to illustrate.
    Let us know if you have any questions.

  21. #21
    Registered User
    Join Date
    06-18-2019
    Location
    Dallas, Texas
    MS-Off Ver
    windows 2013
    Posts
    72

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    Thanks for the reply! I tried the + operator in another workbook and the idea worked, However i mean more like selection of an upper and lower value. Here i explained a little better on this sample file.
    Attached Files Attached Files

  22. #22
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    To select for an upper and lower value of years try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that with 2023 in cell V2, V2+8 is 2031.
    You may want to set the median year in cell V2, i.e. 2027 and the range in another cell i.e. cell AB2 then the formula could read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The same principal could apply to a prince range.
    Let us know if you have any questions.

  23. #23
    Registered User
    Join Date
    06-18-2019
    Location
    Dallas, Texas
    MS-Off Ver
    windows 2013
    Posts
    72

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    That works wonderfully! I changed the function to pull from certain cells instead of choosing +8. AD2 is my upper and AD3 is my lower - this works awesdome and I added them both! Thanks for the help!
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Would there be a way to do this for ratings by giving each rating a numerical value, so pull all related to BBB +5 or between values like above? So basically the same thing as above just with Composite. I have a list of the ordering and it goes like this (from beginning): AAA, AA+, AA, AA-, A+, A, A-, BBB+, BBB, BBB-, BB+, BB, BB-, B+, B, B-, CCC+, CCC, CCC-, CC, C, DDD.
    Last edited by Tgbell; 07-21-2020 at 03:26 PM.

  24. #24
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    I feel as if you could.
    I would envision a two column lookup table where AAA corresponds a value of 1, AA+ corresponds to a value of 2...DDD corresponds to a value of 22.
    Next add a column to table 4 (referring back to the attachment in post #1) and populate that column with either VLOOKUP or INDEX/MATCH to get the composite numerical values.
    Lastly add upper and lower limits referencing the composite numerical values to the formula in post #23.
    If you need specific help please upload another .xlsx file so that we can use your present set up as a guide with which to make suggestions.
    Let us know if you have any questions.

  25. #25
    Registered User
    Join Date
    06-18-2019
    Location
    Dallas, Texas
    MS-Off Ver
    windows 2013
    Posts
    72

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    I just tried the index match but running into a few problems.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    This is the formula I used in L2 on the data tab. This is a former version i uploaded with this added, so it wont include some of the other recent additions above.

    I think I might add in the search area for it to pull from +-3 ratings or so, so that it does it automatically instead of an upper or lower and so it is more code and less reference.
    Attached Files Attached Files
    Last edited by Tgbell; 07-22-2020 at 10:43 AM.

  26. #26
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    The formula should read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.

  27. #27
    Registered User
    Join Date
    06-18-2019
    Location
    Dallas, Texas
    MS-Off Ver
    windows 2013
    Posts
    72

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    When calling it to search by those values, are they now assigned and I could do AAA -1 or something similar? Or will this require another lookup

    Thanks! Yall Rock

  28. #28
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    My suggestion would be to put the range in cell Z2 and then populate AA2:AB2 with formulas i.e.: =INDEX($AB5:$AB28,MATCH($V2,$AA5:$AA28,0))-$Z2 and =INDEX($AB5:$AB28,MATCH($V2,$AA5:$AA28,0))+$Z2
    The formula for the range highlighted in yellow could then be written:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files

  29. #29
    Registered User
    Join Date
    06-18-2019
    Location
    Dallas, Texas
    MS-Off Ver
    windows 2013
    Posts
    72

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    Looks like it is working! If i have any questions I will let you all know. Have a great weekend
    Last edited by Tgbell; 07-24-2020 at 04:31 PM. Reason: Figured error out

  30. #30
    Registered User
    Join Date
    06-18-2019
    Location
    Dallas, Texas
    MS-Off Ver
    windows 2013
    Posts
    72

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    Im running into this problem with the the population of results after I used the range for Composite. Could you take a look for me please.
    Attached Files Attached Files

  31. #31
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    Made the following modifications:
    Cells AH7:AI7 =IFERROR(INDEX(Table5[Value], MATCH($W$2,Table5[Comp], 0))-$AG7, 1)
    Cells in Table4:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files

  32. #32
    Registered User
    Join Date
    06-18-2019
    Location
    Dallas, Texas
    MS-Off Ver
    windows 2013
    Posts
    72

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    When using this with all my data, no results are displayed. The data is in the same exact format, except for the filters were on a different sheet. I tried using the data on top of the book you sent and it also failed to produce any results for some reason

    For example, when the criteria is different, change it to AA+, 2020, and Technology with a price at 97; this should pull at least row 17-20 AAPLs from the data but results with nothing
    Last edited by Tgbell; 07-27-2020 at 01:07 PM.

  33. #33
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    I get 20 rows of results from the criteria listed in post #32.
    I'll be glad to look at the spreadsheet and see what is wrong, if you attach it to a new post (so that I get a notification).

  34. #34
    Registered User
    Join Date
    06-18-2019
    Location
    Dallas, Texas
    MS-Off Ver
    windows 2013
    Posts
    72

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    Just saw this! I ended up playing around with my references and table, and was able to fix my problem. I have been running a few tests ant it looks like it is working smoothly now. Thank you so much for the help, if I have any following questions - I will let you know. I really appreciate the time and availability you've given me.

  35. #35
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

  36. #36
    Registered User
    Join Date
    06-18-2019
    Location
    Dallas, Texas
    MS-Off Ver
    windows 2013
    Posts
    72

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    I just have a quick follow up question, is there a way to manipulate those formulas to only show certain criteria I want - The filter search currently looks for price, date, rating, sector, and industry. This is everything I need it to do, but I sometimes need to search with all those (price, date, rating, sector, and industry) but without one of them. Aka have a toggle because sometimes one of the filters isn't necessary. EX: So this next search I want to search but just have it show all dates or all industry's etc but keep the other 4 the same.

  37. #37
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,403

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    Forgive my stupidity, but is there any reason why you can't use the original table with slicers?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  38. #38
    Registered User
    Join Date
    06-18-2019
    Location
    Dallas, Texas
    MS-Off Ver
    windows 2013
    Posts
    72

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    Yes, I could do that. However this program is meant to bypass the manual selection and filtering, to simplify using a dashboard and search matching a live lookup with historical records

  39. #39
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,403

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    Program?

  40. #40
    Registered User
    Join Date
    06-18-2019
    Location
    Dallas, Texas
    MS-Off Ver
    windows 2013
    Posts
    72

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    *Workbook
    my apologies

  41. #41
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,403

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    Ah!

    I just think that it's getting overly complex as it stands. I have fallen into this trap myself in the past, and, as I am now approaching retirement and moving towards handing things over to my successor, I am much more mindful of what that will entail. You need to think to yourself how easy this is going to be for someone who has had nothing to do with creating to maintain it if you go under a bus. Just sayin' ...
    Last edited by AliGW; 07-30-2020 at 11:14 AM.

  42. #42
    Registered User
    Join Date
    06-18-2019
    Location
    Dallas, Texas
    MS-Off Ver
    windows 2013
    Posts
    72

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    Thank you for the wise words. Yeah I am in agreement, there is nothing worse than when something happens and you're left with a book that you have no idea how to use.

    It sounds really complex, but I am just awful at explaining - I was trying to have this workbook just bypass all the manual searching and have it give me only want I need in a click of a button almost. It is for my team so I was trying to make as user friendly as possible.

  43. #43
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,403

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    The concept is NOT complex, and we understand what you are trying to achieve, however the desire to make this an all-singing-all-dancing dashboard is making your all-encompassing formulae long, clumsy and very hard to maintain.

    What you are trying to do is turn a workbook into a database.

  44. #44
    Registered User
    Join Date
    06-18-2019
    Location
    Dallas, Texas
    MS-Off Ver
    windows 2013
    Posts
    72

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    Yeah it is definitely pushing itself as a database type. I understand this type of work could work with sql Yeah I was worried about that - So theres no way to be able to run it as is but then also be able to just delete the industry criteria (or whichever one when needed), without making the formula too clumsy etc? I could always just delete the portion of it inside the formula, which should work too. Since I needed to reference using some bloomberg api and match it with historical data automatically, I choose to just do it as spreadsheet and not a database type project

  45. #45
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,403

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    It can be done, of course, but again, that's not the point I'm trying to make.

    With each criterion you now want to add an either/or scenario, so that's effectively doubling the size of an already convoluted formula.

    Let me put it another way: when I fell into this trap, it was with a workbook that had to be updated about once every six months. Inevitably, I was asked to introduce more fine tuning later, and the problem was that even I had to re-learn the formula each time I had to amend it. What I am really trying to say is that the way you have chosen to do this is probably not the best, and you are most likely going to regret it at some point.

    Anyway - I've given you enough food for thought now - it's up to you if you decide to pursue it this way and run the risk of tearing your hair out and wishing you hadn't at some point in the future. As I said, been there, got the T-shirt.

  46. #46
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    See if something along these lines, as modeled in the file, would be intuitive, or at least require minimal instruction.
    If the maturity year is left blank then the date filter range will automatically adjust to include all years listed in column G using: =IF(ISNUMBER(V$2),V$2-3,MIN(Table44[Maturity Year])) and =IF(ISNUMBER(V$2),V$2-3,MAX(Table44[Maturity Year])) in AD2 and AD3 respectively.
    Let us know if you have any questions.
    Attached Files Attached Files

  47. #47
    Registered User
    Join Date
    06-18-2019
    Location
    Dallas, Texas
    MS-Off Ver
    windows 2013
    Posts
    72

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    Ah I see; Yeah i definitely want to stay away from that and really appreciate your warning and explanation. Thats exaclty what I would be doing come 6 months from now and can easily see that this way would cause pain and suffering.

    Thanks for the help AliGW you likely saved me alot of time in the future

  48. #48
    Registered User
    Join Date
    06-18-2019
    Location
    Dallas, Texas
    MS-Off Ver
    windows 2013
    Posts
    72

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    That works perfect actually, it utilizes the range selectors by passing formula change and running into errors that way. The same works for for Price as well! Since Industry is not by range and rating is weird because of its index match, those two will have still be have to be just done manually im assuming, which is fine. Thank you for this quick fix for those! You're awesome

  49. #49
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    Here is my rough draft type proposal for doing what you want without increasing the size of my "already convoluted formula" (LOL), in fact it reduces the size.
    The factors that are selected in ranges are left in the formula and those that are not are assigned to slicers.
    Blue cells indicate the need for user input, whereas green cells display calculated output.
    For example the user will need to input the maturity year along with the range into cells AF3:AG3, and then AH3:AI3 will display the range of years.
    The formula now reads:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files

  50. #50
    Registered User
    Join Date
    06-18-2019
    Location
    Dallas, Texas
    MS-Off Ver
    windows 2013
    Posts
    72

    Re: Dynamic Filter Search to pull similar data from the specific value searched

    That is awesome, unfortunately since I have so much data the industry slicer method will cause me to have to copy the formula down really far and cause a huge time concern. Currently not showing all the rows.

    I did the same on my file with composite and for Industry I had a multiple selection using + operator but if I delete the inputted industry it would display with nothing. However I changed it to assigned values like composite so that I can delete the cell references and it will show all. But now I am trying to connect the two in order to be able to select the industry and 3 additional from text boxes if needed. My text box is K5:K8
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Ind helper is the same as comp helper. Just wondering if this would be possible. Adding one other works (k5) but k6,7 wont work when I add to that formula

+ 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] Formula to pull specific value from multiple similar records
    By trisoldee in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-26-2018, 03:28 PM
  2. Replies: 3
    Last Post: 05-24-2016, 11:49 AM
  3. [SOLVED] Any Faster Way to Copy/Paste this range? (I have searched and searched and searched)
    By liquidmettle in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 12-12-2015, 02:33 PM
  4. Search and see only the searched data
    By dirty moose in forum Excel General
    Replies: 32
    Last Post: 03-26-2015, 11:06 PM
  5. Replies: 1
    Last Post: 08-12-2014, 05:54 PM
  6. Summary sheet that pulls specific data when searched for
    By Jake7208 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-24-2013, 01:59 AM
  7. Replies: 4
    Last Post: 10-22-2012, 03:21 AM

Tags for this Thread

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