+ Reply to Thread
Results 1 to 9 of 9

auto populate field for whole year based on 2 criteria's of T/F & name - impossible?

  1. #1
    Registered User
    Join Date
    06-04-2021
    Location
    england
    MS-Off Ver
    2013
    Posts
    4

    auto populate field for whole year based on 2 criteria's of T/F & name - impossible?

    Data is as follows:
    Name date changed old value new value
    001 08/01/2021 T F
    001 22/02/2021 F T
    001 04/04/2021 T F
    001 30/05/2021 T F
    002 07/01/2021 F T
    002 14/04/2021 T F
    002 26/05/2021 T F
    003 17/03/2021 F T
    003 27/04/2021 T F


    What I am trying to do if autofill the data for the whole year and populate by day T or F up until each time it changed. The date it changes, it starts populating the new value.
    i.e. for 001 - the value will be
    01/01/2021 - T
    02/01/2021 - T
    03/01/2021 - T
    04/01/2021 - T
    05/01/2021 - T
    06/01/2021 - T
    07/01/2021 - F
    08/01/2021 - F
    this continues for the rest of the year
    Same is then for 002 & 003. To be clear it needs to recognise the name field and also when the change happened and continue populating. This seems impossible
    As you can imagine this is so manual and if I have 500 names and have to do for 365 days a T or F depending on the input data, it will take ages. I was thinking index match however there are two issues, it only recognises the first set of data and doesn't recognise by name. Any thoughts... would appreciate someone giving a detailed solution. thanks

  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
    51,256

    Re: auto populate field for whole year based on 2 criteria's of T/F & name - impossible?

    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
    06-04-2021
    Location
    england
    MS-Off Ver
    2013
    Posts
    4

    Re: auto populate field for whole year based on 2 criteria's of T/F & name - impossible?

    Thanks for the info, I have now attached a spreadsheet as the example file... there are 2 sheets, 1 is the input of raw data, 2 is output.
    I really am stuck and cannot think of a solution for this
    Attached Files Attached Files
    Last edited by maxmadgamer47; 06-05-2021 at 06:24 AM.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,905

    Re: auto populate field for whole year based on 2 criteria's of T/F & name - impossible?

    Try

    in C2

    =IFERROR(IF(INDEX(input!$B$2:$B$14,MATCH(C$1,input!$A$2:$A$14,0))>$A2,INDEX(input!$C$2:$C$14,MATCH(C$1,input!$A$2:$A$14,0)),IFERROR(INDEX(input!$D$2:$D$14,MATCH(C$1&$A2,input!$A$2:$A$14&input!$B$2:$B$14,1)),"")),"")

    Enter with Ctrl+Shift+Enter (Array formula)

    Copy across and down

  5. #5
    Registered User
    Join Date
    06-04-2021
    Location
    england
    MS-Off Ver
    2013
    Posts
    4

    Re: auto populate field for whole year based on 2 criteria's of T/F & name - impossible?

    Thanks JohnTopley, the problem with this is that if you look at the first data of T001, the date changes on 07/01/2021 so F should be then rather than 08/01/2021.
    Same as T001 22/01/2021, it should start populating T then rather than 23/01/2021?
    Thanks

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,905

    Re: auto populate field for whole year based on 2 criteria's of T/F & name - impossible?

    In my file it changes on the 7 jan and 22nd of Jan .
    Attached Files Attached Files

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    50,987

    Re: auto populate field for whole year based on 2 criteria's of T/F & name - impossible?

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. As you are new here, I will provide the link for you today: https://www.mrexcel.com/board/thread...sible.1172929/.)
    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.

  8. #8
    Registered User
    Join Date
    06-04-2021
    Location
    england
    MS-Off Ver
    2013
    Posts
    4

    Re: auto populate field for whole year based on 2 criteria's of T/F & name - impossible?

    Thanks, John, that was very useful to double-check against.
    Although I am getting an error when I am adding more names to the spreadsheet.
    I have 1000 names and this will be updated throughout the year. This report is dynamic and taken from another system
    hence each time the report is downloaded, it may have more names against it.
    Attached the updated file. Could we use tables so that the data is not limited?
    I've tried to change the formula to look up the table however the T & F are registering against the wrong dates now?
    would appreciate it if you could update the formula against this uploaded file please.
    Ali - apologies for that, thanks for the info, will do going forward.
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,905

    Re: auto populate field for whole year based on 2 criteria's of T/F & name - impossible?

    The issue is that your most of your dates in Input are Dates AND Times so you do not get the expected matches. See first TOP003.

    I have corrected the DATES (removed TIME).
    Attached Files Attached Files
    Last edited by JohnTopley; 06-05-2021 at 03:52 PM.

+ 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. Auto incrementing an Access Table field based upon the start of a year
    By KenGooch in forum Access Tables & Databases
    Replies: 0
    Last Post: 12-30-2019, 05:05 PM
  2. [SOLVED] Auto Populate a field based on two criteria
    By marks9172 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-10-2017, 04:15 PM
  3. Auto Populate based on 2 criteria using Array
    By lexusap in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-23-2015, 02:56 AM
  4. Replies: 5
    Last Post: 08-04-2014, 06:42 PM
  5. Replies: 2
    Last Post: 07-31-2013, 02:00 PM
  6. Formula to auto populate field based on or statement
    By suromi in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 01-22-2013, 05:37 PM
  7. Auto-populate field based on drop down box selection
    By nicfarrell in forum Excel General
    Replies: 7
    Last Post: 01-21-2013, 12:47 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