Find next whole number in list

ManUBlueJay

Board Regular
Joined
Aug 30, 2012
Messages
158
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>
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
35,677
Office Version
365
Platform
Windows
How about
=MATCH(AGGREGATE(15,6,A2:A15,2),A2:A15,0)-MATCH(AGGREGATE(15,6,A2:A15,1),A2:A15,0)-1
 

ManUBlueJay

Board Regular
Joined
Aug 30, 2012
Messages
158
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
Joined
Jun 12, 2014
Messages
35,677
Office Version
365
Platform
Windows
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
Joined
Aug 30, 2012
Messages
158
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
Joined
Jun 12, 2014
Messages
35,677
Office Version
365
Platform
Windows
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
Joined
Aug 30, 2012
Messages
158
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
Joined
Jun 12, 2014
Messages
35,677
Office Version
365
Platform
Windows
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.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,299
Maybe:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="color: #333333;;">Id</td><td style="text-align: right;;"></td><td style=";">First number</td><td style=";">Offset to next number</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">834e</td><td style=";">name</td><td style="text-align: right;;">835</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">834f</td><td style=";">name</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">834g</td><td style=";">name</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">835</td><td style=";">Family</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">832f</td><td style=";">name</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">473m</td><td style=";">name</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">835c</td><td style=";">name</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">835d</td><td style=";">name</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">835e</td><td style=";">name</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">835f</td><td style=";">name</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">839</td><td style=";">Family</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">832g</td><td style=";">name</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">346g</td><td style=";">name</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">836c</td><td style=";">name</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">921a</td><td style=";">name</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">922b</td><td style=";">name</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style="text-align: right;;">888</td><td style=";">Family</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">923c</td><td style=";">name</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=AGGREGATE(<font color="Blue">15,6,EXP(<font color="Red">LN(<font color="Green">(<font color="Purple">ROW(<font color="Teal">B2:B20</font>)-ROW(<font color="Teal">B2</font>)+1</font>)/(<font color="Purple">B2:B20="family"</font>)-MATCH(<font color="Purple">C2,A2:A20,0</font>)</font>)</font>),1</font>)</td></tr></tbody></table></td></tr></table><br />
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
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.
 

Forum statistics

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

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top