+ Reply to Thread
Results 1 to 10 of 10

Filter results based on selected date

  1. #1
    Registered User
    Join Date
    07-19-2019
    Location
    Holland
    MS-Off Ver
    Office Proffesional Plus 2016
    Posts
    26

    Filter results based on selected date

    Hey Guys,

    I registered myself onto this forum because this is the last plast where i could think of with people who might could help me with my problem.

    I have an excel file with fitness exersices in it. The values could be different after each training. I need an cell where i could typ in the date and after that excel should show me the best results of that specif day. My Excel sheet is looking in the whole table except of only looking at the values corrosponding to the date i entered.

    It is possible that someone lift (fitness) 100kg todays for 12 repetitions in 3 sets. I need to see that info. The next day someone could maybe only lift 99.3kg for 11 repetions in 3 sets. So i do not need the max value overall. But the max value of that specific day.

    The data is comming from a .csv file and will be updated everyday, so the list will be very long over time with dates.

    I tried a lot with arrays, if match, lookups and everything into each other but i cannot get it done. What is the most simple solution some of you could give me?

    I hope my angel is onto this forum! :D
    (When my file is approved i will add it to this thread)
    Attached Files Attached Files
    Last edited by TimBell; 07-20-2019 at 04:48 AM. Reason: Added attachement

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,761

    Re: Filter results based on selected date

    Welcome to the forum

    This sounds do-able, but is is almost impossible to offer a suggestion without seeing your data, your references and how you expect the outcome to look like.

    Also, if this data is coming from a CVS file, your dates are probably not really dates, but rather text looking like dates - we will need to see, so we know if we need to convert them

    To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    07-19-2019
    Location
    Holland
    MS-Off Ver
    Office Proffesional Plus 2016
    Posts
    26

    Re: Filter results based on selected date

    Hello FBibbins,

    I added the file in the first post attachement. I was thought something has to be done with dynamic arrays because the range could be diffrent day to day. But maybe i am thinking way to difficult?
    Last edited by AliGW; 07-20-2019 at 02:46 AM. Reason: Please don't quote unnecessarily!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,647

    Re: Filter results based on selected date

    Do the following:

    1. Change the formula in Sheet 2 I2 to this: =VALUE(DAY(A2) & "/" & MONTH(A2) & "/" & YEAR(A2))

    2. Use this extended version of your MAX formula already on Sheet 2 in B6 of Sheet 1: =MAX((Sheet2!$C$2:$C$69=$A6)*(Sheet2!$I$2:$I$69=$B$2)*Sheet2!$E$2:$E$69)
    Attached Files Attached Files
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Registered User
    Join Date
    07-19-2019
    Location
    Holland
    MS-Off Ver
    Office Proffesional Plus 2016
    Posts
    26

    Re: Filter results based on selected date

    Hello Ali!

    I think this is exactly what i was trying to get done :D My day start good, think of all the hours i spend on this trying yesterday. But i believe and i am happy that you master Excel and i am not, cause i think it will be a shame if i could not get it done by myself in that case:P

    But can i use the same forumula to count the amount of sets done that they? Is my formula good to combine it to the "date" filter you created? I am getting a bit confused when i have to combine statements.

    I am also currious about the fact that i changed the range where it should look to the number of: 999. Because the table on Sheet2 will grow fast, Is there a way to make the range dynamic onto the table? Or is a range of 69 or 999, or 999999 no problem and will it always show me the right results depending on the date we entered?
    Attached Files Attached Files
    Last edited by AliGW; 07-20-2019 at 04:31 AM. Reason: Please don't quote unnecessarily!

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,647

    Re: Filter results based on selected date

    Just use the table's own field references:

    =MAX((heavyset_export[Exercise Name]=$A6)*(heavyset_export[Column1]=$B$2)*heavyset_export[Weight (kg)])

    Now you can add as many rows as you like to the table and the formula will see tham all.

    For reps:

    =MAX((heavyset_export[Exercise Name]=$A6)*(heavyset_export[Column1]=$B$2)*heavyset_export[Reps])

  7. #7
    Registered User
    Join Date
    07-19-2019
    Location
    Holland
    MS-Off Ver
    Office Proffesional Plus 2016
    Posts
    26

    Re: Filter results based on selected date

    That is really cool Ali!

    But could D6 of Sheet1 check how many times the name of that specif exercise is apprearing on the date we filled in? Because that will show how many sets are done that day.



    (Sorry for the quotes)
    Last edited by TimBell; 07-20-2019 at 04:41 AM.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,647

    Re: Filter results based on selected date

    Yes, but that's a different calculation:

    =SUMPRODUCT((heavyset_export[Exercise Name]=$A6)*(heavyset_export[Column1]=$B$2))

    or:

    =COUNTIFS(heavyset_export[Exercise Name],$A6,heavyset_export[Column1],$B$2)


    Administrative Note:

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below.

  9. #9
    Registered User
    Join Date
    07-19-2019
    Location
    Holland
    MS-Off Ver
    Office Proffesional Plus 2016
    Posts
    26

    Re: Filter results based on selected date

    Thank you very much Ali, I could not tell you how much this is worth for me. This will same me tons of time!

    Problem SOLVED!

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,647

    Re: Filter results based on selected date

    We're here to help and always happy to save you time.

+ 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] Filter Data Based on What's Selected
    By vill in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-05-2019, 10:00 AM
  2. [SOLVED] Filter results based on cell value and print results
    By MPXJOHN in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 09-25-2018, 11:38 AM
  3. filter data based on text entered or selected
    By lee2121 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-01-2016, 05:43 AM
  4. [SOLVED] Adding a value to a column based on filter results
    By JohnnyBoyxxx in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-24-2014, 05:53 AM
  5. [SOLVED] Filter based on selected range
    By forfiett in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-25-2013, 05:38 PM
  6. Compare 2 worksheets based on a selected filter
    By oxmanonline in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-22-2013, 08:29 AM
  7. Auto-Extract column based on filter results
    By Munir Nizar in forum Excel General
    Replies: 0
    Last Post: 08-16-2011, 02:10 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