E-Mail 'Find missing numbers' To A Friend
Email a copy of 'Find missing numbers' to a friend
Loading ... Loading ...30 Responses to “Find missing numbers”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Hi Oscar,
Thanks for the reply and the time to help me on this one.
You are a big help.
God bless you always.
If this operation needs to be done repeatedly, perhaps using a macro would be a more useful alternative...
Sub InsertMissingRows() Dim X As Long, LastRow As Long, Difference As Long Const OrderColumn As String = "B" Const StartRow As Long = 2 LastRow = Cells(Rows.Count, OrderColumn).End(xlUp).Row For X = LastRow To StartRow + 1 Step -1 Difference = Cells(X, OrderColumn).Value - Cells(X - 1, OrderColumn) If Difference > 1 Then Rows(X).Resize(Difference - 1).Insert End If Next LastRow = Cells(Rows.Count, OrderColumn).End(xlUp).Row Cells(StartRow, OrderColumn).Resize(LastRow - StartRow + 1). _ SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=1+R[-1]C" Columns("B").Value = Columns("B").Value End SubHughMark,
You are welcome!
Rick Rothstein (MVP - Excel),
Thanks for your contribution!
hi,
how can we extend the same to 8000 rows in a column,
creating array formula as you said is not working
Select cell B5
Copy / Paste array formula
Press and hold Ctrl + Shift
Press Enter
Release all keys
what should i do to continue ,
any alternative method to copy the array formula
expecting a reply at the earliest
anchal j vattakunnel,
Adjust cell range (bolded)
=SMALL(IF(ISERROR(MATCH($C$1+ROW(OFFSET($A$1, 0, 0, $C$2-$C$1+1))-1, $A$2:$A$6, 0)), $C$1+ROW(OFFSET($A$1, 0, 0, $C$2-$C$1+1))-1), ROW(A1))
Just wanted to say thanks. This worked on a huge range of data that I had. Really appreciate it!
hi
i have 65000 nos in a column how can in find the missing nos. the above formula cannot work.... please help me to rectify the problem. very urgent...
I couldn't get the formula to work either, by changing the range. Could you please help me also with out using VBA?
Thanks
Kenneth G,
How large is your range?
1-3660
The formula should work. Can you provide your formula? Did you create an array formula?
was not hitting ctrl+shift+enter. it works now!
Dear All,
I have serial numbers from 1 to 40,000 entry in excel. In-between serial numbers there some missing numbers. How can I findout what are the missing numbers from large serial numbers i.e. 1 to 40,000.
For e.g. there are serial numbers 1, 2, 4, 5, 6, 8, 9, 10 like wise i have 60,000numbers. Here missing numbers are 3, 7. How I will findout missing numbers 3 & 7 easily.
Kindly help me.
With advance thanks.
Regards,
Nihar
XLRI
abu and ravi,
I have added a vba solution to this post: Missing numbers (vba)
I want to find missing numbers starting from 80000001 to 80003200, how to find it by VBA code, Excel gets hang after entering VBA code.
Chetan Sonawane,
Yes, you are right. Try the new file Find-missing-values-version2.xlsm. Link above.
I want to split one single coloum of approximately 12000 values into several coloums so that I can take print of such numbers on pages, plz help me on this. how to do it ?
Chetan Sonawane,
Adjust cell range Sheet1!$A$1:$A$151.
Get the Excel *.xlsx file
Rearrange-data-from-a-column-to-multiple-columns.xlsx
Thank you Oscar very much, due to your help my work is getting easy. I tried spliting 12935 values in Excel using MS Office 2007, but the file works very slowly , Shall I install MS Office 2010 ?, will it work more faster ? I want your Advice.
I want to find missing value staring with alphabets like B00001 to B11221, how to find it by VBA code, please post new code.
Dear sir,
I wish to find missing nos. starting with alphabets like S0001 to S1122, Please send the code.
How to find missing nos which starts with B0001 or S-001
Dear Osacar Sir,
Please send me solution for finding missing nos starting with J000001 or S000001
Chetan Sonawane,
Create a new column and remove J and S from your lists.
If your list is in column A, cell B1:
=RIGHT(A1,LEN(A1)-1)*1
Copy formula downwards as far as needed.
Start macro and use it on column B.
Dear Sir,
Instead of formula can you help me with VBA code, If you can please modify VBA code for finding missing nos starting with J000001 or S000001
How i can put values of missing sequential numbers?
Number values
1 30
4 20
missing number 2 having value 10
missing number 3 having value 50
Hi Oscar,
I have tried the Array Formula, however, when i hit CTRL+Shift+Enter nothing happens. All the fields are highlighted but it does not provide any info. Any ideas?
Please help
how to use multi user in single macro program sir
Hi,
Kindly assist in finding the missing number in a specific range with dash along with the numbers.
Sample data.
385-234-4980
Hi sir,
SMALL(IF(ISERROR(MATCH(ROW(INDIRECT($E$2&":"&$E$3)),$B$3:$B$7,0)),ROW(INDIRECT($E$2&":"&$E$3))),ROWS($D$8:D8))
Same concept but with different construction..,,