Sorting a full list into a list of number ranges

  • Thread starter Thread starter gb
  • Start date Start date
G

gb

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

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

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