Hi,
Hoping to get some help on how to write a formula for sequential numbering. I have a file which contains a list of numbers and I want to see where the order breaks.
My first value starts at 533165 and ends at 536370. I've been doing some research and have tried using the following formula but it's not producing the number set I want:
= SMALL(IF(ISNA(MATCH(ROW(A$533165:A$536370),A$533165:A$536370,0)),ROW(A$533165:A$536370)),ROW(A1))
Any suggestions would be greatly appreciated.
Thanks!
Your formula is syntactically correct (but not robust).
What exactly is the problem?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Sorry, I should clarify and try to make this simple. Let's say for example I have the following data set:
101
102
104
105
107
The data begins in cell A1 and goes to cell A5. I want a formula that I can put into B1 which would identify the missing numbers (i.e. 103 and 106). The formula I'm using does not capture the missing numbers.
Something simple:
Row\Col B2: =IF(A2+1<>A3, A2+1, "")
Entia non sunt multiplicanda sine necessitate
I was thinking Conditional Formatting.
Use formula in Conditional Formatting:
Select the range A533165 to 536370
Formula:
Please Login or Register to view this content.
Choose the format of choice. This will highlight the last number in the sequence before every break in the sequence if there is more than one.
or something really simple:
insert a helper column and enter in A533165 the following and fill down the length of your data:
Formula:Please Login or Register to view this content.
TRUE will be returned for every break in the sequence. Use the filter on the Data Tab, select the data and the helper column and filter on TRUE.
Last edited by newdoverman; 05-03-2015 at 08:16 PM.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Sure it does. However, it won't return multiple results to a single cell. You have to return each result to its own cell (with that formula).Originally Posted by Ferarri
Data Range
This array formula** entered in B1:
=IFERROR(SMALL(IF(ISNA(MATCH(ROW(INDIRECT("101:107")),A$1:A$5,0)),ROW(INDIRECT("101:107"))),ROWS(B$1:B1)),"")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Last edited by Tony Valko; 05-03-2015 at 09:11 PM.
To get the missing values in the numeric sequence, this site may be of interest.
http://smallbusiness.chron.com/micro...nce-40336.html
That's not a very robust formula!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks