Zero out a matching cell value based on one criteria

[Lizard07]

Hello - I would like to zero out a cell if it is the second time that value appears for a particular route ID. Please see data table below:

<TABLE style="WIDTH: 309pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=412 x:str><COLGROUP><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2304" width=63><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2267" width=62><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966 1pt solid; BACKGROUND-COLOR: #339966; WIDTH: 47pt; HEIGHT: 13.5pt; BORDER-TOP: #339966 1pt solid; BORDER-RIGHT: #339966 1pt solid" class=xl24 height=18 width=63>Route ID</TD><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966; BACKGROUND-COLOR: #339966; WIDTH: 53pt; BORDER-TOP: #339966 1pt solid; BORDER-RIGHT: #339966 1pt solid" class=xl24 width=71>Start Time</TD><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966; BACKGROUND-COLOR: #339966; WIDTH: 47pt; BORDER-TOP: #339966 1pt solid; BORDER-RIGHT: #339966 1pt solid" class=xl24 width=62>End Time</TD><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966; BACKGROUND-COLOR: #339966; WIDTH: 93pt; BORDER-TOP: #339966 1pt solid; BORDER-RIGHT: #339966 1pt solid" class=xl24 width=124>Sum(Leg Distance)</TD><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966; BACKGROUND-COLOR: #339966; WIDTH: 69pt; BORDER-TOP: #339966 1pt solid; BORDER-RIGHT: #339966 1pt solid" class=xl24 width=92>Activity Name</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966 1pt solid; BACKGROUND-COLOR: white; WIDTH: 47pt; HEIGHT: 13.5pt; BORDER-TOP: #339966; BORDER-RIGHT: #339966 1pt solid" class=xl25 height=18 width=63 align=right x:num>532531</TD><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966; BACKGROUND-COLOR: white; WIDTH: 53pt; BORDER-TOP: #339966; BORDER-RIGHT: #339966 1pt solid" class=xl26 width=71 align=right x:num="40758">8/3/2011</TD><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966; BACKGROUND-COLOR: white; WIDTH: 47pt; BORDER-TOP: #339966; BORDER-RIGHT: #339966 1pt solid" class=xl26 width=62 align=right x:num="40759">8/4/2011</TD><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966; BACKGROUND-COLOR: white; WIDTH: 93pt; BORDER-TOP: #339966; BORDER-RIGHT: #339966 1pt solid" class=xl27 width=124 x:num>26.4</TD><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966; BACKGROUND-COLOR: white; WIDTH: 69pt; BORDER-TOP: #339966; BORDER-RIGHT: #339966 1pt solid" class=xl25 width=92>Deliver</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966 1pt solid; BACKGROUND-COLOR: silver; WIDTH: 47pt; HEIGHT: 13.5pt; BORDER-TOP: #339966; BORDER-RIGHT: #339966 1pt solid" class=xl28 height=18 width=63 align=right x:num>532531</TD><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966; BACKGROUND-COLOR: silver; WIDTH: 53pt; BORDER-TOP: #339966; BORDER-RIGHT: #339966 1pt solid" class=xl29 width=71 align=right x:num="40758">8/3/2011</TD><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966; BACKGROUND-COLOR: silver; WIDTH: 47pt; BORDER-TOP: #339966; BORDER-RIGHT: #339966 1pt solid" class=xl29 width=62 align=right x:num="40759">8/4/2011</TD><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966; BACKGROUND-COLOR: silver; WIDTH: 93pt; BORDER-TOP: #339966; BORDER-RIGHT: #339966 1pt solid" class=xl30 width=124 x:num>26.4</TD><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966; BACKGROUND-COLOR: silver; WIDTH: 69pt; BORDER-TOP: #339966; BORDER-RIGHT: #339966 1pt solid" class=xl28 width=92>Deliver</TD></TR></TBODY></TABLE>

The data table I'm using has hundreds of lines of data this is just to show you an example. In this case, I would want it to find the route id and then the zero out the sum(leg distance) column for the 2nd time a value appears. In other words I want it to look like below

