I am finishing up a product list for a new retail store. We have 4328
separate products, and each one I have had to enter manually by hand. Now I
need to make every item end at 99 cents (.99) for the price column, but I
cannot figure out how to do this with the roundup functions, or any function
for that matter. I really don't want to manually go thru 4000 items again
just to change the *&!*@&*!&@#! price. So if anyone has some knowledge on
this, drop it. I'll love you forever.

See if this points you in the right direction:

With
A1: (a numeric value)

B1: =INT(A1)+0.99

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Smatass" wrote:

I am finishing up a product list for a new retail store. We have 4328
separate products, and each one I have had to enter manually by hand. Now I
need to make every item end at 99 cents (.99) for the price column, but I
cannot figure out how to do this with the roundup functions, or any function
for that matter. I really don't want to manually go thru 4000 items again
just to change the *&!*@&*!&@#! price. So if anyone has some knowledge on
this, drop it. I'll love you forever.

Hey Ron, thanks for the reply. I tried that, but it didn't work as expected.
Let me be more specific as to exactly what I am doing so we are on the same
page. This is what I am working with:

- column 1 = my price for product, say 4.50
- column 2 = retail price, which I have a simple formula (a1*2.9) which
works, for both the spreadsheet and my wallet :P
- so the total in column 3 equals 13.05, but I want it to equal 13.99

When I put in your formula, it gives me the wrong price for column 2 since
it negates the other formula, and I know you can't reference the same cell
that you are putting the formula in.. so i've been sitting here trying to
figure out how that could even possibly work without referencing the same
cell as the formula. I have no idea. You?


So if

"Ron Coderre" wrote:

See if this points you in the right direction:

With
A1: (a numeric value)

B1: =INT(A1)+0.99

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Smatass" wrote:

I am finishing up a product list for a new retail store. We have 4328
separate products, and each one I have had to enter manually by hand. Now I
need to make every item end at 99 cents (.99) for the price column, but I
cannot figure out how to do this with the roundup functions, or any function
for that matter. I really don't want to manually go thru 4000 items again
just to change the *&!*@&*!&@#! price. So if anyone has some knowledge on
this, drop it. I'll love you forever.

Try this:

A1: 4.50
B1: =A1*2.9

C1: =INT(B1)+0.99

Or maybe?....
C1: =CEILING(B1,1)-0.01

Helping yet?
***********
Regards,
Ron

XL2002, WinXP


"Smatass" wrote:

Hey Ron, thanks for the reply. I tried that, but it didn't work as expected.
Let me be more specific as to exactly what I am doing so we are on the same
page. This is what I am working with:

- column 1 = my price for product, say 4.50
- column 2 = retail price, which I have a simple formula (a1*2.9) which
works, for both the spreadsheet and my wallet :P
- so the total in column 3 equals 13.05, but I want it to equal 13.99

When I put in your formula, it gives me the wrong price for column 2 since
it negates the other formula, and I know you can't reference the same cell
that you are putting the formula in.. so i've been sitting here trying to
figure out how that could even possibly work without referencing the same
cell as the formula. I have no idea. You?


So if

"Ron Coderre" wrote:

See if this points you in the right direction:

With
A1: (a numeric value)

B1: =INT(A1)+0.99

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Smatass" wrote:

I am finishing up a product list for a new retail store. We have 4328
separate products, and each one I have had to enter manually by hand. Now I
need to make every item end at 99 cents (.99) for the price column, but I
cannot figure out how to do this with the roundup functions, or any function
for that matter. I really don't want to manually go thru 4000 items again
just to change the *&!*@&*!&@#! price. So if anyone has some knowledge on
this, drop it. I'll love you forever.

Actually scratch that, I got it. Read another post and it was right there.
this is what I put:

=INT(I2372*2.9)+0.99

That works perfectly. Thanks for your help though, I never knew of the INT
function before, so you're like a god to me. or something like that. peace.

"Ron Coderre" wrote:

See if this points you in the right direction:

With
A1: (a numeric value)

B1: =INT(A1)+0.99

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Smatass" wrote:

