Excel 2007 : Collate Consecutive Numbers

**imnottellling** · 03-07-2010, 09:16 PM

Hi all. I have been an avid visitor of this site for awhile and thank u all for your help.

Here is my problem.

I have a set of phone numbers, about 6,000 of them, listed in a single column, sorted in descending order.
I would like to write a formula that looks through the column and extracts phone numbers where the last three digits are consecutive numbers.
ie 1300 360 601 and 1300 360 602.

Is this possible?

Thanks in advance for your advise.

**shg** · 03-07-2010, 09:29 PM

If those two numbers are in A1 and A2, then in B2, =RIGHT(A2,3) - RIGHT(A1,3) = 1 returns true if the last three digits are sequential.

**imnottellling** · 03-07-2010, 09:53 PM

Thanks for that.

The numbers are in A1 to A4596.
What I would like to do is search through the numbers in the column, and pull out any numbers, where the last three digits are consecutive, and paste them into a new worksheet.

Does this make sense?

**JBeaucaire** · 03-07-2010, 11:48 PM

SHG was showing you how to get started....

1) Put that formula in an empty adjacent column.
2) Copy if down the data set
3) Make sure that row1 has "titles" in it, that the data actually starts at row 2
4) Turn on the Data > Filter > AutoFilter
5) Filter the new column by "TRUE"
6) Copy all the visible data to another worksheet.

Done.