Here's what I'm trying to do:
Using Excel's date serial numbers, create a unique value for every instance
of the same date in a column.

For instance, if there are two instances of the date 5/9/2004 in the same
column, then I want to display a value similar to this in the same row of a
different column: 38116.01 for one instance, and with the next instance
having this value: 38116.02. This value should increase incrementally by .01
every time a new instance of the same date is added to the column.

I hope this makes sense and that it's possible with a formula, not a macro.
Thanks in advance for your help!

If there are more than 100 instances then the date will advance after
38116.99

I'm guessing that that is not a possibility?

=A1+COUNTIF(A$1:A1,A1)/100

Copy down as needed. Format as GENERAL to get the serial number.

Biff

"cyndiwise notsowise" wrote
in message ...
Here's what I'm trying to do:
Using Excel's date serial numbers, create a unique value for every
instance
of the same date in a column.

For instance, if there are two instances of the date 5/9/2004 in the same
column, then I want to display a value similar to this in the same row of
a
different column: 38116.01 for one instance, and with the next instance
having this value: 38116.02. This value should increase incrementally by
.01
every time a new instance of the same date is added to the column.

I hope this makes sense and that it's possible with a formula, not a
macro.
Thanks in advance for your help!

Thanks for your quick reply, T. Valko. You are correct, there should never be
more than 99 instances of the same date (far less, I'm sure!)

However, I tried the formula and it numbers every instance of the date the
same. I need the numbers to be unique for every instance:
38116.01
38116.02
38116.03
and so on...

Do you think this is possible?

cyndiwise
----------------------------------------------------------------------------------------

"T. Valko" wrote:

If there are more than 100 instances then the date will advance after
38116.99

I'm guessing that that is not a possibility?

=A1+COUNTIF(A$1:A1,A1)/100

Copy down as needed. Format as GENERAL to get the serial number.

Biff

"cyndiwise notsowise" wrote
in message ...
Here's what I'm trying to do:
Using Excel's date serial numbers, create a unique value for every
instance
of the same date in a column.

For instance, if there are two instances of the date 5/9/2004 in the same
column, then I want to display a value similar to this in the same row of
a
different column: 38116.01 for one instance, and with the next instance
having this value: 38116.02. This value should increase incrementally by
.01
every time a new instance of the same date is added to the column.

I hope this makes sense and that it's possible with a formula, not a
macro.
Thanks in advance for your help!

The formula I suggested does exactly what you want. Post the *exact* formula
you tried.

Biff

"cyndiwise notsowise" wrote
in message ...
Thanks for your quick reply, T. Valko. You are correct, there should never
be
more than 99 instances of the same date (far less, I'm sure!)

However, I tried the formula and it numbers every instance of the date the
same. I need the numbers to be unique for every instance:
38116.01
38116.02
38116.03
and so on...

Do you think this is possible?

cyndiwise
----------------------------------------------------------------------------------------

"T. Valko" wrote:

If there are more than 100 instances then the date will advance after
38116.99

I'm guessing that that is not a possibility?

=A1+COUNTIF(A$1:A1,A1)/100

Copy down as needed. Format as GENERAL to get the serial number.

Biff

"cyndiwise notsowise"
wrote
in message ...
Here's what I'm trying to do:
Using Excel's date serial numbers, create a unique value for every
instance
of the same date in a column.

For instance, if there are two instances of the date 5/9/2004 in the
same
column, then I want to display a value similar to this in the same row
of
a
different column: 38116.01 for one instance, and with the next instance
having this value: 38116.02. This value should increase incrementally
by
.01
every time a new instance of the same date is added to the column.

I hope this makes sense and that it's possible with a formula, not a
macro.
Thanks in advance for your help!

Oops! I "translated" your formula incorrectly for my worksheet. Column A
contains the formula, Column B contains the date, Row 1 is a Header Row. Now
the values are listing sequentially with this formula:
=B2+COUNTIF(B$2:B2,B2)/100

However, now there's an error message displaying for some of the cells
containing the formula. The error message says: "the formula in this cell
refers to a range that has additional numbers adjacent to it". Did I still do
something wrong in translating the formula to fit my worksheet? I have
Columns A thru Q, some of which have numbers and others that contain text,
and some contain other formulas.

Thanks so much for your help.

cyndiwise
---------------------------------------------------------------------------------------

"T. Valko" wrote:

The formula I suggested does exactly what you want. Post the *exact* formula
you tried.

Biff

"cyndiwise notsowise" wrote
in message ...
Thanks for your quick reply, T. Valko. You are correct, there should never
be
more than 99 instances of the same date (far less, I'm sure!)

However, I tried the formula and it numbers every instance of the date the
same. I need the numbers to be unique for every instance:
38116.01
38116.02
38116.03
and so on...

Do you think this is possible?

cyndiwise
----------------------------------------------------------------------------------------

"T. Valko" wrote:

If there are more than 100 instances then the date will advance after
38116.99

I'm guessing that that is not a possibility?

=A1+COUNTIF(A$1:A1,A1)/100

Copy down as needed. Format as GENERAL to get the serial number.

Biff

"cyndiwise notsowise"
wrote
in message ...
Here's what I'm trying to do:
Using Excel's date serial numbers, create a unique value for every
instance
of the same date in a column.

For instance, if there are two instances of the date 5/9/2004 in the
same
column, then I want to display a value similar to this in the same row
of
a
different column: 38116.01 for one instance, and with the next instance
having this value: 38116.02. This value should increase incrementally
by
.01
every time a new instance of the same date is added to the column.

I hope this makes sense and that it's possible with a formula, not a
macro.
Thanks in advance for your help!

now there's an error message displaying for some of the cells
containing the formula.

Is the result of the formula correct?

I suspect that's just Excel trying to be "helpful". Those are error checking
messages that Excel displays. My version (Excel 2002) doesn't have that
particular message. Personally, I hate them and find them to be an intrusive
PITA and have turned them off. All those colored triangles! Argh!

The setting for those messages can be found at ToolsOptionsError
CheckingRules. If you want to turn them off (or some of them) just uncheck
them under Rules.

Biff

"cyndiwise notsowise" wrote
in message ...
Oops! I "translated" your formula incorrectly for my worksheet. Column A
contains the formula, Column B contains the date, Row 1 is a Header Row.
Now
the values are listing sequentially with this formula:
=B2+COUNTIF(B$2:B2,B2)/100

However, now there's an error message displaying for some of the cells
containing the formula. The error message says: "the formula in this cell
refers to a range that has additional numbers adjacent to it". Did I still
do
something wrong in translating the formula to fit my worksheet? I have
Columns A thru Q, some of which have numbers and others that contain text,
and some contain other formulas.

Thanks so much for your help.

cyndiwise
---------------------------------------------------------------------------------------

"T. Valko" wrote:

The formula I suggested does exactly what you want. Post the *exact*
formula
you tried.

Biff

"cyndiwise notsowise"
wrote
in message ...
Thanks for your quick reply, T. Valko. You are correct, there should
never
be
more than 99 instances of the same date (far less, I'm sure!)

However, I tried the formula and it numbers every instance of the date
the
same. I need the numbers to be unique for every instance:
38116.01
38116.02
38116.03
and so on...

Do you think this is possible?

cyndiwise
----------------------------------------------------------------------------------------

"T. Valko" wrote:

If there are more than 100 instances then the date will advance after
38116.99

I'm guessing that that is not a possibility?

=A1+COUNTIF(A$1:A1,A1)/100

Copy down as needed. Format as GENERAL to get the serial number.

Biff

"cyndiwise notsowise"
wrote
in message ...
Here's what I'm trying to do:
Using Excel's date serial numbers, create a unique value for every
instance
of the same date in a column.

For instance, if there are two instances of the date 5/9/2004 in the
same
column, then I want to display a value similar to this in the same
row
of
a
different column: 38116.01 for one instance, and with the next
instance
having this value: 38116.02. This value should increase
incrementally
by
.01
every time a new instance of the same date is added to the column.

I hope this makes sense and that it's possible with a formula, not a
macro.
Thanks in advance for your help!

Thanks, Biff. The results are initially correct, but only if I leave the rows
in the order I entered them. I need to be able to sort the data by different
columns depending upon the data I am looking for. When I sorted my worksheet
by Column P, then the associated numbers in Column A changed.

Example, before sorting:
A2 = 38116.01, B2 = 5/9/2004, C2 = Bonnie, ...P2 = 3.10
A3 = 38116.02, B3 = 5/9/2004, C3 = Annie, ...P3 = 2.50
A4 = 38116.03, B4 = 5/9/2004, C4 = Sally, ...P4 = 6.00
A5 = 38116.04, B5 = 5/9/2004, C5 = George, ...P5 = 1.75
A6 = 38117.01, B6 = 5/10/2004, C6 = Ernie, ...P6 = 1.67
A7 = 38117.02, B7 = 5/10/2004, C7 = Martha, ...P7 = 4.50

After sorting by Column P:
A2 = 38117.01, B2 = 5/10/2004, C2 = Ernie, ...P2 = 1.67
A3 = 38116.01, B3 = 5/9/2004, C3 = George, ...P3 = 1.75
A4 = 38116.02, B4 = 5/9/2004, C4 = Annie, ...P4 = 2.50
A5 = 38116.03, B5 = 5/9/2004, C5 = Bonnie, ...P5 = 3.10
A6 = 38117.02, B6 = 5/10/2004, C6 = Martha, ...P6 = 4.50
A7 = 38116.04, B7 = 5/9/2004, C7 = Sally, ...P7 = 6.00

I put unique values (names) in Column C just to test this. In reality, the
values in all columns except A can be repeated any number of times. That is
why I need unique values in Column A that will "stick" with the original
associated rows.

Maybe I copied the formula incorrectly into the other cells in column A?
What am I missing?

Thanks again for all your help!

cyndiwise
------------------------------------------------------------------------------------------------

"T. Valko" wrote:

now there's an error message displaying for some of the cells
containing the formula.

Is the result of the formula correct?

I suspect that's just Excel trying to be "helpful". Those are error checking
messages that Excel displays. My version (Excel 2002) doesn't have that
particular message. Personally, I hate them and find them to be an intrusive
PITA and have turned them off. All those colored triangles! Argh!

The setting for those messages can be found at ToolsOptionsError
CheckingRules. If you want to turn them off (or some of them) just uncheck
them under Rules.

Biff

"cyndiwise notsowise" wrote
in message ...
Oops! I "translated" your formula incorrectly for my worksheet. Column A
contains the formula, Column B contains the date, Row 1 is a Header Row.
Now
the values are listing sequentially with this formula:
=B2+COUNTIF(B$2:B2,B2)/100

However, now there's an error message displaying for some of the cells
containing the formula. The error message says: "the formula in this cell
refers to a range that has additional numbers adjacent to it". Did I still
do
something wrong in translating the formula to fit my worksheet? I have
Columns A thru Q, some of which have numbers and others that contain text,
and some contain other formulas.

Thanks so much for your help.

cyndiwise
---------------------------------------------------------------------------------------

"T. Valko" wrote:

The formula I suggested does exactly what you want. Post the *exact*
formula
you tried.

Biff

"cyndiwise notsowise"
wrote
in message ...
Thanks for your quick reply, T. Valko. You are correct, there should
never
be
more than 99 instances of the same date (far less, I'm sure!)

However, I tried the formula and it numbers every instance of the date
the
same. I need the numbers to be unique for every instance:
38116.01
38116.02
38116.03
and so on...

Do you think this is possible?

cyndiwise
----------------------------------------------------------------------------------------

"T. Valko" wrote:

If there are more than 100 instances then the date will advance after
38116.99

I'm guessing that that is not a possibility?

=A1+COUNTIF(A$1:A1,A1)/100

Copy down as needed. Format as GENERAL to get the serial number.

Biff

"cyndiwise notsowise"
wrote
in message ...
Here's what I'm trying to do:
Using Excel's date serial numbers, create a unique value for every
instance
of the same date in a column.

For instance, if there are two instances of the date 5/9/2004 in the
same
column, then I want to display a value similar to this in the same
row
of
a
different column: 38116.01 for one instance, and with the next
instance
having this value: 38116.02. This value should increase
incrementally
by
.01
every time a new instance of the same date is added to the column.

I hope this makes sense and that it's possible with a formula, not a
macro.
Thanks in advance for your help!

Ok, now you lost me.

The example after sorting is still returning the correct results. What
should the sample look like after sorting?

Biff

"cyndiwise notsowise" wrote
in message ...
Thanks, Biff. The results are initially correct, but only if I leave the
rows
in the order I entered them. I need to be able to sort the data by
different
columns depending upon the data I am looking for. When I sorted my
worksheet
by Column P, then the associated numbers in Column A changed.

Example, before sorting:
A2 = 38116.01, B2 = 5/9/2004, C2 = Bonnie, ...P2 = 3.10
A3 = 38116.02, B3 = 5/9/2004, C3 = Annie, ...P3 = 2.50
A4 = 38116.03, B4 = 5/9/2004, C4 = Sally, ...P4 = 6.00
A5 = 38116.04, B5 = 5/9/2004, C5 = George, ...P5 = 1.75
A6 = 38117.01, B6 = 5/10/2004, C6 = Ernie, ...P6 = 1.67
A7 = 38117.02, B7 = 5/10/2004, C7 = Martha, ...P7 = 4.50

After sorting by Column P:
A2 = 38117.01, B2 = 5/10/2004, C2 = Ernie, ...P2 = 1.67
A3 = 38116.01, B3 = 5/9/2004, C3 = George, ...P3 = 1.75
A4 = 38116.02, B4 = 5/9/2004, C4 = Annie, ...P4 = 2.50
A5 = 38116.03, B5 = 5/9/2004, C5 = Bonnie, ...P5 = 3.10
A6 = 38117.02, B6 = 5/10/2004, C6 = Martha, ...P6 = 4.50
A7 = 38116.04, B7 = 5/9/2004, C7 = Sally, ...P7 = 6.00

I put unique values (names) in Column C just to test this. In reality, the
values in all columns except A can be repeated any number of times. That
is
why I need unique values in Column A that will "stick" with the original
associated rows.

Maybe I copied the formula incorrectly into the other cells in column A?
What am I missing?

Thanks again for all your help!

cyndiwise
------------------------------------------------------------------------------------------------

"T. Valko" wrote:

now there's an error message displaying for some of the cells
containing the formula.

Is the result of the formula correct?

I suspect that's just Excel trying to be "helpful". Those are error
checking
messages that Excel displays. My version (Excel 2002) doesn't have that
particular message. Personally, I hate them and find them to be an
intrusive
PITA and have turned them off. All those colored triangles! Argh!

The setting for those messages can be found at ToolsOptionsError
CheckingRules. If you want to turn them off (or some of them) just
uncheck
them under Rules.

Biff

"cyndiwise notsowise"
wrote
in message ...
Oops! I "translated" your formula incorrectly for my worksheet. Column
A
contains the formula, Column B contains the date, Row 1 is a Header
Row.
Now
the values are listing sequentially with this formula:
=B2+COUNTIF(B$2:B2,B2)/100

However, now there's an error message displaying for some of the cells
containing the formula. The error message says: "the formula in this
cell
refers to a range that has additional numbers adjacent to it". Did I
still
do
something wrong in translating the formula to fit my worksheet? I have
Columns A thru Q, some of which have numbers and others that contain
text,
and some contain other formulas.

Thanks so much for your help.

cyndiwise
---------------------------------------------------------------------------------------

"T. Valko" wrote:

The formula I suggested does exactly what you want. Post the *exact*
formula
you tried.

Biff

"cyndiwise notsowise"
wrote
in message ...
Thanks for your quick reply, T. Valko. You are correct, there should
never
be
more than 99 instances of the same date (far less, I'm sure!)

However, I tried the formula and it numbers every instance of the
date
the
same. I need the numbers to be unique for every instance:
38116.01
38116.02
38116.03
and so on...

Do you think this is possible?

cyndiwise
----------------------------------------------------------------------------------------

"T. Valko" wrote:

If there are more than 100 instances then the date will advance
after
38116.99

I'm guessing that that is not a possibility?

=A1+COUNTIF(A$1:A1,A1)/100

Copy down as needed. Format as GENERAL to get the serial number.

Biff

"cyndiwise notsowise"

wrote
in message
...
Here's what I'm trying to do:
Using Excel's date serial numbers, create a unique value for
every
instance
of the same date in a column.

For instance, if there are two instances of the date 5/9/2004 in
the
same
column, then I want to display a value similar to this in the
same
row
of
a
different column: 38116.01 for one instance, and with the next
instance
having this value: 38116.02. This value should increase
incrementally
by
.01
every time a new instance of the same date is added to the
column.

I hope this makes sense and that it's possible with a formula,
not a
macro.
Thanks in advance for your help!

I'm sorry my examples were'nt very clear. After sorting by Column P, this is
what it SHOULD look like:

A2 = 38117.01, B2 = 5/10/2004, C2 = Ernie, ...P2 = 1.67
A3 = 38116.04, B3 = 5/9/2004, C3 = George, ...P3 = 1.75
A4 = 38116.02, B4 = 5/9/2004, C4 = Annie, ...P4 = 2.50
A5 = 38116.01, B5 = 5/9/2004, C5 = Bonnie, ...P5 = 3.10
A6 = 38117.02, B6 = 5/10/2004, C6 = Martha, ...P6 = 4.50
A7 = 38116.03, B7 = 5/9/2004, C7 = Sally, ...P7 = 6.00

If you look at the name "Bonnie" (Column C) in the examples from my previous
posting, the corresponding values for Column A are different.

In the "before" example, the value in Column A is 38116.01 for the row
containing "Bonnie".

But, in the "after" example, the value in Column A is 38116.03 for the row
containing "Bonnie", which is unacceptable. The value should stay 38116.01
after sorting.

I realize that some of the names in the examples kept their Column A values,
but I believe that only to be coincidence.

I hope this is more clear, and thanks again for your help.

cyndiwise
-------------------------------------------------------------------------

"T. Valko" wrote:

Ok, now you lost me.

The example after sorting is still returning the correct results. What
should the sample look like after sorting?

Biff

"cyndiwise notsowise" wrote
in message ...
Thanks, Biff. The results are initially correct, but only if I leave the
rows
in the order I entered them. I need to be able to sort the data by
different
columns depending upon the data I am looking for. When I sorted my
worksheet
by Column P, then the associated numbers in Column A changed.

Example, before sorting:
A2 = 38116.01, B2 = 5/9/2004, C2 = Bonnie, ...P2 = 3.10
A3 = 38116.02, B3 = 5/9/2004, C3 = Annie, ...P3 = 2.50
A4 = 38116.03, B4 = 5/9/2004, C4 = Sally, ...P4 = 6.00
A5 = 38116.04, B5 = 5/9/2004, C5 = George, ...P5 = 1.75
A6 = 38117.01, B6 = 5/10/2004, C6 = Ernie, ...P6 = 1.67
A7 = 38117.02, B7 = 5/10/2004, C7 = Martha, ...P7 = 4.50

After sorting by Column P:
A2 = 38117.01, B2 = 5/10/2004, C2 = Ernie, ...P2 = 1.67
A3 = 38116.01, B3 = 5/9/2004, C3 = George, ...P3 = 1.75
A4 = 38116.02, B4 = 5/9/2004, C4 = Annie, ...P4 = 2.50
A5 = 38116.03, B5 = 5/9/2004, C5 = Bonnie, ...P5 = 3.10
A6 = 38117.02, B6 = 5/10/2004, C6 = Martha, ...P6 = 4.50
A7 = 38116.04, B7 = 5/9/2004, C7 = Sally, ...P7 = 6.00

I put unique values (names) in Column C just to test this. In reality, the
values in all columns except A can be repeated any number of times. That
is
why I need unique values in Column A that will "stick" with the original
associated rows.

Maybe I copied the formula incorrectly into the other cells in column A?
What am I missing?

Thanks again for all your help!

cyndiwise
------------------------------------------------------------------------------------------------

"T. Valko" wrote:

now there's an error message displaying for some of the cells
containing the formula.

Is the result of the formula correct?

I suspect that's just Excel trying to be "helpful". Those are error
checking
messages that Excel displays. My version (Excel 2002) doesn't have that
particular message. Personally, I hate them and find them to be an
intrusive
PITA and have turned them off. All those colored triangles! Argh!

The setting for those messages can be found at ToolsOptionsError
CheckingRules. If you want to turn them off (or some of them) just
uncheck
them under Rules.

Biff

"cyndiwise notsowise"
wrote
in message ...
Oops! I "translated" your formula incorrectly for my worksheet. Column
A
contains the formula, Column B contains the date, Row 1 is a Header
Row.
Now
the values are listing sequentially with this formula:
=B2+COUNTIF(B$2:B2,B2)/100

However, now there's an error message displaying for some of the cells
containing the formula. The error message says: "the formula in this
cell
refers to a range that has additional numbers adjacent to it". Did I
still
do
something wrong in translating the formula to fit my worksheet? I have
Columns A thru Q, some of which have numbers and others that contain
text,
and some contain other formulas.

Thanks so much for your help.

cyndiwise
---------------------------------------------------------------------------------------

"T. Valko" wrote:

The formula I suggested does exactly what you want. Post the *exact*
formula
you tried.

Biff

"cyndiwise notsowise"
wrote
in message ...
Thanks for your quick reply, T. Valko. You are correct, there should
never
be
more than 99 instances of the same date (far less, I'm sure!)

However, I tried the formula and it numbers every instance of the
date
the
same. I need the numbers to be unique for every instance:
38116.01
38116.02
38116.03
and so on...

Do you think this is possible?

cyndiwise
----------------------------------------------------------------------------------------

"T. Valko" wrote:

If there are more than 100 instances then the date will advance
after
38116.99

I'm guessing that that is not a possibility?

=A1+COUNTIF(A$1:A1,A1)/100

Copy down as needed. Format as GENERAL to get the serial number.

Biff

"cyndiwise notsowise"

wrote
in message
...
Here's what I'm trying to do:
Using Excel's date serial numbers, create a unique value for
every
instance
of the same date in a column.

For instance, if there are two instances of the date 5/9/2004 in
the
same
column, then I want to display a value similar to this in the
same
row
of
a
different column: 38116.01 for one instance, and with the next
instance
having this value: 38116.02. This value should increase
incrementally
by
.01
every time a new instance of the same date is added to the
column.

I hope this makes sense and that it's possible with a formula,
not a
macro.
Thanks in advance for your help!

That is because column A contains a formula, and that formula returns
results based upon previous data. If you want to preserve the values, you
need to change from formulae to values before sorting.

You could add the formula, get the values in column A, then change to values
by selecting column A< copy the column, then goto EditPasteSpecialValues
and then sort it.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"cyndiwise notsowise" wrote
in message ...
I'm sorry my examples were'nt very clear. After sorting by Column P, this
is
what it SHOULD look like:

A2 = 38117.01, B2 = 5/10/2004, C2 = Ernie, ...P2 = 1.67
A3 = 38116.04, B3 = 5/9/2004, C3 = George, ...P3 = 1.75
A4 = 38116.02, B4 = 5/9/2004, C4 = Annie, ...P4 = 2.50
A5 = 38116.01, B5 = 5/9/2004, C5 = Bonnie, ...P5 = 3.10
A6 = 38117.02, B6 = 5/10/2004, C6 = Martha, ...P6 = 4.50
A7 = 38116.03, B7 = 5/9/2004, C7 = Sally, ...P7 = 6.00

If you look at the name "Bonnie" (Column C) in the examples from my
previous
posting, the corresponding values for Column A are different.

In the "before" example, the value in Column A is 38116.01 for the row
containing "Bonnie".

But, in the "after" example, the value in Column A is 38116.03 for the row
containing "Bonnie", which is unacceptable. The value should stay 38116.01
after sorting.

I realize that some of the names in the examples kept their Column A
values,
but I believe that only to be coincidence.

I hope this is more clear, and thanks again for your help.

cyndiwise
-------------------------------------------------------------------------

"T. Valko" wrote:

Ok, now you lost me.

The example after sorting is still returning the correct results. What
should the sample look like after sorting?

Biff

"cyndiwise notsowise"
wrote
in message ...
Thanks, Biff. The results are initially correct, but only if I leave
the
rows
in the order I entered them. I need to be able to sort the data by
different
columns depending upon the data I am looking for. When I sorted my
worksheet
by Column P, then the associated numbers in Column A changed.

Example, before sorting:
A2 = 38116.01, B2 = 5/9/2004, C2 = Bonnie, ...P2 = 3.10
A3 = 38116.02, B3 = 5/9/2004, C3 = Annie, ...P3 = 2.50
A4 = 38116.03, B4 = 5/9/2004, C4 = Sally, ...P4 = 6.00
A5 = 38116.04, B5 = 5/9/2004, C5 = George, ...P5 = 1.75
A6 = 38117.01, B6 = 5/10/2004, C6 = Ernie, ...P6 = 1.67
A7 = 38117.02, B7 = 5/10/2004, C7 = Martha, ...P7 = 4.50

After sorting by Column P:
A2 = 38117.01, B2 = 5/10/2004, C2 = Ernie, ...P2 = 1.67
A3 = 38116.01, B3 = 5/9/2004, C3 = George, ...P3 = 1.75
A4 = 38116.02, B4 = 5/9/2004, C4 = Annie, ...P4 = 2.50
A5 = 38116.03, B5 = 5/9/2004, C5 = Bonnie, ...P5 = 3.10
A6 = 38117.02, B6 = 5/10/2004, C6 = Martha, ...P6 = 4.50
A7 = 38116.04, B7 = 5/9/2004, C7 = Sally, ...P7 = 6.00

I put unique values (names) in Column C just to test this. In reality,
the
values in all columns except A can be repeated any number of times.
That
is
why I need unique values in Column A that will "stick" with the
original
associated rows.

Maybe I copied the formula incorrectly into the other cells in column
A?
What am I missing?

Thanks again for all your help!

cyndiwise
------------------------------------------------------------------------------------------------

"T. Valko" wrote:

now there's an error message displaying for some of the cells
containing the formula.

Is the result of the formula correct?

I suspect that's just Excel trying to be "helpful". Those are error
checking
messages that Excel displays. My version (Excel 2002) doesn't have
that
particular message. Personally, I hate them and find them to be an
intrusive
PITA and have turned them off. All those colored triangles! Argh!

The setting for those messages can be found at ToolsOptionsError
CheckingRules. If you want to turn them off (or some of them) just
uncheck
them under Rules.

Biff

"cyndiwise notsowise"
wrote
in message ...
Oops! I "translated" your formula incorrectly for my worksheet.
Column
A
contains the formula, Column B contains the date, Row 1 is a Header
Row.
Now
the values are listing sequentially with this formula:
=B2+COUNTIF(B$2:B2,B2)/100

However, now there's an error message displaying for some of the
cells
containing the formula. The error message says: "the formula in this
cell
refers to a range that has additional numbers adjacent to it". Did I
still
do
something wrong in translating the formula to fit my worksheet? I
have
Columns A thru Q, some of which have numbers and others that contain
text,
and some contain other formulas.

Thanks so much for your help.

cyndiwise
---------------------------------------------------------------------------------------

"T. Valko" wrote:

The formula I suggested does exactly what you want. Post the
*exact*
formula
you tried.

Biff

"cyndiwise notsowise"

wrote
in message
...
Thanks for your quick reply, T. Valko. You are correct, there
should
never
be
more than 99 instances of the same date (far less, I'm sure!)

However, I tried the formula and it numbers every instance of the
date
the
same. I need the numbers to be unique for every instance:
38116.01
38116.02
38116.03
and so on...

Do you think this is possible?

cyndiwise
----------------------------------------------------------------------------------------

"T. Valko" wrote:

If there are more than 100 instances then the date will advance
after
38116.99

I'm guessing that that is not a possibility?

=A1+COUNTIF(A$1:A1,A1)/100

Copy down as needed. Format as GENERAL to get the serial number.

Biff

"cyndiwise notsowise"

wrote
in message
...
Here's what I'm trying to do:
Using Excel's date serial numbers, create a unique value for
every
instance
of the same date in a column.

For instance, if there are two instances of the date 5/9/2004
in
the
same
column, then I want to display a value similar to this in the
same
row
of
a
different column: 38116.01 for one instance, and with the next
instance
having this value: 38116.02. This value should increase
incrementally
by
.01
every time a new instance of the same date is added to the
column.

I hope this makes sense and that it's possible with a formula,
not a
macro.
Thanks in advance for your help!

Ok, I figured out what you meant after I had logged out for the night! That
happens a lot with me!

It appears that what you want to do is base the "date value" on both the
date and the value in column P.

Try this:

=B2+(SUMPRODUCT(--(B$2:B$7=B2),--(P2P$2:P$7))+1)/100

Biff

"Bob Phillips" wrote in message
...
That is because column A contains a formula, and that formula returns
results based upon previous data. If you want to preserve the values, you
need to change from formulae to values before sorting.

You could add the formula, get the values in column A, then change to
values by selecting column A< copy the column, then goto
EditPasteSpecialValues and then sort it.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"cyndiwise notsowise" wrote
in message ...
I'm sorry my examples were'nt very clear. After sorting by Column P, this
is
what it SHOULD look like:

A2 = 38117.01, B2 = 5/10/2004, C2 = Ernie, ...P2 = 1.67
A3 = 38116.04, B3 = 5/9/2004, C3 = George, ...P3 = 1.75
A4 = 38116.02, B4 = 5/9/2004, C4 = Annie, ...P4 = 2.50
A5 = 38116.01, B5 = 5/9/2004, C5 = Bonnie, ...P5 = 3.10
A6 = 38117.02, B6 = 5/10/2004, C6 = Martha, ...P6 = 4.50
A7 = 38116.03, B7 = 5/9/2004, C7 = Sally, ...P7 = 6.00

If you look at the name "Bonnie" (Column C) in the examples from my
previous
posting, the corresponding values for Column A are different.

In the "before" example, the value in Column A is 38116.01 for the row
containing "Bonnie".

But, in the "after" example, the value in Column A is 38116.03 for the
row
containing "Bonnie", which is unacceptable. The value should stay
38116.01
after sorting.

I realize that some of the names in the examples kept their Column A
values,
but I believe that only to be coincidence.

I hope this is more clear, and thanks again for your help.

cyndiwise
-------------------------------------------------------------------------

"T. Valko" wrote:

Ok, now you lost me.

The example after sorting is still returning the correct results. What
should the sample look like after sorting?

Biff

"cyndiwise notsowise"
wrote
in message ...
Thanks, Biff. The results are initially correct, but only if I leave
the
rows
in the order I entered them. I need to be able to sort the data by
different
columns depending upon the data I am looking for. When I sorted my
worksheet
by Column P, then the associated numbers in Column A changed.

Example, before sorting:
A2 = 38116.01, B2 = 5/9/2004, C2 = Bonnie, ...P2 = 3.10
A3 = 38116.02, B3 = 5/9/2004, C3 = Annie, ...P3 = 2.50
A4 = 38116.03, B4 = 5/9/2004, C4 = Sally, ...P4 = 6.00
A5 = 38116.04, B5 = 5/9/2004, C5 = George, ...P5 = 1.75
A6 = 38117.01, B6 = 5/10/2004, C6 = Ernie, ...P6 = 1.67
A7 = 38117.02, B7 = 5/10/2004, C7 = Martha, ...P7 = 4.50

After sorting by Column P:
A2 = 38117.01, B2 = 5/10/2004, C2 = Ernie, ...P2 = 1.67
A3 = 38116.01, B3 = 5/9/2004, C3 = George, ...P3 = 1.75
A4 = 38116.02, B4 = 5/9/2004, C4 = Annie, ...P4 = 2.50
A5 = 38116.03, B5 = 5/9/2004, C5 = Bonnie, ...P5 = 3.10
A6 = 38117.02, B6 = 5/10/2004, C6 = Martha, ...P6 = 4.50
A7 = 38116.04, B7 = 5/9/2004, C7 = Sally, ...P7 = 6.00

I put unique values (names) in Column C just to test this. In reality,
the
values in all columns except A can be repeated any number of times.
That
is
why I need unique values in Column A that will "stick" with the
original
associated rows.

Maybe I copied the formula incorrectly into the other cells in column
A?
What am I missing?

Thanks again for all your help!

cyndiwise
------------------------------------------------------------------------------------------------

"T. Valko" wrote:

now there's an error message displaying for some of the cells
containing the formula.

Is the result of the formula correct?

I suspect that's just Excel trying to be "helpful". Those are error
checking
messages that Excel displays. My version (Excel 2002) doesn't have
that
particular message. Personally, I hate them and find them to be an
intrusive
PITA and have turned them off. All those colored triangles! Argh!

The setting for those messages can be found at ToolsOptionsError
CheckingRules. If you want to turn them off (or some of them) just
uncheck
them under Rules.

Biff

"cyndiwise notsowise"
wrote
in message ...
Oops! I "translated" your formula incorrectly for my worksheet.
Column
A
contains the formula, Column B contains the date, Row 1 is a Header
Row.
Now
the values are listing sequentially with this formula:
=B2+COUNTIF(B$2:B2,B2)/100

However, now there's an error message displaying for some of the
cells
containing the formula. The error message says: "the formula in
this
cell
refers to a range that has additional numbers adjacent to it". Did
I
still
do
something wrong in translating the formula to fit my worksheet? I
have
Columns A thru Q, some of which have numbers and others that
contain
text,
and some contain other formulas.

Thanks so much for your help.

cyndiwise
---------------------------------------------------------------------------------------

"T. Valko" wrote:

The formula I suggested does exactly what you want. Post the
*exact*
formula
you tried.

Biff

"cyndiwise notsowise"

wrote
in message
...
Thanks for your quick reply, T. Valko. You are correct, there
should
never
be
more than 99 instances of the same date (far less, I'm sure!)

However, I tried the formula and it numbers every instance of
the
date
the
same. I need the numbers to be unique for every instance:
38116.01
38116.02
38116.03
and so on...

Do you think this is possible?

cyndiwise
----------------------------------------------------------------------------------------

"T. Valko" wrote:

If there are more than 100 instances then the date will advance
after
38116.99

I'm guessing that that is not a possibility?

=A1+COUNTIF(A$1:A1,A1)/100

Copy down as needed. Format as GENERAL to get the serial
number.

Biff

"cyndiwise notsowise"

wrote
in message
...
Here's what I'm trying to do:
Using Excel's date serial numbers, create a unique value for
every
instance
of the same date in a column.

For instance, if there are two instances of the date 5/9/2004
in
the
same
column, then I want to display a value similar to this in the
same
row
of
a
different column: 38116.01 for one instance, and with the
next
instance
having this value: 38116.02. This value should increase
incrementally
by
.01
every time a new instance of the same date is added to the
column.

I hope this makes sense and that it's possible with a
formula,
not a
macro.
Thanks in advance for your help!

Biff, thanks for all your work on this! But I'm afraid I've confused you even
further. The value in Column P is not connected with the date value that I am
trying to create in Column A.

Maybe it will help if I explain exactly what I am doing with this worksheet.
I have a huge collection of items that were all made by the same company. I
purchased most of these items from eBay sellers during the past 5 years, but
some were purchased directly from the company, and others were purchased from
individuals (not on eBay).

I have been keeping track of this collection by using a spreadsheet in
Excel, but I never recorded the actual price I paid for each of the items.
Now that I am selling some of the items, I would like to include the amount I
paid for taxes and shipping in my original cost. I have saved all of the
paperwork that accompanied these purchases so I have access to all of my
costs.

However, sometimes I purchased more than one item from the same "vendor"
(either an eBay seller, the company, or an individual). Since these items
were shipped together, I paid one shipping cost that must be divided among
the items. When I want to look up the shipping cost for one of those items, I
find the paperwork and then calculate the shipping cost.

So I created a spreadsheet to tell me how much I paid in shipping for each
item.
I need a way to uniquely "number" each purchase in order to track this. I
think of this number as an "invoice" or "purchase order" number. All the
purchases made directly from the company already have a Purchase Order
number, but there is no uniform numbering system for the purchases from eBay
sellers or individuals.

Thus, I would like Excel to create unique Order numbers to help me track
each purchase. I thought of using the date serial number because it is unique
for each day, but many times I made purchases from more than one eBay or
individual seller on the same day. That's why I thought of adding the decimal
point numbering to the date value.

Here is a sample of my spreadsheet, with headers:

Order # Date Vendor Name Shipping # of items Shipping cost per item
5/9/2004 Bonnie M. 15.50 5 3.10
5/9/2004 Shelia P. 4.00 3 1.33
5/10/2004 Donna D. 6.50 1 6.50
5/10/2004 Mary M. 4.85 2 2.43
5/26/2004 Bonnie M. 13.00 4 3.25

In the sample above, I have made two purchases from Bonnie M., on different
dates. To distinquish between those two purchases, I need a unique Order
number for each. If I assign the date serial number to each instance of
Bonnie M., then they each have a different Order number. But, that doesn't
work for the purchase from Shelia P. made on 5/9/2004. The date serial number
would be the same (38116) for both of the purchases from Bonnie M and Shelia
P on 5/9/2004. I need a way to add a unique number to the date serial number,
such as 38116.01 for the purchase from Bonnie M, and 38116.02 for the
purchase from Shelia P.

The sample above is very simplistic. Since I am entering the purchase
information randomly, not chronologically, I might enter many other purchases
with different dates. Later, I might enter another purchase made on 5/9/2004,
but it might appear way down in the spreadsheet and I would not remember what
number "extension" to give the date serial number. That's why I wanted Excel
to count the number of entries for each date, and give each a unique "Order"
number.

I believe your original formula accomplishes this, but the Order numbers
don't "stick" with the original entries. If I sort the list by "Vendor Name"
instead of "Date", then Excel changes the value of the Order #. I need the
Order # to stay the same for each purchase, because I am using the Order # in
my huge spreadsheet that lists my item inventory.

On my inventory worksheet, I list the items by their unique "vendor item#",
along with lots of other columns of information, including the original
retail price and the price I actually paid. I need Excel to look up the Order
# in the other worksheet and find the calculated cost of shipping per item.
There is also other information I want Excel to look up by Order #, such as
date received, etc. That way, I only have to enter the Order # for each item,
and Excel looks up the rest of the order information from the other worksheet.

I hope this makes sense about what I am trying to do. I have not found any
software that will accomplish this. Since I already have the Excel inventory
worksheet for tracking the items, I was hoping the Order # worksheet would
help me to efficiently fill in the order information for each item.

It's probably too complicated to be solved by formulas. If so, I will have
to do as Bob Phillips suggested and manually copy & paste the calculated
Order #'s into a separate column to keep the Order #'s with their orginal
purchase information.

Thanks,
cyndiwise
---------------------------------------------------------------------------------

"T. Valko" wrote:

Ok, I figured out what you meant after I had logged out for the night! That
happens a lot with me!

It appears that what you want to do is base the "date value" on both the
date and the value in column P.

Try this:

=B2+(SUMPRODUCT(--(B$2:B$7=B2),--(P2P$2:P$7))+1)/100

Biff

"Bob Phillips" wrote in message
...
That is because column A contains a formula, and that formula returns
results based upon previous data. If you want to preserve the values, you
need to change from formulae to values before sorting.

You could add the formula, get the values in column A, then change to
values by selecting column A< copy the column, then goto
EditPasteSpecialValues and then sort it.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"cyndiwise notsowise" wrote
in message ...
I'm sorry my examples were'nt very clear. After sorting by Column P, this
is
what it SHOULD look like:

A2 = 38117.01, B2 = 5/10/2004, C2 = Ernie, ...P2 = 1.67
A3 = 38116.04, B3 = 5/9/2004, C3 = George, ...P3 = 1.75
A4 = 38116.02, B4 = 5/9/2004, C4 = Annie, ...P4 = 2.50
A5 = 38116.01, B5 = 5/9/2004, C5 = Bonnie, ...P5 = 3.10
A6 = 38117.02, B6 = 5/10/2004, C6 = Martha, ...P6 = 4.50
A7 = 38116.03, B7 = 5/9/2004, C7 = Sally, ...P7 = 6.00

If you look at the name "Bonnie" (Column C) in the examples from my
previous
posting, the corresponding values for Column A are different.

In the "before" example, the value in Column A is 38116.01 for the row
containing "Bonnie".

But, in the "after" example, the value in Column A is 38116.03 for the
row
containing "Bonnie", which is unacceptable. The value should stay
38116.01
after sorting.

I realize that some of the names in the examples kept their Column A
values,
but I believe that only to be coincidence.

I hope this is more clear, and thanks again for your help.

cyndiwise
-------------------------------------------------------------------------

"T. Valko" wrote:

Ok, now you lost me.

The example after sorting is still returning the correct results. What
should the sample look like after sorting?

Biff

"cyndiwise notsowise"
wrote
in message ...
Thanks, Biff. The results are initially correct, but only if I leave
the
rows
in the order I entered them. I need to be able to sort the data by
different
columns depending upon the data I am looking for. When I sorted my
worksheet
by Column P, then the associated numbers in Column A changed.

Example, before sorting:
A2 = 38116.01, B2 = 5/9/2004, C2 = Bonnie, ...P2 = 3.10
A3 = 38116.02, B3 = 5/9/2004, C3 = Annie, ...P3 = 2.50
A4 = 38116.03, B4 = 5/9/2004, C4 = Sally, ...P4 = 6.00
A5 = 38116.04, B5 = 5/9/2004, C5 = George, ...P5 = 1.75
A6 = 38117.01, B6 = 5/10/2004, C6 = Ernie, ...P6 = 1.67
A7 = 38117.02, B7 = 5/10/2004, C7 = Martha, ...P7 = 4.50

After sorting by Column P:
A2 = 38117.01, B2 = 5/10/2004, C2 = Ernie, ...P2 = 1.67
A3 = 38116.01, B3 = 5/9/2004, C3 = George, ...P3 = 1.75
A4 = 38116.02, B4 = 5/9/2004, C4 = Annie, ...P4 = 2.50
A5 = 38116.03, B5 = 5/9/2004, C5 = Bonnie, ...P5 = 3.10
A6 = 38117.02, B6 = 5/10/2004, C6 = Martha, ...P6 = 4.50
A7 = 38116.04, B7 = 5/9/2004, C7 = Sally, ...P7 = 6.00

I put unique values (names) in Column C just to test this. In reality,
the
values in all columns except A can be repeated any number of times.
That
is
why I need unique values in Column A that will "stick" with the
original
associated rows.

Maybe I copied the formula incorrectly into the other cells in column
A?
What am I missing?

Thanks again for all your help!

cyndiwise
------------------------------------------------------------------------------------------------

"T. Valko" wrote:

now there's an error message displaying for some of the cells
containing the formula.

Is the result of the formula correct?

I suspect that's just Excel trying to be "helpful". Those are error
checking
messages that Excel displays. My version (Excel 2002) doesn't have
that
particular message. Personally, I hate them and find them to be an
intrusive
PITA and have turned them off. All those colored triangles! Argh!

The setting for those messages can be found at ToolsOptionsError
CheckingRules. If you want to turn them off (or some of them) just
uncheck
them under Rules.

Biff

"cyndiwise notsowise"
wrote
in message ...
Oops! I "translated" your formula incorrectly for my worksheet.
Column
A
contains the formula, Column B contains the date, Row 1 is a Header
Row.
Now
the values are listing sequentially with this formula:
=B2+COUNTIF(B$2:B2,B2)/100

However, now there's an error message displaying for some of the
cells
containing the formula. The error message says: "the formula in
this
cell
refers to a range that has additional numbers adjacent to it". Did
I
still
do
something wrong in translating the formula to fit my worksheet? I
have
Columns A thru Q, some of which have numbers and others that
contain
text,
and some contain other formulas.

Thanks so much for your help.

cyndiwise
---------------------------------------------------------------------------------------

"T. Valko" wrote:

The formula I suggested does exactly what you want. Post the
*exact*
formula
you tried.

Biff

"cyndiwise notsowise"

wrote
in message
...
Thanks for your quick reply, T. Valko. You are correct, there
should
never
be
more than 99 instances of the same date (far less, I'm sure!)

However, I tried the formula and it numbers every instance of
the
date
the
same. I need the numbers to be unique for every instance:
38116.01
38116.02
38116.03
and so on...

Do you think this is possible?

cyndiwise
----------------------------------------------------------------------------------------

"T. Valko" wrote:

If there are more than 100 instances then the date will advance
after
38116.99

I'm guessing that that is not a possibility?

=A1+COUNTIF(A$1:A1,A1)/100

Copy down as needed. Format as GENERAL to get the serial
number.

Biff

"cyndiwise notsowise"

wrote
in message
...
Here's what I'm trying to do:
Using Excel's date serial numbers, create a unique value for
every
instance
of the same date in a column.

For instance, if there are two instances of the date 5/9/2004
in
the
same
column, then I want to display a value similar to this in the
same
row
of
a
different column: 38116.01 for one instance, and with the
next
instance
having this value: 38116.02. This value should increase
incrementally
by
.01
every time a new instance of the same date is added to the
column.

I hope this makes sense and that it's possible with a
formula,
not a
macro.
Thanks in advance for your help!

You could do it all via VBA, add the formula, copy and paste, then sort. All
at the click of a button.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"cyndiwise notsowise" wrote
in message ...
Biff, thanks for all your work on this! But I'm afraid I've confused you
even
further. The value in Column P is not connected with the date value that I
am
trying to create in Column A.

Maybe it will help if I explain exactly what I am doing with this
worksheet.
I have a huge collection of items that were all made by the same company.
I
purchased most of these items from eBay sellers during the past 5 years,
but
some were purchased directly from the company, and others were purchased
from
individuals (not on eBay).

I have been keeping track of this collection by using a spreadsheet in
Excel, but I never recorded the actual price I paid for each of the items.
Now that I am selling some of the items, I would like to include the
amount I
paid for taxes and shipping in my original cost. I have saved all of the
paperwork that accompanied these purchases so I have access to all of my
costs.

However, sometimes I purchased more than one item from the same "vendor"
(either an eBay seller, the company, or an individual). Since these items
were shipped together, I paid one shipping cost that must be divided among
the items. When I want to look up the shipping cost for one of those
items, I
find the paperwork and then calculate the shipping cost.

So I created a spreadsheet to tell me how much I paid in shipping for each
item.
I need a way to uniquely "number" each purchase in order to track this. I
think of this number as an "invoice" or "purchase order" number. All the
purchases made directly from the company already have a Purchase Order
number, but there is no uniform numbering system for the purchases from
eBay
sellers or individuals.

Thus, I would like Excel to create unique Order numbers to help me track
each purchase. I thought of using the date serial number because it is
unique
for each day, but many times I made purchases from more than one eBay or
individual seller on the same day. That's why I thought of adding the
decimal
point numbering to the date value.

Here is a sample of my spreadsheet, with headers:

Order # Date Vendor Name Shipping # of items Shipping cost per item
5/9/2004 Bonnie M. 15.50 5 3.10
5/9/2004 Shelia P. 4.00 3 1.33
5/10/2004 Donna D. 6.50 1 6.50
5/10/2004 Mary M. 4.85 2 2.43
5/26/2004 Bonnie M. 13.00 4 3.25

In the sample above, I have made two purchases from Bonnie M., on
different
dates. To distinquish between those two purchases, I need a unique Order
number for each. If I assign the date serial number to each instance of
Bonnie M., then they each have a different Order number. But, that doesn't
work for the purchase from Shelia P. made on 5/9/2004. The date serial
number
would be the same (38116) for both of the purchases from Bonnie M and
Shelia
P on 5/9/2004. I need a way to add a unique number to the date serial
number,
such as 38116.01 for the purchase from Bonnie M, and 38116.02 for the
purchase from Shelia P.

The sample above is very simplistic. Since I am entering the purchase
information randomly, not chronologically, I might enter many other
purchases
with different dates. Later, I might enter another purchase made on
5/9/2004,
but it might appear way down in the spreadsheet and I would not remember
what
number "extension" to give the date serial number. That's why I wanted
Excel
to count the number of entries for each date, and give each a unique
"Order"
number.

I believe your original formula accomplishes this, but the Order numbers
don't "stick" with the original entries. If I sort the list by "Vendor
Name"
instead of "Date", then Excel changes the value of the Order #. I need the
Order # to stay the same for each purchase, because I am using the Order #
in
my huge spreadsheet that lists my item inventory.

On my inventory worksheet, I list the items by their unique "vendor
item#",
along with lots of other columns of information, including the original
retail price and the price I actually paid. I need Excel to look up the
Order
# in the other worksheet and find the calculated cost of shipping per
item.
There is also other information I want Excel to look up by Order #, such
as
date received, etc. That way, I only have to enter the Order # for each
item,
and Excel looks up the rest of the order information from the other
worksheet.

I hope this makes sense about what I am trying to do. I have not found any
software that will accomplish this. Since I already have the Excel
inventory
worksheet for tracking the items, I was hoping the Order # worksheet would
help me to efficiently fill in the order information for each item.

It's probably too complicated to be solved by formulas. If so, I will have
to do as Bob Phillips suggested and manually copy & paste the calculated
Order #'s into a separate column to keep the Order #'s with their orginal
purchase information.

Thanks,
cyndiwise
---------------------------------------------------------------------------------

"T. Valko" wrote:

Ok, I figured out what you meant after I had logged out for the night!
That
happens a lot with me!

It appears that what you want to do is base the "date value" on both the
date and the value in column P.

Try this:

=B2+(SUMPRODUCT(--(B$2:B$7=B2),--(P2P$2:P$7))+1)/100

Biff

"Bob Phillips" wrote in message
...
That is because column A contains a formula, and that formula returns
results based upon previous data. If you want to preserve the values,
you
need to change from formulae to values before sorting.

You could add the formula, get the values in column A, then change to
values by selecting column A< copy the column, then goto
EditPasteSpecialValues and then sort it.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"cyndiwise notsowise"
wrote
in message ...
I'm sorry my examples were'nt very clear. After sorting by Column P,
this
is
what it SHOULD look like:

A2 = 38117.01, B2 = 5/10/2004, C2 = Ernie, ...P2 = 1.67
A3 = 38116.04, B3 = 5/9/2004, C3 = George, ...P3 = 1.75
A4 = 38116.02, B4 = 5/9/2004, C4 = Annie, ...P4 = 2.50
A5 = 38116.01, B5 = 5/9/2004, C5 = Bonnie, ...P5 = 3.10
A6 = 38117.02, B6 = 5/10/2004, C6 = Martha, ...P6 = 4.50
A7 = 38116.03, B7 = 5/9/2004, C7 = Sally, ...P7 = 6.00

If you look at the name "Bonnie" (Column C) in the examples from my
previous
posting, the corresponding values for Column A are different.

In the "before" example, the value in Column A is 38116.01 for the row
containing "Bonnie".

But, in the "after" example, the value in Column A is 38116.03 for the
row
containing "Bonnie", which is unacceptable. The value should stay
38116.01
after sorting.

I realize that some of the names in the examples kept their Column A
values,
but I believe that only to be coincidence.

I hope this is more clear, and thanks again for your help.

cyndiwise
-------------------------------------------------------------------------

"T. Valko" wrote:

Ok, now you lost me.

The example after sorting is still returning the correct results.
What
should the sample look like after sorting?

Biff

"cyndiwise notsowise"
wrote
in message ...
Thanks, Biff. The results are initially correct, but only if I
leave
the
rows
in the order I entered them. I need to be able to sort the data by
different
columns depending upon the data I am looking for. When I sorted my
worksheet
by Column P, then the associated numbers in Column A changed.

Example, before sorting:
A2 = 38116.01, B2 = 5/9/2004, C2 = Bonnie, ...P2 = 3.10
A3 = 38116.02, B3 = 5/9/2004, C3 = Annie, ...P3 = 2.50
A4 = 38116.03, B4 = 5/9/2004, C4 = Sally, ...P4 = 6.00
A5 = 38116.04, B5 = 5/9/2004, C5 = George, ...P5 = 1.75
A6 = 38117.01, B6 = 5/10/2004, C6 = Ernie, ...P6 = 1.67
A7 = 38117.02, B7 = 5/10/2004, C7 = Martha, ...P7 = 4.50

After sorting by Column P:
A2 = 38117.01, B2 = 5/10/2004, C2 = Ernie, ...P2 = 1.67
A3 = 38116.01, B3 = 5/9/2004, C3 = George, ...P3 = 1.75
A4 = 38116.02, B4 = 5/9/2004, C4 = Annie, ...P4 = 2.50
A5 = 38116.03, B5 = 5/9/2004, C5 = Bonnie, ...P5 = 3.10
A6 = 38117.02, B6 = 5/10/2004, C6 = Martha, ...P6 = 4.50
A7 = 38116.04, B7 = 5/9/2004, C7 = Sally, ...P7 = 6.00

I put unique values (names) in Column C just to test this. In
reality,
the
values in all columns except A can be repeated any number of times.
That
is
why I need unique values in Column A that will "stick" with the
original
associated rows.

Maybe I copied the formula incorrectly into the other cells in
column
A?
What am I missing?

Thanks again for all your help!

cyndiwise
------------------------------------------------------------------------------------------------

"T. Valko" wrote:

now there's an error message displaying for some of the cells
containing the formula.

Is the result of the formula correct?

I suspect that's just Excel trying to be "helpful". Those are
error
checking
messages that Excel displays. My version (Excel 2002) doesn't have
that
particular message. Personally, I hate them and find them to be an
intrusive
PITA and have turned them off. All those colored triangles! Argh!

The setting for those messages can be found at ToolsOptionsError
CheckingRules. If you want to turn them off (or some of them)
just
uncheck
them under Rules.

Biff

"cyndiwise notsowise"

wrote
in message
...
Oops! I "translated" your formula incorrectly for my worksheet.
Column
A
contains the formula, Column B contains the date, Row 1 is a
Header
Row.
Now
the values are listing sequentially with this formula:
=B2+COUNTIF(B$2:B2,B2)/100

However, now there's an error message displaying for some of the
cells
containing the formula. The error message says: "the formula in
this
cell
refers to a range that has additional numbers adjacent to it".
Did
I
still
do
something wrong in translating the formula to fit my worksheet?
I
have
Columns A thru Q, some of which have numbers and others that
contain
text,
and some contain other formulas.

Thanks so much for your help.

cyndiwise
---------------------------------------------------------------------------------------

"T. Valko" wrote:

The formula I suggested does exactly what you want. Post the
*exact*
formula
you tried.

Biff

"cyndiwise notsowise"

wrote
in message
...
Thanks for your quick reply, T. Valko. You are correct, there
should
never
be
more than 99 instances of the same date (far less, I'm sure!)

However, I tried the formula and it numbers every instance of
the
date
the
same. I need the numbers to be unique for every instance:
38116.01
38116.02
38116.03
and so on...

Do you think this is possible?

cyndiwise
----------------------------------------------------------------------------------------

"T. Valko" wrote:

If there are more than 100 instances then the date will
advance
after
38116.99

I'm guessing that that is not a possibility?

=A1+COUNTIF(A$1:A1,A1)/100

Copy down as needed. Format as GENERAL to get the serial
number.

Biff

"cyndiwise notsowise"

wrote
in message
...
Here's what I'm trying to do:
Using Excel's date serial numbers, create a unique value
for
every
instance
of the same date in a column.

For instance, if there are two instances of the date
5/9/2004
in
the
same
column, then I want to display a value similar to this in
the
same
row
of
a
different column: 38116.01 for one instance, and with the
next
instance
having this value: 38116.02. This value should increase
incrementally
by
.01
every time a new instance of the same date is added to the
column.

I hope this makes sense and that it's possible with a
formula,
not a
macro.
Thanks in advance for your help!