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

maxmadgamer47

New Member
Joined
Jun 4, 2021
Messages
1
Office Version
  1. 2013
Platform
  1. Windows
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 whether it is 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 so 001 from 30/05/2021 will continue being F till 31/12/2021.
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
mad.gif

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
 
Last edited by a moderator:

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,913
Office Version
  1. 2016
Platform
  1. Windows
Hi Maxmadgamer47,

With 500 names and 365 searches this could take some time to execute so I've tried to keep the formulae simple. It goes down to row 9999 for your raw data so change 9999 to whatever is the last row of actual data.

It assumes the data is in the sequence provided, i.e. Name then Date within Name.

You see a different formula in row 2 for each Name as it populates up to the first matching Name/Date using the Old value for that Name and a Date less than the first of that year minus one (i.e. in this case 31 Dec 2020). From row 3 down it looks for a matching Name/Date and if it finds a match it uses the New value but if no match is found it uses the T/F from the row above.

The formulae across row 1 should be populated first so you can delete any columns with #N/A (i.e. no further Names).

maxmadgamer47.xlsx
ABCDEFGHI
1NameDate ChangedOldNew001002003#N/A
20011/8/2021TF1/1/2021TFF#N/A
30012/22/2021FT1/2/2021TFF#N/A
40014/4/2021TF1/3/2021TFF#N/A
50015/30/2021TF1/4/2021TFF#N/A
60021/7/2021FT1/5/2021TFF#N/A
70024/14/2021TF1/6/2021TFF#N/A
80025/26/2021TF1/7/2021TTF#N/A
90033/17/2021FT1/8/2021FTF#N/A
100034/27/2021TF1/9/2021FTF#N/A
111/10/2021FTF#N/A
121/11/2021FTF#N/A
Sheet1
Cell Formulas
RangeFormula
F1:I1F1=INDEX($A$2:$A$9999,MATCH(TRUE,INDEX($A$2:$A$9999>E1,0),0))
F2:I2F2=INDEX($C$2:$C$9999,MATCH(1,INDEX((F$1=$A$2:$A$9999)*($E2-1<$B$2:$B$9999),0,1),0))
E3:E12E3=E2+1
F3:I12F3=IF(ISNA(MATCH(1,INDEX((F$1=$A$2:$A$9999)*($E3=$B$2:$B$9999),0,1),0)),F2,INDEX($D$2:$D$9999,MATCH(1,INDEX((F$1=$A$2:$A$9999)*($E3=$B$2:$B$9999),0,1),0)))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: auto populate field for whole year based on 2 criteria's of T/F & name - impossible?
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 

Forum statistics

Threads
1,141,061
Messages
5,704,043
Members
421,324
Latest member
Devo182

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top