I am finishing up a product list for a new retail store. We have 4328
separate products, and each one I have had to enter manually by hand. Now I
need to make every item end at 99 cents (.99) for the price column, but I
cannot figure out how to do this with the roundup functions, or any function
for that matter. I really don't want to manually go thru 4000 items again
just to change the *&!*@&*!&@#! price. So if anyone has some knowledge on
this, drop it. I'll love you forever.

What up ron, I see where you are going with it, but it wont work though.
The formula solution HAS to be in the 2nd column. If it isn't it defeats the
purpose of these postings, as I would have to enter it manually each time or
cut and paste from somewhere else. If you didnt see my reply to my own reply
to your post, I got it figured out. I do appreciate your help though.
Actually, if you wouldnt mind I do have one other thing I am trying to figure
out.. if you know anything about this that would be cool. One of the
columns on the spreadsheet contains model #'s, and I have around 25 different
manufacturers for the products, and I cannot have 2 different products with
the same model #, follow me? So my question is how would you:

1) check a column for duplicate entries in cells for the data I have already
entered

and

2) Create a formula that would check all of the previous entries in the same
column when new data is entered, so I would know if it was a duplicate as
soon as I entered it.

Does that make sense? Let me know if you can help. thanks bro.



"Ron Coderre" wrote:

Try this:

A1: 4.50
B1: =A1*2.9

C1: =INT(B1)+0.99

Or maybe?....
C1: =CEILING(B1,1)-0.01

Helping yet?
***********
Regards,
Ron

XL2002, WinXP


"Smatass" wrote:

Hey Ron, thanks for the reply. I tried that, but it didn't work as expected.
Let me be more specific as to exactly what I am doing so we are on the same
page. This is what I am working with:

- column 1 = my price for product, say 4.50
- column 2 = retail price, which I have a simple formula (a1*2.9) which
works, for both the spreadsheet and my wallet :P
- so the total in column 3 equals 13.05, but I want it to equal 13.99

When I put in your formula, it gives me the wrong price for column 2 since
it negates the other formula, and I know you can't reference the same cell
that you are putting the formula in.. so i've been sitting here trying to
figure out how that could even possibly work without referencing the same
cell as the formula. I have no idea. You?


So if

"Ron Coderre" wrote:

See if this points you in the right direction:

With
A1: (a numeric value)

B1: =INT(A1)+0.99

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Smatass" wrote:

I am finishing up a product list for a new retail store. We have 4328
separate products, and each one I have had to enter manually by hand. Now I
need to make every item end at 99 cents (.99) for the price column, but I
cannot figure out how to do this with the roundup functions, or any function
for that matter. I really don't want to manually go thru 4000 items again
just to change the *&!*@&*!&@#! price. So if anyone has some knowledge on
this, drop it. I'll love you forever.

Try this:

With
Col_A for input, A1:A10 already has values

-------------------
Question 1: Find dupes already input
B1: =IF(COUNTIF(A:A,A1)1,"dupe","ok")
Copy that fomula down through B10

-------------------
Question #2: Flag new dupes
Select Col_A, with A1 as the active cell

Then
From the Excel main menu:
<data<validation
Allow: Custom
Formula: =COUNTIF(A:A,A1)=1
Click the [OK] button

Adjust range references to suit your situation.
-------------------

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Smatass" wrote:

What up ron, I see where you are going with it, but it wont work though.
The formula solution HAS to be in the 2nd column. If it isn't it defeats the
purpose of these postings, as I would have to enter it manually each time or
cut and paste from somewhere else. If you didnt see my reply to my own reply
to your post, I got it figured out. I do appreciate your help though.
Actually, if you wouldnt mind I do have one other thing I am trying to figure
out.. if you know anything about this that would be cool. One of the
columns on the spreadsheet contains model #'s, and I have around 25 different
manufacturers for the products, and I cannot have 2 different products with
the same model #, follow me? So my question is how would you:

1) check a column for duplicate entries in cells for the data I have already
entered

and

