# Find next whole number in list

#### ManUBlueJay

##### Board Regular
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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### Fluff

##### MrExcel MVP, Moderator
=MATCH(AGGREGATE(15,6,A2:A15,2),A2:A15,0)-MATCH(AGGREGATE(15,6,A2:A15,1),A2:A15,0)-1

#### ManUBlueJay

##### Board Regular
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.

#### Fluff

##### MrExcel MVP, Moderator
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?

#### ManUBlueJay

##### Board Regular
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

#### Fluff

##### MrExcel MVP, Moderator
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:

#### ManUBlueJay

##### Board Regular
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

#### Fluff

##### MrExcel MVP, Moderator
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.

Maybe:

#### Marcelo Branco

##### MrExcel MVP
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

M.

1,089,598
Messages
5,409,196
Members
403,256
Latest member
Viq