Deleting only some data in rows

[stylencia18]

Good afternoon:


I have 180,000 rows in Excelof bid numbers and award amounts. The problem is the award amounts repeat inevery row. How do I only keep the first award amount and delete the duplicateaward amounts without deleting the entire row.


Bid Number Award Amount
1 $500
1 $500 (delete this amount but keep the rest ofthe data in this row)
1 $500 (delete this amount but keep the rest ofthe data in this row)
1 $500 (delete this amount but keep the rest ofthe data in this row)
1 $500 (delete this amount but keep the rest ofthe data in this row)
2 $700
2 $700 (delete this amount but keep the rest ofthe data in this row)

2 $700 (delete this amount but keep the rest ofthe data in this row)
2 $700 (delete this amount but keep the rest ofthe data in this row)
2 $700 (delete this amount but keep the rest ofthe data in this row)
3 $900
3 $900 (deletethis amount but keep the rest of the data in this row)
3 $900 (delete this amount but keep the rest ofthe data in this row)
3 $900 (delete this amount but keep the rest ofthe data in this row)

 

[Joe4]

So, are they always sorted by Bid Number and Award Amount?
Is Bid Number in column A and Award Amount in column B?
On each duplicate row, do you just want to clear the Award Amount in column B, but leave Bid Number in column A?
Would you ever have an instance where you could have two different Award Amounts for a single Bid Number?

 

[stylencia18]

1. Yes,sorted by bid number and amount
   
2. BidNumber is Column A, Award Amount is Column W
   
   
3. Yes,just clear the award amount. leave Bid number in Column A and all other data in the row
   
   
4. Each bidnumber has the same award amount

 

[Joe4]

Because of this:

Yes,sorted by bid number and amount

and this:

Each bidnumber has the same award amount

This should work:

Sub ClearAmounts()

    Dim lr As Long, r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column A with numbers
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows in column A, starting on row 2
    For r = 2 To lr
'       If value in column A the same as the row above, clear column W
        If Cells(r, "A") = Cells(r - 1, "A") Then Cells(r, "W").ClearContents
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[stylencia18]

WOW it worked!!!! THANK YOU!!!

Because of this:

and this:

This should work:

Sub ClearAmounts()

    Dim lr As Long, r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column A with numbers
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows in column A, starting on row 2
    For r = 2 To lr
'       If value in column A the same as the row above, clear column W
        If Cells(r, "A") = Cells(r - 1, "A") Then Cells(r, "W").ClearContents
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

 

[Joe4]

You are welcome.
Glad I was able to help!