2) Create a formula that would check all of the previous entries in the same
column when new data is entered, so I would know if it was a duplicate as
soon as I entered it.

Does that make sense? Let me know if you can help. thanks bro.



"Ron Coderre" wrote:

Try this:

A1: 4.50
B1: =A1*2.9

C1: =INT(B1)+0.99

Or maybe?....
C1: =CEILING(B1,1)-0.01

Helping yet?
***********
Regards,
Ron

XL2002, WinXP


"Smatass" wrote:

Hey Ron, thanks for the reply. I tried that, but it didn't work as expected.
Let me be more specific as to exactly what I am doing so we are on the same
page. This is what I am working with:

- column 1 = my price for product, say 4.50
- column 2 = retail price, which I have a simple formula (a1*2.9) which
works, for both the spreadsheet and my wallet :P
- so the total in column 3 equals 13.05, but I want it to equal 13.99

When I put in your formula, it gives me the wrong price for column 2 since
it negates the other formula, and I know you can't reference the same cell
that you are putting the formula in.. so i've been sitting here trying to
figure out how that could even possibly work without referencing the same
cell as the formula. I have no idea. You?


So if

"Ron Coderre" wrote:

See if this points you in the right direction:

With
A1: (a numeric value)

B1: =INT(A1)+0.99

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Smatass" wrote:

I am finishing up a product list for a new retail store. We have 4328
separate products, and each one I have had to enter manually by hand. Now I
need to make every item end at 99 cents (.99) for the price column, but I
cannot figure out how to do this with the roundup functions, or any function
for that matter. I really don't want to manually go thru 4000 items again
just to change the *&!*@&*!&@#! price. So if anyone has some knowledge on
this, drop it. I'll love you forever.

there you go, that works perfect. thanks bro. You're first solution actually
works for both of my questions, didn't have to use the data, validation menu.
Since your so knowledgeable, I only have 58 questions left to run by you
:P thanks.



"Ron Coderre" wrote:

Try this:

With
Col_A for input, A1:A10 already has values

-------------------
Question 1: Find dupes already input
B1: =IF(COUNTIF(A:A,A1)1,"dupe","ok")
Copy that fomula down through B10

-------------------
Question #2: Flag new dupes
Select Col_A, with A1 as the active cell

Then
From the Excel main menu:
<data<validation
Allow: Custom
Formula: =COUNTIF(A:A,A1)=1
Click the [OK] button

Adjust range references to suit your situation.
-------------------

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Smatass" wrote:

What up ron, I see where you are going with it, but it wont work though.
The formula solution HAS to be in the 2nd column. If it isn't it defeats the
purpose of these postings, as I would have to enter it manually each time or
cut and paste from somewhere else. If you didnt see my reply to my own reply
to your post, I got it figured out. I do appreciate your help though.
Actually, if you wouldnt mind I do have one other thing I am trying to figure
out.. if you know anything about this that would be cool. One of the
columns on the spreadsheet contains model #'s, and I have around 25 different
manufacturers for the products, and I cannot have 2 different products with
the same model #, follow me? So my question is how would you:

1) check a column for duplicate entries in cells for the data I have already
entered

and

2) Create a formula that would check all of the previous entries in the same
column when new data is entered, so I would know if it was a duplicate as
soon as I entered it.

Does that make sense? Let me know if you can help. thanks bro.



"Ron Coderre" wrote:

Try this:

A1: 4.50
B1: =A1*2.9

C1: =INT(B1)+0.99

Or maybe?....
C1: =CEILING(B1,1)-0.01

Helping yet?
***********
Regards,
Ron

XL2002, WinXP


"Smatass" wrote:

Hey Ron, thanks for the reply. I tried that, but it didn't work as expected.
Let me be more specific as to exactly what I am doing so we are on the same
page. This is what I am working with:

- column 1 = my price for product, say 4.50
- column 2 = retail price, which I have a simple formula (a1*2.9) which
works, for both the spreadsheet and my wallet :P
- so the total in column 3 equals 13.05, but I want it to equal 13.99

