how do i count through a string of numbers until a specified number is

[rivertaff]

i need some help

how do i count through a string of numbers until a specified number is found?

many thanks

Rivertaff

[jaclaz]

how do i count through a string of numbers until a specified number is found?

Sorry, but the question is not clear.

Do you mean searching a range of cells and find the one containing a certain number?

Use the function =VLOOKUP(lookup_value;table_range;col_index_num;[range_lookup])

Or do you mean parsing a text string, such as find the position of 5 in the string 123456789 ?

Use the function =FIND(find_text;within_text;[start_num])

Or are you talking about a FOR loop in Visual Basic for Applications?

jaclaz

[rivertaff]

i have a column of random numbers. i want to know how long it is since each number last occurred

[jaclaz]

I don't think there is a function that can do that , I guess you need a small VB program that "scans" the range and finds the number.

Table related functions find the first occurrence of a value in a certain range, so that a workaround is possible, if you can "invert" the needed data (i.e. finding first NEXT occurrence of a number)

An example, if you have in A1:A6 the values:

12

23

0

23

12

23

And you put in B1:B6 numbers:

1

2

3

4

5

6

You can put in c1 the formula:

=VLOOKUP($A1;$A2:$B$6;2;0)-$B1

and copy it down till C6 getting:

4

2

#N/A

2

#N/A

0

i.e. offsets to next occurrence of the number.

To get rid of the #N/A, change the formula as follows:

=IF(ISNA(VLOOKUP($A1;$A2:$B$6;2;0)-$B1);0;VLOOKUP($A1;$A2:$B$6;2;0)-$B1)

and you get this:

4

2

0

2

0

0

jaclaz