<TABLE style="WIDTH: 309pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=412 x:str><COLGROUP><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2304" width=63><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2267" width=62><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966 1pt solid; BACKGROUND-COLOR: #339966; WIDTH: 47pt; HEIGHT: 13.5pt; BORDER-TOP: #339966 1pt solid; BORDER-RIGHT: #339966 1pt solid" class=xl24 height=18 width=63>Route ID</TD><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966; BACKGROUND-COLOR: #339966; WIDTH: 53pt; BORDER-TOP: #339966 1pt solid; BORDER-RIGHT: #339966 1pt solid" class=xl24 width=71>Start Time</TD><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966; BACKGROUND-COLOR: #339966; WIDTH: 47pt; BORDER-TOP: #339966 1pt solid; BORDER-RIGHT: #339966 1pt solid" class=xl24 width=62>End Time</TD><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966; BACKGROUND-COLOR: #339966; WIDTH: 93pt; BORDER-TOP: #339966 1pt solid; BORDER-RIGHT: #339966 1pt solid" class=xl24 width=124>Sum(Leg Distance)</TD><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966; BACKGROUND-COLOR: #339966; WIDTH: 69pt; BORDER-TOP: #339966 1pt solid; BORDER-RIGHT: #339966 1pt solid" class=xl24 width=92>Activity Name</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966 1pt solid; BACKGROUND-COLOR: white; WIDTH: 47pt; HEIGHT: 13.5pt; BORDER-TOP: #339966; BORDER-RIGHT: #339966 1pt solid" class=xl25 height=18 width=63 align=right x:num>532531</TD><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966; BACKGROUND-COLOR: white; WIDTH: 53pt; BORDER-TOP: #339966; BORDER-RIGHT: #339966 1pt solid" class=xl26 width=71 align=right x:num="40758">8/3/2011</TD><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966; BACKGROUND-COLOR: white; WIDTH: 47pt; BORDER-TOP: #339966; BORDER-RIGHT: #339966 1pt solid" class=xl26 width=62 align=right x:num="40759">8/4/2011</TD><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966; BACKGROUND-COLOR: white; WIDTH: 93pt; BORDER-TOP: #339966; BORDER-RIGHT: #339966 1pt solid" class=xl27 width=124 x:num>26.4</TD><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966; BACKGROUND-COLOR: white; WIDTH: 69pt; BORDER-TOP: #339966; BORDER-RIGHT: #339966 1pt solid" class=xl25 width=92>Deliver</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966 1pt solid; BACKGROUND-COLOR: silver; WIDTH: 47pt; HEIGHT: 13.5pt; BORDER-TOP: #339966; BORDER-RIGHT: #339966 1pt solid" class=xl28 height=18 width=63 align=right x:num>532531</TD><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966; BACKGROUND-COLOR: silver; WIDTH: 53pt; BORDER-TOP: #339966; BORDER-RIGHT: #339966 1pt solid" class=xl29 width=71 align=right x:num="40758">8/3/2011</TD><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966; BACKGROUND-COLOR: silver; WIDTH: 47pt; BORDER-TOP: #339966; BORDER-RIGHT: #339966 1pt solid" class=xl29 width=62 align=right x:num="40759">8/4/2011</TD><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966; BACKGROUND-COLOR: silver; WIDTH: 93pt; BORDER-TOP: #339966; BORDER-RIGHT: #339966 1pt solid" class=xl30 width=124 x:num>0.0</TD><TD style="BORDER-BOTTOM: #339966 1pt solid; BORDER-LEFT: #339966; BACKGROUND-COLOR: silver; WIDTH: 69pt; BORDER-TOP: #339966; BORDER-RIGHT: #339966 1pt solid" class=xl28 width=92>Deliver</TD></TR></TBODY></TABLE>


Please help, thanks!

 

[jbeaucaire]

In a helper column off to the right, enter this formula and copy down.

=IF(COUNTIF($A$1:$A1,$A2)>0, 0, $D2)

Then highlight the values created, COPY, click on D2, then Edit > Paste Special > Values. Clear the helper column.

Excel Workbook
	A	B	C	D	E	F	G
1	Route ID	Start Time	End Time	Sum(Leg Distance)	Activity Name		Helper
2	532531	8/3/2011	8/4/2011	26.4	Deliver		26.4
3	532531	8/3/2011	8/4/2011	26.4	Deliver		0
4	532555	8/3/2011	8/4/2011	22.1	Deliver		22.1
5	532555	8/3/2011	8/4/2011	22.1	Deliver		0
6	553464	8/3/2011	8/4/2011	11.7	Deliver		11.7
7	553464	8/3/2011	8/4/2011	11.7	Deliver		0
Sheet2

 

[AlphaFrog]

This assumes the Route ID is in column A and the Leg Distance is in column D

Sub Zero_Duplicate_Routes()

    Dim lr As Long, c As Long
    
    lr = Cells.Find("*", , , , xlByRows, xlPrevious).Row
    c = Cells.Find("*", , , , xlByColumns, xlPrevious).Column + 1
       
    Application.ScreenUpdating = False
    Range("A1:A" & lr).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    Cells(1, c).Resize(lr).Value = "Unique"
    Cells(1, c).Resize(lr).AutoFilter Field:=1, Criteria1:="<>Unique"
    Range("D2:D" & lr).SpecialCells(xlCellTypeVisible).Value = 0
    ActiveSheet.AutoFilterMode = False
    Cells(1, c).Resize(lr).ClearContents
    Application.ScreenUpdating = True
    
End Sub