function string to find non-overlapping ranges of values

Dear all,

I'm trying to implement a little look up routine in Excel (not in VBA) and can't quite seem to crack it.

Essentially, I have rows of data showing min and max values in separate columns. Starting with the first row, I want to look down at each row and flag with an "x" those rows which have min and max values (i.e. ranges) which don't overlap with any other rows. The columns are sorted from min to max.

For instance given the following data:

0 10
10 30
10 35
10 37
20 40
40 45
40 47
50 60

The function I need would start with an x in row 1 and find all subsequent non-overlapping ranges, hence:

0 10 x
10 30 x
10 35
10 37
20 40
40 45 x
40 47
50 60 x

The first range goes up to 10, so the function would then look for the next row which begins with 10 or more, which is the range in row 2, which is 10 to 30, etc..

Any help would be gratefully appreciated, any questions just ask

Thanks for the reply tigeravatar,

Apologies though - seems I used a poor example, sorry. In the dataset I have this won't always work. Consider these data instead:
0 10 x
10 30 x
10 35
10 37
30 40 x
40 45 x
40 47
50 60 x

I changed the 20 to a 30, which makes things a little more difficult I think! The function needs to look back up the columns to find the last "x" I guess. I just don't quite know how this can be done without things getting circular. It's no problem to extend a solution to more columns if need be.

I don't understand your logic. Doesn't the range 10 30 overlap the range 10 35 and the range 10 37 ?

Pete

Originally Posted by carbonboywonder

Pete_UK. Yes, hence why these shouldn't be flagged. 10 30 is the first instance down the columns of a range which doesn't overlap with ranges in the rows above.

I read this differently, then:

Starting with the first row, I want to look down at each row and flag with an "x" those rows which have min and max values (i.e. ranges) which don't overlap with any other rows.

Nothing there about flagging the first instance only.

Just curious ...

Pete

Ah yes, sorry for not being clear in the first instance!

I've just noticed a slight issue with the function you gave me tigeravatar, sorry...

I just extended my data by one row and got this:
0 10 x
10 30 x
10 35
10 37
30 40 x
40 45 x
40 47
50 60 x
55 70 x

There shouldn't be an x after the 55-70 row as this range overlaps with the 50-60 row above. If I change 55 to 44 it works, which suggests the lookup function isn't looking at the very first x above it but the first x it sees is the x next to the 40-45 row. Anyone any ideas why the lookup is doing this?

Thanks again tigeravatar, looks like this has done the trick. Without wishing to bother you even further, can you explain how this works. I'm familiar with Lookup but can't see how the 2,1/($C$1:C1="x") works? Feel free to leave it there though, this is a great help regardless :-)
D

Great stuff, thanks!