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

[maxmadgamer47]

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

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

 

[Toadstool]

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
	A	B	C	D	E	F	G	H	I
1	Name	Date Changed	Old	New		001	002	003	#N/A
2	001	1/8/2021	T	F	1/1/2021	T	F	F	#N/A
3	001	2/22/2021	F	T	1/2/2021	T	F	F	#N/A
4	001	4/4/2021	T	F	1/3/2021	T	F	F	#N/A
5	001	5/30/2021	T	F	1/4/2021	T	F	F	#N/A
6	002	1/7/2021	F	T	1/5/2021	T	F	F	#N/A
7	002	4/14/2021	T	F	1/6/2021	T	F	F	#N/A
8	002	5/26/2021	T	F	1/7/2021	T	T	F	#N/A
9	003	3/17/2021	F	T	1/8/2021	F	T	F	#N/A
10	003	4/27/2021	T	F	1/9/2021	F	T	F	#N/A
11					1/10/2021	F	T	F	#N/A
12					1/11/2021	F	T	F	#N/A
Sheet1

Cell Formulas
Range	Formula
F1:I1	F1	=INDEX($A$2:$A$9999,MATCH(TRUE,INDEX($A$2:$A$9999>E1,0),0))
F2:I2	F2	=INDEX($C$2:$C$9999,MATCH(1,INDEX((F$1=$A$2:$A$9999)*($E2-1<$B$2:$B$9999),0,1),0))
E3:E12	E3	=E2+1
F3:I12	F3	=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]

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.