selecting specific rows with a algorithym!?
 

I have an Excel worksheet with about 19.000 rows with soil information.
The different soils are characterized by an ID field (column) with different
soil depth.
I would like create a new worksheet, that has only the rows of the upper
most and lowest soil depth for each soil ID.
Here an example:
BEFORE
ID Soil depth
1 0
1 20
1 40
2 0
2 20
2 40

AFTER
ID Soil depth
1 0
1 40
2 0
2 40

Help is very appreciated!

Thanks in advance!
ffcmello

selecting specific rows with a algorithym!?
 

A12 and A13 hold criterias

=MIN(IF($A$2:$A$7=A12,$B$2:$B$7))
=MAX(IF($A$2:$A$7=A13,$B$2:$B$7))

Both formula are required ctrl+shift+enter, not just enter


"ffcmello" wrote:

I have an Excel worksheet with about 19.000 rows with soil information.
The different soils are characterized by an ID field (column) with different
soil depth.
I would like create a new worksheet, that has only the rows of the upper
most and lowest soil depth for each soil ID.
Here an example:
BEFORE
ID Soil depth
1 0
1 20
1 40
2 0
2 20
2 40

AFTER
ID Soil depth
1 0
1 40
2 0
2 40

Help is very appreciated!

Thanks in advance!
ffcmello

selecting specific rows with a algorithym!?
 

Ok tksTeethless mama...but it didn't work here...maybe i'm doing something
wrong...

The main trouble on this case is that i have a logical answer....(Zero for
all data i've found here) and it do not especify witch lines are those i
need...

And there is a plus on the question

The soil dephts can or cannot repeat (like 0-20, 20-40....but also you have
15-35, 0-1...etc)and you have different quantities of profiles for each ID

so..the example should be like this..

ID SOIL DEPHT (UPPER) (LOWER)
1 0 20
1 20 40
1 40 80
2 0 15
2 15 30
3 0 35
4 0 5
4 5 10
4 10 20
4 20 80
4 100 120

Tks again...

ffcmello


"Teethless mama" wrote:

A12 and A13 hold criterias

=MIN(IF($A$2:$A$7=A12,$B$2:$B$7))
=MAX(IF($A$2:$A$7=A13,$B$2:$B$7))

Both formula are required ctrl+shift+enter, not just enter


"ffcmello" wrote:

I have an Excel worksheet with about 19.000 rows with soil information.
The different soils are characterized by an ID field (column) with different
soil depth.
I would like create a new worksheet, that has only the rows of the upper
most and lowest soil depth for each soil ID.
Here an example:
BEFORE
ID Soil depth
1 0
1 20
1 40
2 0
2 20
2 40

AFTER
ID Soil depth
1 0
1 40
2 0
2 40

Help is very appreciated!

Thanks in advance!
ffcmello