formula to show ranges in columns

[Lori]

I am trying to find a formula that will show the individual ranges shown in
column A. The first number in the range will appear in column B and the last
number in the range will appear in the adjacent cell in column C. Is this
even possible?

A B C

1500 1500 1502
1501
1502

1505 1505 1507
1506
1507

1509 1509 1510
1510

 

[bj]

try
=min(range1) in column B
and
=max(range1) in column C

 

[Lori]

Thank you, but that doesn't seem to work because there are 1000s of numbers
in column A with numerous individual ranges, and I want all the individual
ranges to be shown in columns B and C.

 

[bj]

How do you differencialte the different individual ranges?

 

[Lori]

It's not set up to differentiate between individual ranges. What would you
suggest?

 

[bj]

from your first example are all of them in groups of three separated by one
row.
if this is the case you could use in B3
=if(A2="",min(A3:A5),"")
and in C3
=if(A2="",max(A3:A5),"")
If I understand what you are doing, you will need either a pattern or a
identifier of some type which will identify the different grouping you wish
to get the range for.

If it is not three numbers per group, and you use a blank line between the
groups what is the max and minimum number of data ppoints you want to get the
range for?
Also in your example the trios were in order. Is this always the case.
(there are some short cuts which can be used if it is always the case.)

 

[Lori]

The numbers in column A are always in numerical order and there are no
spaces separating the individual ranges. The individual ranges vary. There
could be 2 numbers in numerical order to hundreds of numbers in numerical
order. Here is a better example.


A B C
1500 1500 1502
1501
1502
1505 1505 1507
1506
1507
1509 1509 1510
1510
1512 1512 1516
1513
1514
1515
1516

 

[bj]

from this example it looks as though the ranges are defined as consecutive
numbers and the range divisions are caused by missing numbers
if this is the case in B2 enter
= if(A2=A1+1,"",A2)
in D2 enter
=if(A2=A3-1,"",A2)
in C2 enter
=if(B2<>"",D2,"")
copy B22 to the end of your data and hide Column D

If my assumption as to what defines the ranges is wrong, Please let me know
with another example and I wil trry again.