| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Indicate missing number in a sequence
can somone please assist me what formula I can use to find the missing in a
sequence number? see example below. How could I find the missing 1327, 1330 and 1333? Order # 1325 1326 1328 1329 1331 1332 1334 1335 Any help will be appreciated. Thank youj, Beth |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indicate missing number in a sequence
Would this work for you?
With your numbers in A1 to A8, enter this in B2 and copy down: =IF(A1+1=A2,"",A1+1) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Beth" wrote in message ... can somone please assist me what formula I can use to find the missing in a sequence number? see example below. How could I find the missing 1327, 1330 and 1333? Order # 1325 1326 1328 1329 1331 1332 1334 1335 Any help will be appreciated. Thank youj, Beth |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indicate missing number in a sequence
Sub findmissingnumbersinlist()
mc = 1 'for col A For i = 2 To Cells(Rows.Count, mc).End(xlUp).Row - 1 If Cells(i + 1, mc) < Cells(i, mc) + 1 Then MsgBox "Missing " & Cells(i, mc) + 1 & " at row " & i + 1 End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Beth" wrote in message ... can somone please assist me what formula I can use to find the missing in a sequence number? see example below. How could I find the missing 1327, 1330 and 1333? Order # 1325 1326 1328 1329 1331 1332 1334 1335 Any help will be appreciated. Thank youj, Beth |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indicate missing number in a sequence
Thank you for your help on this. It is not working. The header is on A1 and
the first data starts from A2. Where do I need to start typing the formula (the first formula)? Thank you, Beth "Ragdyer" wrote: Would this work for you? With your numbers in A1 to A8, enter this in B2 and copy down: =IF(A1+1=A2,"",A1+1) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Beth" wrote in message ... can somone please assist me what formula I can use to find the missing in a sequence number? see example below. How could I find the missing 1327, 1330 and 1333? Order # 1325 1326 1328 1329 1331 1332 1334 1335 Any help will be appreciated. Thank youj, Beth |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indicate missing number in a sequence
Hi Beth
I think you need to change Ragdyer's formula to this if you have a header in A1and place it in B3, then copy down. You will need to input the first value in B2 which is 1325. =IF(A2+1=A2,"",A2+1) -- HTH Appreciate that you click the Yes button below if this posting is helpful cheers, francis "Beth" wrote in message ... Thank you for your help on this. It is not working. The header is on A1 and the first data starts from A2. Where do I need to start typing the formula (the first formula)? Thank you, Beth "Ragdyer" wrote: Would this work for you? With your numbers in A1 to A8, enter this in B2 and copy down: =IF(A1+1=A2,"",A1+1) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Beth" wrote in message ... can somone please assist me what formula I can use to find the missing in a sequence number? see example below. How could I find the missing 1327, 1330 and 1333? Order # 1325 1326 1328 1329 1331 1332 1334 1335 Any help will be appreciated. Thank youj, Beth |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indicate missing number in a sequence
Thank you, xlm.
by using your formula I got below missing numbers. could you please review and advise. Order # Missing Numbers 1325 1326 1326 1327 1328 1329 1329 1330 1331 1332 1332 1333 1334 1335 1335 1336 1336 1337 1337 1338 Thank you, Beth "xlm" wrote: Hi Beth I think you need to change Ragdyer's formula to this if you have a header in A1and place it in B3, then copy down. You will need to input the first value in B2 which is 1325. =IF(A2+1=A2,"",A2+1) -- HTH Appreciate that you click the Yes button below if this posting is helpful cheers, francis "Beth" wrote in message ... Thank you for your help on this. It is not working. The header is on A1 and the first data starts from A2. Where do I need to start typing the formula (the first formula)? Thank you, Beth "Ragdyer" wrote: Would this work for you? With your numbers in A1 to A8, enter this in B2 and copy down: =IF(A1+1=A2,"",A1+1) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Beth" wrote in message ... can somone please assist me what formula I can use to find the missing in a sequence number? see example below. How could I find the missing 1327, 1330 and 1333? Order # 1325 1326 1328 1329 1331 1332 1334 1335 Any help will be appreciated. Thank youj, Beth |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indicate missing number in a sequence
On Thu, 1 Jan 2009 08:54:01 -0800, Beth wrote:
can somone please assist me what formula I can use to find the missing in a sequence number? see example below. How could I find the missing 1327, 1330 and 1333? Order # 1325 1326 1328 1329 1331 1332 1334 1335 Any help will be appreciated. Thank youj, Beth All of the following are **array** formulas. After pasting them into a cell, they must be entered by holding down <ctrl<shift while hitting <enter. If you do this correctly, Excel will place braces {...} around the formula. In the formulas below, "Order" refers to the range in which you have these values listed. I NAME'd a sample range, but you could substitute an absolute reference (e.g. $A$2:$A$16) For the missing values in descending order, enter: =LARGE(IF(COUNTIF(Order,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))),""),ROWS($1:1)) and copy/drag down until you begin to get #NUM! errors For the missing values in ascending order, enter: =LARGE(IF(COUNTIF(Order,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))),""),COUNT(IF(COUNTIF(Order,ROW(I NDIRECT(MIN(Order) &":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))),""))+1-ROWS($1:1)) and copy/drag down until you begin to get #NUM! errors If you want to avoid the error messages, you can use one of the following formulas instead: If you are running Excel 2007: Descending order: =IFERROR(LARGE(IF(COUNTIF(Order,ROW(INDIRECT(MIN(O rder) &":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))),""),ROWS($1:1)),"") Ascending order: =IFERROR(LARGE(IF(COUNTIF(Order,ROW(INDIRECT(MIN(O rder) &":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))),""),COUNT(IF(COUNTIF(Order,ROW(I NDIRECT(MIN(Order) &":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))),""))+1-ROWS($1:1)),"") If you are running a version of Excel prior to 2007: Descending order: =IF(ISERR(LARGE(IF(COUNTIF(Order,ROW(INDIRECT(MIN( Order) &":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))),""),ROWS($1:1))),"",LARGE(IF(COU NTIF(Order,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))),""),ROWS($1:1))) Ascending order: &":"&MAX(Order))),""),COUNT(IF(COUNTIF(Order,ROW(I NDIRECT(MIN(Order) &":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))),""))+1-ROWS($1:1))),"",LARGE(IF(COUNTIF(Order,ROW(INDIREC T(MIN(Order) &":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))),""),COUNT(IF(COUNTIF(Order,ROW(I NDIRECT(MIN(Order) &":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))),""))+1-ROWS($1:1))) --ron |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indicate missing number in a sequence
Try this array formula** :
Assuming your numbers are in the range A2:A9 The sequence you want to check is from 1325 to 1335. Array entered** in B2: =SMALL(IF(ISNA(MATCH(ROW(A$1325:A$1335),A$2:A$9,0) ),ROW(A$1325:A$1335)),ROWS(B$2:B2)) Copy down until you #NUM! errors meaning all missing numbers have been returned. Note: this method is slow to calculate on very large sequences. Also note that you're limited to sequences with a maximum end value that is equal to the number or rows that a worksheet has which is dependent upon what version of Excel you're using. ** 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. -- Biff Microsoft Excel MVP "Beth" wrote in message ... can somone please assist me what formula I can use to find the missing in a sequence number? see example below. How could I find the missing 1327, 1330 and 1333? Order # 1325 1326 1328 1329 1331 1332 1334 1335 Any help will be appreciated. Thank youj, Beth |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indicate missing number in a sequence
I think you probably intended to say:
=IF(A2+1=A3,"",A2+1) ? -- David Biddulph xlm wrote: Hi Beth I think you need to change Ragdyer's formula to this if you have a header in A1and place it in B3, then copy down. You will need to input the first value in B2 which is 1325. =IF(A2+1=A2,"",A2+1) cheers, francis "Beth" wrote in message ... Thank you for your help on this. It is not working. The header is on A1 and the first data starts from A2. Where do I need to start typing the formula (the first formula)? Thank you, Beth "Ragdyer" wrote: Would this work for you? With your numbers in A1 to A8, enter this in B2 and copy down: =IF(A1+1=A2,"",A1+1) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Beth" wrote in message ... can somone please assist me what formula I can use to find the missing in a sequence number? see example below. How could I find the missing 1327, 1330 and 1333? Order # 1325 1326 1328 1329 1331 1332 1334 1335 Any help will be appreciated. Thank youj, Beth |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indicate missing number in a sequence
"Ron Rosenfeld" wrote in message
... On Thu, 1 Jan 2009 08:54:01 -0800, Beth wrote: can somone please assist me what formula I can use to find the missing in a sequence number? see example below. How could I find the missing 1327, 1330 and 1333? Order # 1325 1326 1328 1329 1331 1332 1334 1335 Any help will be appreciated. Thank youj, Beth All of the following are **array** formulas. After pasting them into a cell, they must be entered by holding down <ctrl<shift while hitting <enter. If you do this correctly, Excel will place braces {...} around the formula. In the formulas below, "Order" refers to the range in which you have these values listed. I NAME'd a sample range, but you could substitute an absolute reference (e.g. $A$2:$A$16) For the missing values in descending order, enter: =LARGE(IF(COUNTIF(Order,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))),""),ROWS($1:1)) and copy/drag down until you begin to get #NUM! errors For the missing values in ascending order, enter: =LARGE(IF(COUNTIF(Order,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))),""),COUNT(IF(COUNTIF(Order,ROW(I NDIRECT(MIN(Order) &":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))),""))+1-ROWS($1:1)) and copy/drag down until you begin to get #NUM! errors If you want to avoid the error messages, you can use one of the following formulas instead: If you are running Excel 2007: Descending order: =IFERROR(LARGE(IF(COUNTIF(Order,ROW(INDIRECT(MIN(O rder) &":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))),""),ROWS($1:1)),"") Ascending order: =IFERROR(LARGE(IF(COUNTIF(Order,ROW(INDIRECT(MIN(O rder) &":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))),""),COUNT(IF(COUNTIF(Order,ROW(I NDIRECT(MIN(Order) &":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))),""))+1-ROWS($1:1)),"") If you are running a version of Excel prior to 2007: Descending order: =IF(ISERR(LARGE(IF(COUNTIF(Order,ROW(INDIRECT(MIN( Order) &":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))),""),ROWS($1:1))),"",LARGE(IF(COU NTIF(Order,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))),""),ROWS($1:1))) Ascending order: &":"&MAX(Order))),""),COUNT(IF(COUNTIF(Order,ROW(I NDIRECT(MIN(Order) &":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))),""))+1-ROWS($1:1))),"",LARGE(IF(COUNTIF(Order,ROW(INDIREC T(MIN(Order) &":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))),""),COUNT(IF(COUNTIF(Order,ROW(I NDIRECT(MIN(Order) &":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))),""))+1-ROWS($1:1))) --ron Assuming no numbers are duplicated. A more efficient error trap for versions prior to Excel 2007 might be: =IF(ROWS(B$2:B2)<=MAX(Order)-MIN(Order)+1-COUNT(Order),..... -- Biff Microsoft Excel MVP |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indicate missing number in a sequence
Hi Beth
Did you type the number 1325 into cell B2? and place the formula in cell B3? Otherwise, if you just want to find the missing number, David's formula will do that. -- HTH Appreciate that you click the Yes button below if this posting is helpful cheers, francis "Beth" wrote in message ... Thank you, xlm. by using your formula I got below missing numbers. could you please review and advise. Order # Missing Numbers 1325 1326 1326 1327 1328 1329 1329 1330 1331 1332 1332 1333 1334 1335 1335 1336 1336 1337 1337 1338 Thank you, Beth "xlm" wrote: Hi Beth I think you need to change Ragdyer's formula to this if you have a header in A1and place it in B3, then copy down. You will need to input the first value in B2 which is 1325. =IF(A2+1=A2,"",A2+1) -- HTH Appreciate that you click the Yes button below if this posting is helpful cheers, francis "Beth" wrote in message ... Thank you for your help on this. It is not working. The header is on A1 and the first data starts from A2. Where do I need to start typing the formula (the first formula)? Thank you, Beth "Ragdyer" wrote: Would this work for you? With your numbers in A1 to A8, enter this in B2 and copy down: =IF(A1+1=A2,"",A1+1) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Beth" wrote in message ... can somone please assist me what formula I can use to find the missing in a sequence number? see example below. How could I find the missing 1327, 1330 and 1333? Order # 1325 1326 1328 1329 1331 1332 1334 1335 Any help will be appreciated. Thank youj, Beth |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indicate missing number in a sequence
On Thu, 1 Jan 2009 14:39:59 -0500, "T. Valko" wrote:
**Assuming no numbers are duplicated.** That's the key point, of course A more efficient error trap for versions prior to Excel 2007 might be: =IF(ROWS(B$2:B2)<=MAX(Order)-MIN(Order)+1-COUNT(Order),..... I really like the IFERROR function in 2007. --ron |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indicate missing number in a sequence
Another alternative tinker to try out ...
Assuming your data in A2 down, with starting number in A2 Put in B3: =IF(ROWS($1:1)+$A$2MAX(A:A),"Stop",IF(ISNUMBER(MA TCH(ROWS($1:1)+$A$2,A:A,0)),"",ROWS($1:1)+$A$2)) Copy B3 down until "Stop" appears. Leave B1:B2 empty Then put in C3: =IF(ROWS($1:1)COUNT(B:B),"",SMALL(B:B,ROWS($1:1)) ) Copy C3 down to col B's filled extent, to return the required results (ie the missing nums) neatly packed at the top -- Max Singapore http://savefile.com/projects/236895 Downloads:22,000 Files:370 Subscribers:66 xdemechanik --- |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indicate missing number in a sequence
"Ron Rosenfeld" wrote in message
... I really like the IFERROR function in 2007. --ron Yeah, it can really cut down on "uglyness" when dealing with those long complex formulas. I'm not convinced that it's *always* more efficient, though. For *anticipated* errors... Consider your example: =IFERROR(LARGE(IF(COUNTIF(Order,ROW(INDIRECT(MIN(O rder) &":"&MAX(Order))))=0,ROW(INDIRECT(MIN(Order) &":"&MAX(Order))),""),ROWS($1:1)),"") The *entire* LARGE function has to calculate to generate an error that the IFERROR can then trap. If you were to use my suggested error trap, only that portion of the formula has to calculate to trap the *anticipated* error. However, when there is no anticipated error generated your formula doesn't have to calculate a separate error trap. Overall, I think there are times when IFERROR is not the most efficient method but this depends on the application and whether or not efficiency is a high priority. On a related side note: We've all assumed that the start and end of the sequence are present in the range. If they might be among the missing numbers it'd be a good idea to let the user set the boundaries in a couple of cells (unless they want to hard code it). -- Biff Microsoft Excel MVP |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Indicate missing number in a sequence
On Jan 1, 1:08*pm, Beth wrote:
Thank you, xlm. by using your formula I got below missing numbers. *could you please review and advise. Order # Missing Numbers 1325 * *1326 1326 * *1327 1328 * *1329 1329 * *1330 1331 * *1332 1332 * *1333 1334 * *1335 1335 * *1336 1336 * *1337 1337 * *1338 Thank you, Beth "xlm" wrote: Hi Beth I think you need to change Ragdyer's formula to this if you have a header in A1and place it in B3, then copy down. You will need to input the first value in B2 which is 1325. =IF(A2+1=A2,"",A2+1) -- HTH Appreciate that you click the Yes button below if this posting is helpful cheers, francis "Beth" wrote in message ... Thank you for your help on this. *It is not working. *The header is on A1 and the first data starts from A2. *Where do I need to start typing the formula (the first formula)? Thank you, Beth "Ragdyer" wrote: Would this work for you? With your numbers in A1 to A8, enter this in B2 and copy down: =IF(A1+1=A2,"",A1+1) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Beth" wrote in message ... can somone please assist me what formula I can use to find the missing in a sequence number? see example below. *How could I find the missing 1327, 1330 and 1333? Order # 1325 1326 1328 1329 1331 1332 1334 1335 Any help will be appreciated. Thank youj, Beth Beth, here is what I get, I have shown the formula to the right of the cell where it is needed. 1325 =IF(A2+1=A3,"",A2+1) 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 It really does not matter if you start the formula in B2 or B3, it will only vary the cell where the missing number appears. -TrekMan |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| find missing numbers in a sequence | Excel Discussion (Misc queries) | |||
| Indicate missing number in a sequence (Part II) | Excel Discussion (Misc queries) | |||
| indicate a missing number in a sequence | Excel Discussion (Misc queries) | |||
| How do I find a missing number in a sequence of numbers? | Excel Worksheet Functions | |||
| Finding numbers missing from a sequence | Excel Discussion (Misc queries) | |||
![Beth[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
