Converting Time Start/Stop Ranges to Presence/Absence

[Thomas Scott]

I have variables (Var) that operate each year and want to convert only those that are take place in 5-year spans into simple yes/no. There would be only six resulting worksheets with each showing results for only that 5-year span. Partially missing data for start, stop,. or both may be present. I have tried If Then concepts but cannot see how to jump (increment) in 2 and 5 year increments without looping that will require a macro. I can do formulas well but writing macros is a much farther stretch. The following is as far as I can figure out. Thanks for any assistance.

Create Timeslices Model.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Var1Start	Var1Stop	Var2Start	Var2Stop	<==Collected data (> 100 Vars)			Reduced to 6 worksheets, each 5 year period
2	Alpha	2010	2012	2010	2014
3	Beta	2011	2013	2010	2012
4	Gamma	2010	2015	2014	2019		Problem: Test for operation during 5 year interval and record yes/no
5	Delta	2015	2019	2016	2021		Partially missing data can be present, i.e., missing a start, stop, or both
6	Epsilon	2016	2020	2014	2016
7	Zeta	2007	2008	2012	2014
8
9	Name	Var1	Var2	<==Worksheet A(2010-2014)
10	Alpha	1	1
11	Beta	1	1
12	Gamma	1	1
13	Delta	1	0
14	Epsilon	0	1
15	Zeta	0	0
16
17	Name	Var1	Var2	<==Worksheet B(2015-2019)
18	Alpha	0	0
19	Beta	0	0
20	Gamma	1	1
21	Delta	1	1
22	Epsilon	1	1
23	Zeta	0	0
Example

 

[myall_blues]

Here is a formula solution for your given data. Obviously the formulas etc in A9:C15 and A17:C23 can be moved to separate worksheets.

I wasn't sure what answer you wanted if one or other or both years are missing, although I'm guessing perhaps:
- 1 or other missing - test the remaining value?
- Both missing - answer = no.

If you let me know what you want to do we can probably extend this formula.

junk.xlsx
	A	B	C	D	E	F	G	H	I
1	Name	Var1Start	Var1Stop	Var2Start	Var2Stop	<==Collected data (> 100 Vars)			Reduced to 6 worksheets, each 5 year period
2	Alpha	2010	2012	2010	2014
3	Beta	2011	2013	2010	2012
4	Gamma	2010	2015	2014	2019		Problem: Test for operation during 5 year interval and record yes/no
5	Delta	2015	2019	2016	2021		Partially missing data can be present, i.e., missing a start, stop, or both
6	Epsilon	2016	2020	2014	2016
7	Zeta	2007	2008	2012	2014
8
9					Year From	Year To
10	Name	Var1	Var2	<==Worksheet A(2010-2014)	2010	2014
11	Alpha	Yes	Yes
12	Beta	Yes	Yes
13	Gamma	Yes	Yes
14	Delta	No	No
15	Epsilon	No	Yes
16	Zeta	No	Yes
17					Year From	Year To
18	Name	Var1	Var2	<==Worksheet B(2015-2019)	2015	2019
19	Alpha	No	No
20	Beta	No	No
21	Gamma	Yes	Yes
22	Delta	Yes	Yes
23	Epsilon	Yes	Yes
24	Zeta	No	No
Sheet1

Cell Formulas
Range	Formula
B11:B16	B11	=IF(OR($F$10<B2,C2<$E$10),"No", "Yes")
C11:C16	C11	=IF(OR($F$10<D2,E2<$E$10),"No", "Yes")
B19:B24	B19	=IF(OR($F$18<B2,C2<$E$18),"No", "Yes")
C19:C24	C19	=IF(OR($F$18<D2,E2<$E$18),"No", "Yes")

 

[Thomas Scott]

Ok, but I did not state the problem clearly enough.
1. Var1Start, Var1Stop, etc. in row 2 need to be tested and the results sent to a new Worksheet. I can change what you proposed to add the sheetname but the 2nd problem is more difficult.

2. There are hundreds of VARs, incrementing every 2 columns (start/stop), so the formula must somehow increment 2 more columns and test again and then send those results. I did not see how a single formula could do that without some sort of counter which made me thing of loops which made think macro.

Thanks.

 

[Thomas Scott]

Murray,

I see that I am still not clear enough. The test for each 5 year time period (in this sample) tests for the 5-year range and sends the results for only that time period to the matching worksheet The first new worksheet would be 2010-2014. The second new worksheet would be the 2015-2019 up to six worksheets receiving the yes/no responses.
All the rows would be checked (example used Greek alphabet) to see if the Start and Stop were within the 5-year range and sent to that worksheet but there can be 1-2 hundred rows.

This all seems strange but I am studying variable start/stops, testing for which time range they occurred, then sending the results to the appropriate time range worksheet. I can do this manually by simply look at the start and stop and knowing if it should be coded yes no, but doing it with Excel is not so easy but with up to 80,000 tests it would be no fun. If only there was real artificial intelligence available!

Thanks again.

 

[myall_blues]

No need for AI - we have ourselves.

Here is an example using formulas that increments 1 column on the result sheet (e.g. 2010-2014) for every two columns on the data sheet (Sheet1 in my case). I've done 4 vars for demonstration but the number is only limited by the number of columns available on the sheet.

Since you only have six sheets to create this would be quick to set up - the 2010-2014 sheet is an example of the first one.

Here are your data:

ThomasScott.xlsx
	A	B	C	D	E	F	G	H	I
1	Name	Var1Start	Var1Stop	Var2Start	Var2Stop	Var3Start	Var3Stop	Var4Start	Var4Stop
2	Alpha	2010	2012	2010	2014	2010	2014	2010	2014
3	Beta	2011	2013	2010	2012	2010	2012	2010	2012
4	Gamma	2010	2015	2014	2019	2014	2019	2014	2019
5	Delta	2015	2019	2016	2021	2016	2021	2016	2021
6	Epsilon	2016	2020	2014	2016	2014	2016	2014	2016
7	Zeta	2007	2008	2012	2014	2012	2014	2012	2014
Sheet1

and here is the output:

ThomasScott.xlsx
	A	B	C	D	E
1	Year From:	2010
2	Year To:	2014
3
4	Name	Var1	Var2	Var3	Var4
5	Alpha	Yes	Yes	Yes	Yes
6	Beta	Yes	Yes	Yes	Yes
7	Gamma	Yes	Yes	Yes	Yes
8	Delta	No	No	No	No
9	Epsilon	No	Yes	Yes	Yes
10	Zeta	No	Yes	Yes	Yes
2010-2014

Cell Formulas
Range	Formula
B4:E4	B4	=LEFT(OFFSET(Sheet1!$B$1,0,(COLUMN(Sheet1!B$1)-2)*2),4)
B5:E10	B5	=IF(OR($B$2<OFFSET(Sheet1!$B2,0,(COLUMN(Sheet1!B2)-2)*2),OFFSET(Sheet1!$B2,0,(COLUMN(Sheet1!B2)-2)*2+1)<$B$1),"No", "Yes")

 

[Akuini]

@Thomas Scott
In your example (post #1), in row 15, why does zeta get 0 0?

 

[myall_blues]

@Thomas Scott
In your example (post #1), in row 15, why does zeta get 0 0?

Delta should also be 0,0 not 1,0 I believe.