Hello everyone

I have a number of sheets each containing a list of numbers. Should this be
important, each number is always 11 digits in length and they all start with
a 0.

Many of the numbers are sequential, where the last digit increments by 1.

What I would like to do for those numbers that are part of a range is to
find the first and last number of that range. Is there a formula that could
read through the list one by one and make a separate list of each unique
number and the first and last number where the numbers are sequential?

As an example, using completely made up numbers:

Full list Sorted list
01265875698 01265875698
01285489657 01285489657
01354123452 01354123452
01354123453
01354123454
01354123455
01354123456
01354123457 01354123457
01546985444 01546985444
01546985445
01546985446 01546985446
01978542667 01978542667

I hope what I'm asking makes sense.....but is there a way?

Many thanks

gb

One try ..

Assuming "Full List" is in col A, data from row2 down

Put in B2: =IF(A3-A2=1,"",A2)
Put in B3: =IF(AND(A3-A2=1,A4-A3=1),"",A3)
Copy B3 down

This should return the "Sorted List" in col B
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"gb" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> Hello everyone
>
> I have a number of sheets each containing a list of numbers. Should this
be
> important, each number is always 11 digits in length and they all start
with
> a 0.
>
> Many of the numbers are sequential, where the last digit increments by 1.
>
> What I would like to do for those numbers that are part of a range is to
> find the first and last number of that range. Is there a formula that
could
> read through the list one by one and make a separate list of each unique
> number and the first and last number where the numbers are sequential?
>
> As an example, using completely made up numbers:
>
> Full list Sorted list
> 01265875698 01265875698
> 01285489657 01285489657
> 01354123452 01354123452
> 01354123453
> 01354123454
> 01354123455
> 01354123456
> 01354123457 01354123457
> 01546985444 01546985444
> 01546985445
> 01546985446 01546985446
> 01978542667 01978542667
>
> I hope what I'm asking makes sense.....but is there a way?
>
> Many thanks
>
> gb
>
>

Hi Max!

Thank you very much for this. It seems to work. However, some results are
returned as #VALUE! I have double-checked all numbers follow the same
format, but I cannot think of what's causing this. It's odd, as it works
for most of it, with a few #VALUE! errors thrown amongst the results.

Can you help at all?

Thank you

gb

"Max" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
One try ..

Assuming "Full List" is in col A, data from row2 down

Put in B2: =IF(A3-A2=1,"",A2)
Put in B3: =IF(AND(A3-A2=1,A4-A3=1),"",A3)
Copy B3 down

This should return the "Sorted List" in col B
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"gb" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> Hello everyone
>
> I have a number of sheets each containing a list of numbers. Should this
be
> important, each number is always 11 digits in length and they all start
with
> a 0.
>
> Many of the numbers are sequential, where the last digit increments by 1.
>
> What I would like to do for those numbers that are part of a range is to
> find the first and last number of that range. Is there a formula that
could
> read through the list one by one and make a separate list of each unique
> number and the first and last number where the numbers are sequential?
>
> As an example, using completely made up numbers:
>
> Full list Sorted list
> 01265875698 01265875698
> 01285489657 01285489657
> 01354123452 01354123452
> 01354123453
> 01354123454
> 01354123455
> 01354123456
> 01354123457 01354123457
> 01546985444 01546985444
> 01546985445
> 01546985446 01546985446
> 01978542667 01978542667
>
> I hope what I'm asking makes sense.....but is there a way?
>
> Many thanks
>
> gb
>
>

Think some data in col A may not be clean

Try this check:

Put in say C2: =ISNUMBER(A2+0)
Copy down as many rows as there is data in col A
Col C should return TRUE

Do a Data > Filter > Autofilter on col C in C1
Select FALSE from the droplist
Examine col A's cell(s) in the filtered rows
(select the cell(s), click inside the formula bar)
and "correct" these manually
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

gb <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Max!
>
> Thank you very much for this. It seems to work. However, some results
are
> returned as #VALUE! I have double-checked all numbers follow the same
> format, but I cannot think of what's causing this. It's odd, as it works
> for most of it, with a few #VALUE! errors thrown amongst the results.
>
> Can you help at all?

Hello Max

Sorry for the delay.

Just to confirm that you were right, and the second formula you provided did
the trick to identify that.

Thank you very much and Merry Xmas!

gb

"Max" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
Think some data in col A may not be clean

Try this check:

Put in say C2: =ISNUMBER(A2+0)
Copy down as many rows as there is data in col A
Col C should return TRUE

Do a Data > Filter > Autofilter on col C in C1
Select FALSE from the droplist
Examine col A's cell(s) in the filtered rows
(select the cell(s), click inside the formula bar)
and "correct" these manually
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

gb <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Max!
>
> Thank you very much for this. It seems to work. However, some results
are
> returned as #VALUE! I have double-checked all numbers follow the same
> format, but I cannot think of what's causing this. It's odd, as it works
> for most of it, with a few #VALUE! errors thrown amongst the results.
>
> Can you help at all?

Glad to hear that it worked !
Thanks for posting back
Merry Christmas !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

gb <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello Max
>
> Sorry for the delay.
>
> Just to confirm that you were right, and the second formula you provided
did
> the trick to identify that.
>
> Thank you very much and Merry Xmas!