Find the duplicate values in a column and edit another

[Satos]

Hi all,

I am trying to find a macro that will find the same entries in Column A and for all of the duplicates except the first one change the value in column T to 0.

I don't want to remove the column because in column A I have the order number and in column T I have the total amount of cart. In many cases we get orders of different products with the same order number, and these unique values in the other columns are needed in my other calculations.

So I would like to change this:

OrderNumber TotalAmountofCart
100001 50
100002 40
100002 40
100003 50
100004 80
100004 80
100005 75

into:

OrderNumber TotalAmountofCart
100001 50
100002 40
100002 0
100003 50
100004 80
100004 0
100005 75

thanks for your help

 

[BobUmlas]

Macro not necessary. Assuming in your example "OrderNumber" is in A1, enter this in C2:
=IF(COUNTIF($A$1:A2,A2)>1,0,B2)
Fill down
Copy C2 to the bottom, pastespecial values onto B2, Clear column C.
If you NEED a macro, record these steps.

 

[jim may]

Copy Cell formula B2 downward...

Excel 2010
	A	B	C	D
1	Former #'s	Converted #'s
2	100001 50	100001 50		Copy and Paste Column B to itself and Repace Column A with the results
3	100002 40	100002 40
4	100002 40	100002 0
5	100003 50	100003 50
6	100004 80	100004 80
7	100004 80	100004 0
8	100005 75	100005 75
Sheet1

Cell Formulas
Range	Formula
B2	=IF(LEFT(A1,6)<>LEFT(A2,6),A2,LEFT(A1,6) & " " & 0)

 

[Satos]

Thank you guys for your help I didn't try to change any value but I made what i wanted by changing the formula that is uses the value in column T.

=IF((IFNA(IF(MATCH(A3;$A$2:A2;0);0;T3);T3))=0;0;(VLOOKUP(P3;'Input Variables'!$A$19:$C$35;2;FALSE)*T3)+VLOOKUP(P3;'Input Variables'!$A$19:$C$35;3;FALSE))

Work perfectly fine.
What do you guys think?