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

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

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

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.


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
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

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
print
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
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

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
print
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
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

Thanks that was a lot of help

"T. Valko" wrote:

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
print
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
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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"beginner here" wrote in message
...
Thanks that was a lot of help

"T. Valko" wrote:

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
print
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
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

"Blood" and "Stone" come to mind with that one, Biff !! <bg

Well done for dragging it out of him in the end.

Pete

On Nov 26, 10:28 pm, "T. Valko" wrote:
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

"beginner here" wrote in message

...



Thanks that was a lot of help

"T. Valko" wrote:

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(-rng,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
print
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
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- Hide quoted text -

- Show quoted text -

Pete,

I don't think that comment was called for, and very unprofessional. In fact
I thought I was very straight forward in my question.

So if my questions don't make any sense, guess I will just have to try
somewhere else, to find my answers, becasue by your comment, it sure doesn't
look like I am welcome here.

So long folks.

Steve Martin

"Pete_UK" wrote:

"Blood" and "Stone" come to mind with that one, Biff !! <bg

Well done for dragging it out of him in the end.

Pete

On Nov 26, 10:28 pm, "T. Valko" wrote:
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

"beginner here" wrote in message

...



Thanks that was a lot of help

"T. Valko" wrote:

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(-rng,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
print
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
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- Hide quoted text -

- Show quoted text -

My first impression on reading this thread in its entirety yesterday
was that it was like getting blood from a stone - a common expression
here in the UK, and not usually one which causes offence. My comment
was aimed as a complement to Biff for his patience and perseverance,
in that he had to ask you several follow-up questions and make
suppositions before he had sufficient details to offer you a solution,
spanning an hour.

I would remind you that people who post advice and help on the
newsgroups do so for FREE, giving their time, experience and knowledge
to help others. It WASTES time if a problem is not clearly defined, as
yours obviously wasn't.

Still, if you have taken umbrage and gone somewhere else, then you
won't be reading this ...

Pete

On Nov 27, 11:49 pm, beginner here
wrote:
Pete,

I don't think that comment was called for, and very unprofessional. In fact
I thought I was very straight forward in my question.

So if my questions don't make any sense, guess I will just have to try
somewhere else, to find my answers, becasue by your comment, it sure doesn't
look like I am welcome here.

So long folks.

Steve Martin



"Pete_UK" wrote:
"Blood" and "Stone" come to mind with that one, Biff !! <bg

Well done for dragging it out of him in the end.

Pete

On Nov 26, 10:28 pm, "T. Valko" wrote:
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

"beginner here" wrote in message

...

Thanks that was a lot of help

"T. Valko" wrote:

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(--rng,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
print
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
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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -