Identify and display the first and last cell of numeric ranges from one column in another

Identify the first cell (starting cell) and the last cell End Cell) in multiple numeric ranges from one column and display the results in a separate column.

Figure-5.png

I have several spreadsheets containing tens of thousands of rows of sequential numeric postal codes that span a few columns. I have been trying to simplify how this data is processed so that I can greatly reduce the amount of keystrokes I have to enter through out the project.

Column A contains a complete list of sequential numeric postal codes.

Column B contains unique codes that can be found in Column A. I have already figured out how to arrange each unique value so that they line up with the identical value in column A. The result is a series of ranges (Point #6)

Column C is where I am stumped and require assistance.

For simplicity I have included a screen shot which has been edited to point to certain requirements (Please excuse the fact that it starts with Point #6, I have been able to solve the first 5 points already.)

For each numeric range created in column B, I need to identify the first and last cell and display the results in the same row but in Column C. (Red Boxes in Column C) I must also follow these following guidelines.

Point #7 - The cells between each numeric range must remain blank.
Point #8 - Ranges that have only one cell only require one cell to function as both the START and
STOP cells for that particular range.
Point #9 - Ranges that have two cells require only the display of the START and STOP cells in Column
C.
Point #10 - If the ranges contain 3 or more cells then the cells between the START and STOP cells in
Column C must remain blank.

I need to write a formula that I can copy/paste down the length of Column C that will accomplish this task and save me from time consuming manual entry of the data.

I need to have these particular START and STOP cells identified because my next task is to turn each range into Regular Expressions (RegEx) displayed in Column D in the row containing the STOP cell. (Any help on accomplishing that would be greatly appreciated as well)

Thank you in advance

See if this works for you...

=IF(OR(B3="",AND(ISNUMBER(B2),ISNUMBER(B4))),"",B3)

Originally Posted by Kicks1981

...

I need to have these particular START and STOP cells identified because my next task is to turn each range into Regular Expressions (RegEx) displayed in Column D in the row containing the STOP cell. (Any help on accomplishing that would be greatly appreciated as well)

Thank you in advance

Please demonstrate what you want in column D.

Thank you so much jhren, you have literally just saved me hours of work!

Fig-1.jpgFig-2.jpg

I have attached two screen shots from the file I am currently working on the created the START and STOP cells using your formula above. (Fig-1.0).

What I now need to do is take each of those individual sets of START STOP cells and create a RegEx and place the " | " symbol at the end of each RegEx (Fig-2.0). As you can see the ranges can go from anywhere between 1 cell in length to thousands later on. I need something that will identify the different ranges accurately.

Up until now have been utilizing an online RegEx generator, but upon looking at what was involved in its creation, I think that it may be something that can be applied to this problem in the form of a macro? VBA? (Not sure which is the proper terminology) you can locate the utility here

http://utilitymill.com/edit/Regex_For_Range (I have had a few slight issues with this utility as sometimes it neglects to place " - " when its required.

If you could help that would be the most amazing thing to happen to me all weekend, hell I might actually get to enjoy the few remaining hours of this weekend!

Will there ever be a range than spans into three decades, e.g. 20729-20741, where 20729 is in the 20720-2029 decade and 20741 is in the 20740-20749 decade and the 20730-20739 decade is in between...

...and would the example be coded 207(29|3[0-9]|40)| or something else?

Also, will there ever be a range that spans into two or more centuries, e.g. 20798-20802 or 20799-20902, and how would they be coded?

In cell D3, and copy down...

Formula:

Please Login or Register  to view this content.

Caveat: Only works for ranges having same first 3 digits and spanning not more than two [absolute] decades.

To do better, I suggest going with VBA code (aka macro), but someone else will have to write it.