Find next whole number in list

ManUBlueJay

Active Member
Joined
Aug 30, 2012
Messages
302
Office Version
  1. 2016
Platform
  1. Windows
I have a long is of Id numbers, some of have a letter attatched some of whole numbers. Can any one help me find the second whole number in a list. "Id" is in A1. Through Match I can find 835 (I need the number of rows from A1, So I return 5. I need to find the number of rows 839 is from 835. Any help would be appreciated.

Id
834e
834f
834g
835
832f
473m
835c
835d
835e
835f
839
832g
346g
836c

<colgroup><col></colgroup><tbody>
</tbody>
 
If you want the number of rows between the first real whole number and the second real whole number, you could use this array-entered** formula...

=SMALL(IF(ISNUMBER(A2:A15),ROW(A2:A15)),2)-SMALL(IF(ISNUMBER(A2:A15),ROW(A2:A15)),1)-1

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
I couldn't get 9 or 10 to work. I don't have a basic understanding of Aggregate to tweak it. THe other switching to my sheet, I could not get it to work. #21 works but it only works for the first and second.
I don't know how many whole numbers are before the one I am searching for. I need it to find a given Number, find the next integer, and tell me how many rows are in between
 
Upvote 0
Let's throw a couple more into the mix. Basically the same formula but D2 requires Ctrl+Shift+Enter while E2 does not.
Note that I have changed the sample data slightly as you said
I don't know how many whole numbers are before the one I am searching for.

Excel Workbook
ABCDE
1Id
2834ename77
3666Family
4834gname
5835Family
6832fname
7473mname
8835cname
9835dname
10835ename
11835fname
12839Family
13832gname
14346gname
15836cname
16921aname
17922bname
18888Family
19923cname
Offset
 
Last edited:
Upvote 0
Thank you. That is a home run. Sorry to all that I was not clearer from the beginning
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top