Column B
=iferror(If($A2/$A2=1,row(),””),””)
Column C
=if($B2=””,””,$B2-iferror(max($B$1:$B1),0))
Yet another approach to finding offset between the first and second numbers:
=SUM(SMALL(IF(ISNUMBER(A2:A19),ROW(A2:A19)),{1,2})*{-1,1})
or
=SUM(SMALL(IF(B2:B19="Family",ROW(B2:B19)),{1,2})*{-1,1})
Please note: these are array formulas; they have to be entered using Ctrl+Shift+Enter, not just Enter.
P.S.: The formulas can be easily modified for finding offset between the first and ,say, third numbers -- if you ever need this. Just change the highlighted 2 to 3.
Last edited by Tetra201; Sep 11th, 2019 at 09:03 PM.
Column B
=iferror(If($A2/$A2=1,row(),””),””)
Column C
=if($B2=””,””,$B2-iferror(max($B$1:$B1),0))
Here is a vba solution to find all the whole numbers and list them in column C if your data is currently in column A
Code:Option Explicit Sub FindInteger() Dim i As Long, lr As Long, lr2 As Long lr = Range("A" & Rows.Count).End(xlUp).Row For i = 1 To lr If IsNumeric(Range("A" & i)) Then If Range("A" & i) = Int(Range("A" & i)) Then lr2 = Range("C" & Rows.Count).End(xlUp).Row + 1 Range("C" & lr2) = Range("A" & i) End If End If Next i End Sub
Let me know if that works for you
Alan
Am Yisrael Chai
Win 10--Office 2019
When Posting Code, please use code tags.
How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/
As I said my range has 7000 lines. I know what I am looking for. (In this case 835) Named "MYSearch". When I find 835 or MySearch. I need to find the next integer in the list to create a drop down with the numbers in between. I Was using Offset(Match) to find 835, and was planning to use height in Offset to finish my list. This number would be how many rows are between 835 and the next integer.
I am not sure the OP is actually after the numbers themselves as opposed to the distance from each other; however, with that said, the following one-liner will output the same list as your code above...
Code:Sub FindIntegers() Columns("A").SpecialCells(xlConstants, xlNumbers).Copy Range("C2") End Sub
Rick's "mini" blog... http://www.excelfox.com/forum/f22/
.
Want to post a small screen shot? See Part B here.
I could use VBA, but I don't want a list. I want to be able to find a number called MySearch. Find the next integer after Mysearch, and tell me how many rows are between the 2.
I could use VBA, but I don't want a list. I want to be able to find a number called MySearch. Find the next integer after Mysearch, and tell me how many rows are between the 2.
Have you tried the formulas from posts 9 and 10? They do exactly what you're asking.
Cheers,
Eric
When you eliminate the impossible, whatever remains, however improbable, must be the truth.
-Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
-Post a screen shot with the HTML Maker
To match first an 2nd int this formula (Ctrl+Shift+Enter) and drag down 2 rows
to match rows Bet ween this formula (Ctrl+Shift+Enter)Code:=INDEX($A$2:$A$15,SMALL(IF(ISNUMBER($A$2:$A$15),ROW($A$2:$A$15)-ROW($A$2)+1),ROWS($A$1:A1)))
Code:=MATCH(INDEX($A$2:$A$15,SMALL(IF(ISNUMBER($A$2:$A$15),ROW($A$2:$A$15)-ROW($A$2)+1),2)),$A$1:$A$15,0)-+MATCH(INDEX($A$2:$A$15,SMALL(IF(ISNUMBER($A$2:$A$15),ROW($A$2:$A$15)-ROW($A$2)+1),1)),$A$1:$A$15,0)-1
A B C D E F G 2 834e 1st Int 835 rows between 6 3 834f 2nd In 839 4 834g 5 835 6 832f 7 473m 8 835c 9 835d 10 835e 11 835f 12 839 13 832g 14 346g 15 836c Sheet1
Array Formulas Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Cell Formula G2 {=MATCH(INDEX($A$2:$A$15,SMALL(IF(ISNUMBER($A$2:$A$15),ROW($A$2:$A$15)-ROW($A$2)+1),2)),$A$1:$A$15,0)-MATCH(INDEX($A$2:$A$15,SMALL(IF(ISNUMBER($A$2:$A$15),ROW($A$2:$A$15)-ROW($A$2)+1),1)),$A$1:$A$15,0)-1} D2 {=INDEX($A$2:$A$15,SMALL(IF(ISNUMBER($A$2:$A$15),ROW($A$2:$A$15)-ROW($A$2)+1),ROWS($A$1:A1)))}
Note: Do not try and enter the {} manually yourself
Last edited by salim hasan; Sep 12th, 2019 at 04:37 PM.
Like this thread? Share it with others