Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding first occurence of a number
In col F cell 2, I have a number that is ten characters long, and this number
could remain the same for up to 20 or 30 rows with additional information on either side of this number, but then this number will change to a different number (number will always be ten characters long) for up to the same 20 or 30 rows, this format will continue for about another 200 rows or so. So what I am needing is a formula that will locate the first occurence of this number, so that I can then access the remaining portion of this information. Steve |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding first occurence of a number
So, does this mean you want the cell address for the first instance of this
number? with additional information on either side of this number I assume that means this info is in different cells on either side of the number. A1 = number =ADDRESS(MATCH(A1,F:F,0),COLUMNS(A1:F1),4) -- Biff Microsoft Excel MVP "beginner here" wrote in message ... In col F cell 2, I have a number that is ten characters long, and this number could remain the same for up to 20 or 30 rows with additional information on either side of this number, but then this number will change to a different number (number will always be ten characters long) for up to the same 20 or 30 rows, this format will continue for about another 200 rows or so. So what I am needing is a formula that will locate the first occurence of this number, so that I can then access the remaining portion of this information. Steve |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding first occurence of a number
The other information that is on eiterh side of my number, I use other
formulas to locate. What I'm needing is a formula that will find the first occurence each time the number changes. Example: In col F cell 2 my number is 1480000770, then in cell 32 same column the number changes to 1485555691. So I want a formula that will print the information that is in col F cell 2, or print the information that is in cell 32, depending on some other criteria. Steve "T. Valko" wrote: So, does this mean you want the cell address for the first instance of this number? with additional information on either side of this number I assume that means this info is in different cells on either side of the number. A1 = number =ADDRESS(MATCH(A1,F:F,0),COLUMNS(A1:F1),4) -- Biff Microsoft Excel MVP "beginner here" wrote in message ... In col F cell 2, I have a number that is ten characters long, and this number could remain the same for up to 20 or 30 rows with additional information on either side of this number, but then this number will change to a different number (number will always be ten characters long) for up to the same 20 or 30 rows, this format will continue for about another 200 rows or so. So what I am needing is a formula that will locate the first occurence of this number, so that I can then access the remaining portion of this information. Steve |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding first occurence of a number
Example: In col F cell 2 my number is 1480000770, then in cell 32 same
column the number changes to 1485555691. So I want a formula that will the information that is in col F cell 2, or print the information that is in cell 32, depending on some other criteria. What do you mean by: depending on some other criteria? What other criteria? -- Biff Microsoft Excel MVP "beginner here" wrote in message ... The other information that is on eiterh side of my number, I use other formulas to locate. What I'm needing is a formula that will find the first occurence each time the number changes. Example: In col F cell 2 my number is 1480000770, then in cell 32 same column the number changes to 1485555691. So I want a formula that will the information that is in col F cell 2, or print the information that is in cell 32, depending on some other criteria. Steve "T. Valko" wrote: So, does this mean you want the cell address for the first instance of this number? with additional information on either side of this number I assume that means this info is in different cells on either side of the number. A1 = number =ADDRESS(MATCH(A1,F:F,0),COLUMNS(A1:F1),4) -- Biff Microsoft Excel MVP "beginner here" wrote in message ... In col F cell 2, I have a number that is ten characters long, and this number could remain the same for up to 20 or 30 rows with additional information on either side of this number, but then this number will change to a different number (number will always be ten characters long) for up to the same 20 or 30 rows, this format will continue for about another 200 rows or so. So what I am needing is a formula that will locate the first occurence of this number, so that I can then access the remaining portion of this information. Steve |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding first occurence of a number
Biff,
Sorry for any confusion, should not written that last statement "depending on some other criteria". All I really need is something that will find each new occurence in the number in col F. Steve "T. Valko" wrote: Example: In col F cell 2 my number is 1480000770, then in cell 32 same column the number changes to 1485555691. So I want a formula that will the information that is in col F cell 2, or print the information that is in cell 32, depending on some other criteria. What do you mean by: depending on some other criteria? What other criteria? -- Biff Microsoft Excel MVP "beginner here" wrote in message ... The other information that is on eiterh side of my number, I use other formulas to locate. What I'm needing is a formula that will find the first occurence each time the number changes. Example: In col F cell 2 my number is 1480000770, then in cell 32 same column the number changes to 1485555691. So I want a formula that will the information that is in col F cell 2, or print the information that is in cell 32, depending on some other criteria. Steve "T. Valko" wrote: So, does this mean you want the cell address for the first instance of this number? with additional information on either side of this number I assume that means this info is in different cells on either side of the number. A1 = number =ADDRESS(MATCH(A1,F:F,0),COLUMNS(A1:F1),4) -- Biff Microsoft Excel MVP "beginner here" wrote in message ... In col F cell 2, I have a number that is ten characters long, and this number could remain the same for up to 20 or 30 rows with additional information on either side of this number, but then this number will change to a different number (number will always be ten characters long) for up to the same 20 or 30 rows, this format will continue for about another 200 rows or so. So what I am needing is a formula that will locate the first occurence of this number, so that I can then access the remaining portion of this information. Steve |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding first occurence of a number
In other words, you want a list of the distinct numbers from column F.
Assume you want the numbers listed starting in cell H2. Cell H1 is the column header. Enter this formula in H2 and copy down until you get blanks: rng = your actual range like F2:F200 =IF(ROWS(H$1:H1)<=COUNT(1/FREQUENCY(rng,rng)),SMALL(rng,SUMPRODUCT(COUNTIF(r ng,H$1:H1))+1),"") This will list the numbers in ascending order. -- Biff Microsoft Excel MVP "beginner here" wrote in message ... Biff, Sorry for any confusion, should not written that last statement "depending on some other criteria". All I really need is something that will find each new occurence in the number in col F. Steve "T. Valko" wrote: Example: In col F cell 2 my number is 1480000770, then in cell 32 same column the number changes to 1485555691. So I want a formula that will the information that is in col F cell 2, or print the information that is in cell 32, depending on some other criteria. What do you mean by: depending on some other criteria? What other criteria? -- Biff Microsoft Excel MVP "beginner here" wrote in message ... The other information that is on eiterh side of my number, I use other formulas to locate. What I'm needing is a formula that will find the first occurence each time the number changes. Example: In col F cell 2 my number is 1480000770, then in cell 32 same column the number changes to 1485555691. So I want a formula that will the information that is in col F cell 2, or print the information that is in cell 32, depending on some other criteria. Steve "T. Valko" wrote: So, does this mean you want the cell address for the first instance of this number? with additional information on either side of this number I assume that means this info is in different cells on either side of the number. A1 = number =ADDRESS(MATCH(A1,F:F,0),COLUMNS(A1:F1),4) -- Biff Microsoft Excel MVP "beginner here" wrote in message ... In col F cell 2, I have a number that is ten characters long, and this number could remain the same for up to 20 or 30 rows with additional information on either side of this number, but then this number will change to a different number (number will always be ten characters long) for up to the same 20 or 30 rows, this format will continue for about another 200 rows or so. So what I am needing is a formula that will locate the first occurence of this number, so that I can then access the remaining portion of this information. Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract single occurence of order number from list | Excel Worksheet Functions | |||
Assign a number each letter on list compounding occurence @ each e | Excel Worksheet Functions | |||
Finding how many events since last occurence in a database list | Excel Worksheet Functions | |||
Finding most common occurence of values in cells containing letters and numbers | Excel Worksheet Functions | |||
finding a number and the number of times it occurs | Excel Discussion (Misc queries) |