Finding 2nd range in row

Dutch1956

Board Regular
Joined
Sep 14, 2010
Messages
80
Hi All,

hopefully somebody will be able to help me with the below;
00:00
00:30
01:00
01:30
02:00
02:30
03:00
03:30
04:00
04:30
05:00
05:30
06:00
06:30
07:00
2
5
6
6
8
8
7
9

<TBODY>
</TBODY>

The below formula will return the value of the first non blank cell in row 2:
=INDEX($A$1:$O$1,MATCH(TRUE,INDEX(($A2:$O2<>0),0),0)) Result: 01:00
The next formula returns he last non blank cell in row 2:
=LOOKUP(9.999999999E+307,$A2:$O2,$A$1:$O$1) Result: 06:00
I am looking for the start and end time of the first range and the start and end time of the second range.
Which formulas do I need to use to get
01:00-02:30 and 04:30-06:00.
any help is very much appreciated.

Bert.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Does this help you?


Excel 2010
ABCDEFGHIJKLMNO
100:0000:3001:0001:3002:0002:3003:0003:3004:0004:3005:0005:3006:0006:3007:00
225668879
3
4Start101:00
5End102:30
6Start204:30
7End206:00
Sheet1
Cell Formulas
RangeFormula
B4=INDEX($A$1:$O$1,MATCH(TRUE,INDEX(($A2:$O2<>0),0),0))
B5=INDEX(INDEX($A$1:$O$1,MATCH(B4,$A$1:$O$1)):$O$1,MATCH(TRUE,INDEX(INDEX($A$2:$O$2,MATCH(B4,$A$1:$O$1)):$O2=0,0),0)-1)
B6=INDEX(INDEX($A$1:$O$1,MATCH(B5,$A$1:$O$1)+1):$O$1,MATCH(TRUE,INDEX(INDEX($A$2:$O$2,MATCH(B5,$A$1:$O$1)+1):$O2<>0,0),0))
B7=LOOKUP(9.999999999E+307,$A2:$O2,$A$1:$O$1)
 
Upvote 0
Does this help you?

Excel 2010
ABCDEFGHIJKLMNO
100:0000:3001:0001:3002:0002:3003:0003:3004:0004:3005:0005:3006:0006:3007:00
225668879
3
4Start101:00
5End102:30
6Start204:30
7End206:00

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>
Sheet1

Worksheet Formulas
CellFormula
B4=INDEX($A$1:$O$1,MATCH(TRUE,INDEX(($A2:$O2<>0),0),0))
B5=INDEX(INDEX($A$1:$O$1,MATCH(B4,$A$1:$O$1)):$O$1,MATCH(TRUE,INDEX(INDEX($A$2:$O$2,MATCH(B4,$A$1:$O$1)):$O2=0,0),0)-1)
B6=INDEX(INDEX($A$1:$O$1,MATCH(B5,$A$1:$O$1)+1):$O$1,MATCH(TRUE,INDEX(INDEX($A$2:$O$2,MATCH(B5,$A$1:$O$1)+1):$O2<>0,0),0))
B7=LOOKUP(9.999999999E+307,$A2:$O2,$A$1:$O$1)

<THEAD>
</THEAD><TBODY>
</TBODY>

<TBODY>
</TBODY>


Andrew,

Than you so much for your quick response. You are amazing for helping me out again.
It works perfect, exactly what I was looking for. I never thought of doing it that way.
One last question related to this post:
What can I do when the data row 2 is a result of a formula?


Bert.
 
Upvote 0
What's the formula?


Hi Andrew,

the data is in a pivot table and my previous question might not be an issue when I leave the zeros out.
If it is ok then I will test this a little later tonight and get back to you.
Again, thank you so much.

Bert.
 
Upvote 0
Hi Andrew,

the data is in a pivot table and my previous question might not be an issue when I leave the zeros out.
If it is ok then I will test this a little later tonight and get back to you.
Again, thank you so much.

Bert.


Hi Andrew,

your formulas work perfectly. The problem I encountered was that some cells were not blank. They had a value < 0.
Once again, I can not thank you enough and I dare to say that many MrExcel members would be lost without you.

Bert.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

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
Back
Top