Calculate ranges based on 2 columns

[gschmidt]

Hi,

I have 2 columns with data:

• Column A has chainage (CH) values, which are ascending consecutive numbers, and not always start with chainage 1
• Column B has FALSE/TRUE values

I want to determine the values of all the Start (see column D) and End chainages (see column E), for the rows where the value of column B between Start and End is TRUE.
See example picture.

This is an example of 30 lines for column A/B, but the amount of rows and the amount of TRUE/FALSE values may differ
So the amount of [D] Start CH and [E] End CH rows also may differ

How do I do this in Excel?

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E
1
2	1	FALSE		3	8
3	2	FALSE		11	13
4	3	TRUE		15	15
5	4	TRUE
6	5	TRUE
7	6	TRUE
8	7	TRUE
9	8	TRUE
10	9	FALSE
11	10	FALSE
12	11	TRUE
13	12	TRUE
14	13	TRUE
15	14	FALSE
16	15	TRUE
17
18
Lists

Cell Formulas
Range	Formula
D2:D6	D2	=IFERROR(INDEX($A$2:$A$200,AGGREGATE(15,6,(ROW($A$2:$A$200)-ROW($A$2)+1)/($B$1:$B$199=FALSE)/($B$2:$B$200=TRUE),ROWS(D$2:D2))),"")
E2:E6	E2	=IFERROR(INDEX($A$2:$A$200,AGGREGATE(15,6,(ROW($A$2:$A$200)-ROW($A$2)+1)/($B$1:$B$199=TRUE)/($B$2:$B$200<>TRUE),ROWS(E$2:E2))-1),"")

 

[gschmidt]

Thanx, that's fast! I rarely use excel so I would never come up with this solution myself.
I will study the function carefully!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[gschmidt]

You're welcome & thanks for the feedback.

Wel I am trying to intergrate this into my Excelsheet, but sadly I get an empty result.
Can it be that these 2 functions can only handle a maximum number of rows?
Because your test with 15 rows works, with 1,000 rows works also but with > 10,000 rows the resulting cells are empty

Most of the time I have to deal with > 40,000 rows
This XLSX does not start with CH 1 but with CH 23400 and ends at CH 64846
(FYI the last CH value is the total length of an offshore windfarm cable which is 64.846 km long)

 

[Fluff]

It should work with 40,000+ rows, although it might take a while to calculate.
What is your amended formula?

 

[gschmidt]

It should work with 40,000+ rows, although it might take a while to calculate.
What is your amended formula?

Time doesn't matter...this will always be faster than the current process

This is my "Input" Sheet.
The grey columns A-J are values from a CSV output file of other software.
The CSV values will be copied into this template sheet.
The amount of rows is always different, depending on the Chainage (length) of the cable, but at least > 10000-60000 rows

Column N (Valid CH for Polygons) is the TRUE/FALSE column.
This value is the result of a formula:

=IF((OR(K4>=$L$2, L4>=$L$2, M4>=$L$2)), "TRUE", "FALSE")

The result has to be listed in columns B and C of sheet "SOWL"
My amended formulas are

For B2 is

=IFERROR(INDEX(Input!$D$4:$D$50000,AGGREGATE(15,6,(ROW(Input!$D$4:$D$50000)-ROW(Input!$D$4)+1)/(Input!$N$3:$N$49999=FALSE)/(Input!$N$4:$N$50000=TRUE),ROWS(B$2:B2))),"")

For C2 is

=IFERROR(INDEX(Input!$D$4:$D$50000,AGGREGATE(15,6,(ROW(Input!$D$4:$D$50000)-ROW(Input!$D$4)+1)/(Input!$N$3:$N$49999=TRUE)/(Input!$N$4:$N$50000<>TRUE),ROWS(C$2:C2))-1),"")

I have taken Row 50000 as the max for now

I don't get errors, but as you can see the cell B2 and C2 are empty....
What am I doing wrong here?

 

[gschmidt]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E
1
2	1	FALSE		3	8
3	2	FALSE		11	13
4	3	TRUE		15	15
5	4	TRUE
6	5	TRUE
7	6	TRUE
8	7	TRUE
9	8	TRUE
10	9	FALSE
11	10	FALSE
12	11	TRUE
13	12	TRUE
14	13	TRUE
15	14	FALSE
16	15	TRUE
17
18
Lists

Cell Formulas
Range	Formula
D2:D6	D2	=IFERROR(INDEX($A$2:$A$200,AGGREGATE(15,6,(ROW($A$2:$A$200)-ROW($A$2)+1)/($B$1:$B$199=FALSE)/($B$2:$B$200=TRUE),ROWS(D$2:D2))),"")
E2:E6	E2	=IFERROR(INDEX($A$2:$A$200,AGGREGATE(15,6,(ROW($A$2:$A$200)-ROW($A$2)+1)/($B$1:$B$199=TRUE)/($B$2:$B$200<>TRUE),ROWS(E$2:E2))-1),"")

I have created your initial example in Test.xlsx file from scratch....and this works
Also when I move the result columns D2 and E2 to a next Sheet2 in column B2 and C2 (and add Sheet1!)
Even when I shift the Columns in Sheet1 to the positions I want them (A2-->D4 and B2-->N4), the formula is updated and the values are calculated.
But if I copy the formula into my own sheet....no error and more important no result


Strange is that if the TRUE/FALSE column values are copied from the input XLSX to the test XLSX (from Excel to Excel, paste only values), there is no error but also no result (empty cells)
And if I copy the TRUE/FALSE column values from the input sheet first to a plain TXT file, and from there pasted into the test XLSX sheet, then cells are filled with the correct result.
So I am a bit lost what there error is...

 

[Fluff]

You're col N values are text rather than boolean values.
Change the col N formula to

=OR(K4>=$L$2, L4>=$L$2, M4>=$L$2)

 

[gschmidt]

I figured it out!

FALSE and TRUE in the formula are Booleans....and In my sheet they are plain TEXT
So in the formulas I changed FALSE to "FALSE" and TRUE to "TRUE" and now it is working as aspected