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>
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
How about
=MATCH(AGGREGATE(15,6,A2:A15,2),A2:A15,0)-MATCH(AGGREGATE(15,6,A2:A15,1),A2:A15,0)-1
 
Upvote 0
I have never used aggregate. The list I have in real terms is 7000 lines long. The first whole number (835) I am looking for could be anywhere in that list, but I can find it using match as I know the number. I need to find the next whole number (839) after 835 so I can count the rows in between.
 
Upvote 0
Did you try my suggestion?

Also will 835 always be the first number in the list?
and will 839 always be the 2nd number in the list?
 
Upvote 0
I couldn't quite figure it out as Aggregate is new to me.
No the numbers are variable. I do know the first number I am looking for. I am trying to make a list of the numbers in between the first number and the next number after that one
 
Upvote 0
If it's always the first two numbers, then just use the formula I provided & change the range "A2:A15" to suit your actual data.
 
Last edited:
Upvote 0
It is not the first 2 numbers. I can find the first using match in my range of 7000 cells. It could be anywhere in there. The next number is what I would like to find in relation to the first.
Another option may be the next cell over has the word "Family" in every cell that has a whole number, and the word "name" in all the ones that have a letter. It may be easier to find the correct family but my brain is stumped
 
Upvote 0
If you don't want the first two numbers why did you say
Can any one help me find the second whole number in a list.

This is now beyond my ability with formulae. Hopefully another member will step in & help.
 
Upvote 0
Maybe:


Book1
ABCD
1IdFirst numberOffset to next number
2834ename8357
3834fname
4834gname
5835Family
6832fname
7473mname
8835cname
9835dname
10835ename
11835fname
12839Family
13832gname
14346gname
15836cname
16921aname
17922bname
18888Family
19923cname
Sheet4
Cell Formulas
RangeFormula
D2=AGGREGATE(15,6,EXP(LN((ROW(B2:B20)-ROW(B2)+1)/(B2:B20="family")-MATCH(C2,A2:A20,0))),1)
 
Upvote 0
Maybe...


A
B
C
D
1
Id​
First number​
Offset to next number​
2
834e​
835​
7​
3
834f​
4
834g​
5
835​
6
832f​
7
473m​
8
835c​
9
835d​
10
835e​
11
835f​
12
839​
13
832g​
14
346g​
15
836c​
16
921a​
17
922b​
18
888​
19
923c​

Formula in D2
=IFERROR(MATCH(1,INDEX(--ISNUMBER(INDEX(A:A,MATCH(C2,A:A,0)+1):A$10000),),0),"Not found")

M.
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

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