When I put in your formula, it gives me the wrong price for column 2 since
it negates the other formula, and I know you can't reference the same cell
that you are putting the formula in.. so i've been sitting here trying to
figure out how that could even possibly work without referencing the same
cell as the formula. I have no idea. You?


So if

"Ron Coderre" wrote:

See if this points you in the right direction:

With
A1: (a numeric value)

B1: =INT(A1)+0.99

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Smatass" wrote:

I am finishing up a product list for a new retail store. We have 4328
separate products, and each one I have had to enter manually by hand. Now I
need to make every item end at 99 cents (.99) for the price column, but I
cannot figure out how to do this with the roundup functions, or any function
for that matter. I really don't want to manually go thru 4000 items again
just to change the *&!*@&*!&@#! price. So if anyone has some knowledge on
this, drop it. I'll love you forever.

Thanks for the feedback....I'm glad I could help.


***********
Regards,
Ron

XL2002, WinXP


"Smatass" wrote:

there you go, that works perfect. thanks bro. You're first solution actually
works for both of my questions, didn't have to use the data, validation menu.
Since your so knowledgeable, I only have 58 questions left to run by you
:P thanks.



"Ron Coderre" wrote:

Try this:

With
Col_A for input, A1:A10 already has values

-------------------
Question 1: Find dupes already input
B1: =IF(COUNTIF(A:A,A1)1,"dupe","ok")
Copy that fomula down through B10

-------------------
Question #2: Flag new dupes
Select Col_A, with A1 as the active cell

Then
From the Excel main menu:
<data<validation
Allow: Custom
Formula: =COUNTIF(A:A,A1)=1
Click the [OK] button

Adjust range references to suit your situation.
-------------------

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Smatass" wrote:

What up ron, I see where you are going with it, but it wont work though.
The formula solution HAS to be in the 2nd column. If it isn't it defeats the
purpose of these postings, as I would have to enter it manually each time or
cut and paste from somewhere else. If you didnt see my reply to my own reply
to your post, I got it figured out. I do appreciate your help though.
Actually, if you wouldnt mind I do have one other thing I am trying to figure
out.. if you know anything about this that would be cool. One of the
columns on the spreadsheet contains model #'s, and I have around 25 different
manufacturers for the products, and I cannot have 2 different products with
the same model #, follow me? So my question is how would you:

1) check a column for duplicate entries in cells for the data I have already
entered

and

2) Create a formula that would check all of the previous entries in the same
column when new data is entered, so I would know if it was a duplicate as
soon as I entered it.

Does that make sense? Let me know if you can help. thanks bro.



"Ron Coderre" wrote:

Try this:

A1: 4.50
B1: =A1*2.9

C1: =INT(B1)+0.99

Or maybe?....
C1: =CEILING(B1,1)-0.01

Helping yet?
***********
Regards,
Ron

XL2002, WinXP


"Smatass" wrote:

Hey Ron, thanks for the reply. I tried that, but it didn't work as expected.
Let me be more specific as to exactly what I am doing so we are on the same
page. This is what I am working with:

- column 1 = my price for product, say 4.50
- column 2 = retail price, which I have a simple formula (a1*2.9) which
works, for both the spreadsheet and my wallet :P
- so the total in column 3 equals 13.05, but I want it to equal 13.99

When I put in your formula, it gives me the wrong price for column 2 since
it negates the other formula, and I know you can't reference the same cell
that you are putting the formula in.. so i've been sitting here trying to
figure out how that could even possibly work without referencing the same
cell as the formula. I have no idea. You?


So if

"Ron Coderre" wrote:

See if this points you in the right direction:

With
A1: (a numeric value)

B1: =INT(A1)+0.99

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Smatass" wrote:

I am finishing up a product list for a new retail store. We have 4328
separate products, and each one I have had to enter manually by hand. Now I
need to make every item end at 99 cents (.99) for the price column, but I
cannot figure out how to do this with the roundup functions, or any function
for that matter. I really don't want to manually go thru 4000 items again
just to change the *&!*@&*!&@#! price. So if anyone has some knowledge on
this, drop it. I'll love you forever.